I am trying to create sub nodes of same name but having different data coming from different columns in a table using for xml. But i am not getting the expected output from the query i have built.
Can some one point me to the right way of building this query?
Sample table and the FOR XML query used given below:
;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 output :
<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>
You can select the 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')