FOR XML EXPLICIT

For XML Explicit is a very complex mode but it allows to present the data in any desirable format we want. It can cause maintenance problem because queries can be very big and any change can be overwhelming. One of the advantages of Explicit mode over others is we can output column values as CDATA. In Explicit mode a parent child relationship is created between the rows of the same table. The format query incorporates is that it has a tag and parent column. Let's go ahead and understand by example, similar to our prior article on XML we will use AdventureWorks.

SELECT 1 AS Tag,

NULL AS Parent,

p.ProductID AS [Product!1!ID],

p.Name AS [Product!1!Name],

p.ProductNumber AS [Product!1!Number],

NULL AS [SalesOrderDetail!2!OrderQty],NULL as [SalesOrderDetail!2!UnitPrice]

FROM SalesLT.Product p

WHERE p.ProductID IN ( 937, 925 )

UNION ALL

SELECT 2 AS Tag,

1 AS Parent,

p.ProductID,

p.Name,

p.ProductNumber,

od.OrderQty ,od.UnitPrice

FROM SalesLT.SalesOrderDetail od

INNER JOIN SalesLT.Product p

ON p.ProductID = od.ProductID

WHERE p.ProductID IN ( 937, 925 )

ORDER BY [Product!1!ID], [Product!1!Number], [SalesOrderDetail!2!OrderQty],[SalesOrderDetail!2!UnitPrice]

FOR XML EXPLICIT;

Below will be the XML output

<Product ID="925" Name="LL Mountain Frame - Black, 44" Number="FR-M21B-44">

<SalesOrderDetail OrderQty="1" UnitPrice="149.8740" />

<SalesOrderDetail OrderQty="1" UnitPrice="149.8740" />

<SalesOrderDetail OrderQty="2" UnitPrice="149.8740" />

<SalesOrderDetail OrderQty="2" UnitPrice="149.8740" />

<SalesOrderDetail OrderQty="6" UnitPrice="149.8740" />

</Product>

<Product ID="937" Name="HL Mountain Pedal" Number="PD-M562">

<SalesOrderDetail OrderQty="1" UnitPrice="48.5940" />

<SalesOrderDetail OrderQty="1" UnitPrice="48.5940" />

<SalesOrderDetail OrderQty="1" UnitPrice="48.5940" />

<SalesOrderDetail OrderQty="2" UnitPrice="48.5940" />

<SalesOrderDetail OrderQty="2" UnitPrice="48.5940" />

<SalesOrderDetail OrderQty="5" UnitPrice="48.5940" />

</Product>

Below will be the tabular format

Now we see that OrderQty and Unit Price is from SalesOrderDetail table and for tag 1 they are null showing the the first query is acting as the parent and the second query is nesting the columns that are refrencing the columns in parent query, creating the hierarchy in XML format.

In order to understand more clearly we can, try to nest Product table columns inside SalesOrderDetails table as below as shown below:

SELECT distinct 1 AS Tag,

NULL AS Parent, s.OrderQty AS [SalesOrderDetail!1!OrderQty]

,s.UnitPrice AS [SalesOrderDetail!1!UnitPrice]

,NULL AS [Product!2!ID],

NULL AS [Product!2!Name],

NULL AS [Product!2!Number]

FROM SalesLT.SalesOrderDetail s

WHERE s.ProductID IN ( 937, 925 )

UNION ALL

SELECT 2 AS Tag,

1 AS Parent,

s.OrderQty ,s.UnitPrice ,

p.ProductID,

p.Name,

p.ProductNumber

FROM SalesLT.SalesOrderDetail s

INNER JOIN SalesLT.Product p

ON p.ProductID = s.ProductID

WHERE p.ProductID IN ( 937, 925 )

ORDER BY [SalesOrderDetail!1!OrderQty],[SalesOrderDetail!1!UnitPrice],[Product!2!ID], [Product!2!Number]

FOR XML EXPLICIT;

The result os above query will be :

<SalesOrderDetail OrderQty="1" UnitPrice="48.5940">

<Product ID="937" Name="HL Mountain Pedal" Number="PD-M562" />

<Product ID="937" Name="HL Mountain Pedal" Number="PD-M562" />

<Product ID="937" Name="HL Mountain Pedal" Number="PD-M562" />

</SalesOrderDetail>

<SalesOrderDetail OrderQty="1" UnitPrice="149.8740">

<Product ID="925" Name="LL Mountain Frame - Black, 44" Number="FR-M21B-44" />

<Product ID="925" Name="LL Mountain Frame - Black, 44" Number="FR-M21B-44" />

</SalesOrderDetail>

<SalesOrderDetail OrderQty="2" UnitPrice="48.5940">

<Product ID="937" Name="HL Mountain Pedal" Number="PD-M562" />

<Product ID="937" Name="HL Mountain Pedal" Number="PD-M562" />

</SalesOrderDetail>

<SalesOrderDetail OrderQty="2" UnitPrice="149.8740">

<Product ID="925" Name="LL Mountain Frame - Black, 44" Number="FR-M21B-44" />

<Product ID="925" Name="LL Mountain Frame - Black, 44" Number="FR-M21B-44" />

</SalesOrderDetail>

<SalesOrderDetail OrderQty="5" UnitPrice="48.5940">

<Product ID="937" Name="HL Mountain Pedal" Number="PD-M562" />

</SalesOrderDetail>

<SalesOrderDetail OrderQty="6" UnitPrice="149.8740">

<Product ID="925" Name="LL Mountain Frame - Black, 44" Number="FR-M21B-44" />

</SalesOrderDetail>

But above will not be a better way of presentation as we are having redundant rows that will take up space.

FOR XML EXPLICIT is deprecated and can be removed from future releases of SQL Server. Binary Base 64, XMLDATA, ROOT and TYPE all work in the same way for FOR XML EXPLICIT as do for other modes.

Tags: , , ,

SQL Server | SQL Tricks | XML