Flattening xml into columns from different xml elements in SQL

Question

I have the following XML

<creatures>
    <Animals>
        <Name>Dog</Name>
        <Name>Cat</Name>
        <Name>Monkey</Name>
    </Animals>
    <NumLegs>
        <Legs>4</Legs>
        <Legs>4</Legs>
        <Legs>2</Legs>
    </NumLegs>
</creatures>

Need a table as follows

Name    Legs
 Dog    4 
 Cat    4
 Monkey 2

How do I do it in SQL? All the other questions I found on this topic had their XML structured differently. For e.g. if the XML was structured as below, I believe it is straightforward to parse it in XML using nodes()

<creatures>
      <Animal>
           <Name>Dog</Name>
           <Legs>4</Legs>
      </Animal>
      .
      .
      .
 </creatures>

score 0 · Accepted Answer · answered Mar 15 '14 at 21:25

You could generate a index for each row in both sets, then join on that:

declare @xml xml
set @xml =
'<creatures>
    <Animals>
        <Name>Dog</Name>
        <Name>Cat</Name>
        <Name>Monkey</Name>
    </Animals>
    <NumLegs>
        <Legs>4</Legs>
        <Legs>4</Legs>
        <Legs>2</Legs>
    </NumLegs>
</creatures>'

-- create a table of the animals with an index generated by an identity
declare @animals table(n tinyint not null identity(1, 1), animal nvarchar(50) not null)
insert @animals (animal)
select
    a.n.value('.', 'nvarchar(50)')
from
    @xml.nodes('/creatures/Animals/Name') a(n)

-- create a table of the leg amounts with an index generated by an identity
declare @legs table(n tinyint not null identity(1, 1), amount tinyint not null)
insert @legs (amount)
select
    nl.l.value('.', 'tinyint')
from
    @xml.nodes('/creatures/NumLegs/Legs') nl(l)

-- bring together the 2 tables based on the index
select
    a.animal, l.amount
from
    @animals a
    join @legs l
        on a.n = l.n

Flattening xml into columns from different xml elements in SQL

1 Answers1