In many of the previous posts, we discussed about controlling the structure of the XML being generated with FOR XML. SQL Server 2005 introduced the PATH operator which provides a great extend of control over the shaping of the XML being generated. With PATH, we could achieve most of the XML shaping requirements previously possible only with FOR XML EXPLICIT. In my XML Workshop Series at www.sqlservercentral.com, I have discussed many of the XML shaping requirements and different ways to meet those requirements.
I just helped some one to solve another XML shaping problem in the MSDN forums and thought of sharing it here, because the problem seems to be common. Position of elements is significant in XML. Hence, some times we need to control the position of elements being generated. We might need to have a certain element placed before another. Most of the times when we write a FOR XML EXPLICIT query, we might need to have elements placed in a given order. I have discussed it in the FOR XML EXPLICIT tutorial given here.
Let us look into the problem and the solution.
Source data
/*
id NameA NameB NameC
----------- ---------- ---------- ----------
1 Value A Value B Value C
*/
Current Query
SELECT
1 AS Tag,
NULL AS Parent,
ID AS [Tab!1!ID],
NameA AS [Tab!1!NameA!Element],
NULL AS [Tab2!2!NameB!Element],
NameC AS [Tab!1!NameC!Element]
FROM tbl
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
ID,
NULL AS [Tab!1!NameA!Element],
NameB AS [Tab!1!NameB!Element],
NULL AS [Tab!1!NameC!Element]
FROM tbl
FOR XML EXPLICIT
Current XML Result
<Tab ID="1">
<NameA>Value A</NameA>
<NameC>Value C</NameC>
<Tab2>
<NameB>Value B</NameB>
</Tab2>
</Tab>
Expected XML Result
<Tab ID="1">
<NameA>Value A</NameA>
<Tab2>
<NameB>Value B</NameB>
</Tab2>
<NameC>Value C</NameC>
</Tab>
Corrected Solution
We have two options here. We could either go with FOR XML PATH or FOR XML EXPLICIT (FOR XML PATH is available only on SQL server 2005 and above). Here is the query that uses FOR XML EXPLICIT.
DECLARE @tmp TABLE (
id Int,
NameA VarChar(10),
NameB VarChar(10),
NameC VarChar(10)
)
INSERT INTO @tmp (id, NameA, NameB, NameC)
VALUES (1, 'Value A', 'Value B', 'Value C')
SELECT
1 AS Tag,
NULL AS Parent,
ID AS [Tab!1!ID],
NameA AS [Tab!1!NameA!Element],
NULL AS [Tab2!2!NameB!Element],
NULL AS [NameC!3]
FROM @tmp
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
ID, NULL,
NameB, NULL
FROM @tmp
UNION ALL
SELECT
3 AS Tag,
1 AS Parent,
NULL, NULL, NULL,
NameC
FROM @tmp
FOR XML EXPLICIT
/*
<Tab ID="1">
<NameA>Value A</NameA>
<Tab2>
<NameB>Value B</NameB>
</Tab2>
<NameC>Value C</NameC>
</Tab>
*/
And here is the version that uses FOR XML PATH.
DECLARE @tmp TABLE (
id Int,
NameA VarChar(10),
NameB VarChar(10),
NameC VarChar(10)
)
INSERT INTO @tmp (id, NameA, NameB, NameC)
VALUES (1, 'Value A', 'Value B', 'Value C')
SELECT
id AS '@ID',
NameA AS 'NameA',
NameB AS 'Tab2/NameB',
NameC AS 'NameC'
FROM @tmp
FOR XML PATH('Tab')
<Tab ID="1">
<NameA>Value A</NameA>
<Tab2>
<NameB>Value B</NameB>
</Tab2>
<NameC>Value C</NameC>
</Tab>
Note that the query that uses FOR XML PATH is much simpler than the version using FOR XM EXPLICIT.
FOR XML Tutorials