SQL FOR XML to generate multiple same name nodes

Question

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>

If you rename the alias for name2 column something other than [names/name] like [name2], you will get the expected output. Only thing the xml tag for that will not be <name></name> but <name2></name2>. You can find and replace it after generating the XML, if needed. — Abhay Chauhan
– Abhay Chauhan, Commented Jul 29, 2015 at 13:17
@AbhayChauhan that is what i am planning to do but only as a last resort. — Sarang
– Sarang, Commented Jul 29, 2015 at 13:27

JamieD77 · Accepted Answer · 2015-07-29 13:31:25Z

1

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

answered Jul 29, 2015 at 13:31

JamieD77

14k1 gold badge19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sarang Over a year ago

This works but can give performance issues when the source table has lots of data. Till then i am using the solution mentioned by @AbhayChauhan in his comment to the question.

JamieD77 Over a year ago

if id is primary key it shouldn't be too slow.. but it's up to you

Janne Jämsen · Accepted Answer · 2017-01-24 20:03:29Z

1

I guess, this should be quite 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')

answered Jan 24, 2017 at 20:03

Janne Jämsen

111 bronze badge

Comments

Mark Sowul · Accepted Answer · 2018-07-24 13:36:59Z

0

You could do this quite nicely with a 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')

edited Jul 24, 2018 at 13:36

Mark Sowul

10.7k1 gold badge49 silver badges52 bronze badges

answered Apr 8, 2016 at 18:40

DaveFoyf

12 bronze badges

Comments

Zachary Scott · Accepted Answer · 2022-09-27 15:06:15Z

A little more elegant:

;WITH Temp(id, name1, name2)
AS
(
    SELECT 1 id, 'A', 'B' UNION
    SELECT 2 id, 'C', 'D' UNION
    SELECT 3 id, 'E', 'F'
)
SELECT
    id [Data/id],
    (select name1 name, null, name2 name 
     for xml path('names'), type) [Data]
FROM
    Temp
FOR XML PATH(''), TYPE, ROOT('Feed')

produces:

<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>
    <names>
      <name>E</name>
      <name>F</name>
    </names>
  </Data>
</Feed>

Microsoft XML naturally collapses same name fields and removes empty values by default, so null becomes nothing and permits the same name fields to be side by side in the output.

Collectives™ on Stack Overflow

SQL FOR XML to generate multiple same name nodes

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related