CODEWITHSUNDEEP
sqlxmlt-sqlxpath
guaike
guaike

Reputation: 2491

Select XML nodes,build a table from nested elements

I have a xml string from other system, I need select xml as a table in sql

<jrt>
  <item>
    <id>cell1</id>
    <id>cell2</id>
    <id>cell3</id>
  </item>
  <item>
    <id>cell4</id>
    <id>cell5</id>
    <id>cell6</id>
  </item>
</jrt>

The results should be:

row         value
----------- -----
1           cell1
2           cell2
3           cell3
1           cell4
2           cell5
3           cell6

Notice: the row number is every item's id postion

Please help me how to use tsql select this xml?

Upvotes: 1

Views: 975

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107766

declare @xml xml set @xml = '
<jrt>
  <item>
    <id>cell1</id>
    <id>cell12</id>
    <id>cell83</id>
  </item>
  <item>
    <id>cell4</id>
    <id>cell5</id>
    <id>cell6</id>
  </item>
</jrt>'

SELECT item.number itemrow,
       id.number idrow,
       o.c.value('.', 'nvarchar(max)') value
FROM master..spt_values item
inner join master..spt_values id on id.type='P' and id.number >= 1
CROSS APPLY @xml.nodes('/jrt/item[sql:column("item.number")]') n(c)
CROSS APPLY n.c.nodes('id[sql:column("id.number")]') o(c)
where item.type='P' and item.number >= 1
ORDER BY itemrow, idrow

With help from https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=383888

Upvotes: 2

Related Questions