sqlxmlfor-xml

SQL FOR XML to generate multiple same name nodes


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>

Solution

  • 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')