SQL FOR XML to generate multiple identical node names
I am trying to create sub nodes with the same name but have different data coming from different columns in the table using for xml. But I am not getting the expected result from the query I have built.
Can anyone point me to the correct way to build this query?
Example table and used FOR XML query:
;WITH Temp(id, name1, name2)
AS
(
SELECT 1, 'A', 'B' UNION
SELECT 2, 'C', 'D' UNION
SELECT 3, 'E', 'F'
)
SELECT
id
,name1 AS [names/name]
,name2 AS [names/name]
FROM
Temp
FOR XML PATH('Data'), TYPE, ROOT('Feed')
Output:
<Feed>
<Data>
<id>1</id>
<names>
<name>AB</name>
</names>
</Data>
<Data>
<id>2</id>
<names>
<name>CD</name>
</names>
</Data>
<Data>
<id>3</id>
<names>
<name>EF</name>
</names>
</Data>
</Feed>
Expected Result:
<Feed>
<Data>
<id>1</id>
<names>
<name>A</name>
<name>B</name>
</names>
</Data>
<Data>
<id>2</id>
<names>
<name>C</name>
<name>D</name>
</names>
</Data>
<Data>
<id>3</id>
<name>E</name>
<name>F</name>
</names>
</Data>
</Feed>
+3
source to share
3 answers
You can select names in a subquery
;WITH Temp(id, name1, name2)
AS
(
SELECT 1, 'A', 'B' UNION
SELECT 2, 'C', 'D' UNION
SELECT 3, 'E', 'F'
)
SELECT
id
,(SELECT name
FROM (
SELECT name1 AS name
FROM Temp t2
WHERE t1.id = t2.id
UNION ALL
SELECT name2 AS name
FROM Temp t2
WHERE t1.id = t2.id) AS t
FOR XML PATH(''), TYPE) AS names
FROM
Temp t1
FOR XML PATH('Data'), TYPE, ROOT('Feed')
+1
source to share
You can do it pretty well with Cross Apply:
;WITH Temp(id, name1, name2)
AS
(
SELECT 1, 'A', 'B' UNION
SELECT 2, 'C', 'D' UNION
SELECT 3, 'E', 'F'
)
SELECT
id
,x.name AS [names/name]
FROM
Temp
CROSS APPLY
(VALUES
(name1),
(name2)
) x (name)
FOR XML PATH ('Data'), TYPE, ROOT ('Feed')
0
source to share
I think this should be pretty efficient (works in SQL Server at least):
;WITH Temp(id, name1, name2)
AS
(
SELECT 1, 'A', 'B' UNION
SELECT 2, 'C', 'D' UNION
SELECT 3, 'E', 'F'
)
SELECT
id,
(
SELECT
name1 AS name
,null
,name2 AS name
FOR XML PATH(''), TYPE
) AS names
FROM
Temp
FOR XML PATH('Data'), TYPE, ROOT('Feed')
0
source to share