How to write a xquery in SQL Server to read all the childnodes from an XML data column in a table?

Question

I have a table in SQL server named as [QueryResult] with columns of type Init, of type XML.

Here is the sample XML of Result column data.

<OUTPUT>
<item>
    <VALUE001>2023038.09</VALUE001>
    <VALUE002>265127.97</VALUE002>
    <VALUE003>459868.86</VALUE003>
    <VALUE004>16000.01</VALUE004>
    <VALUE005>552010.15</VALUE005>
    <VALUE006>21404.96</VALUE006>
<item>
<item>
    <VALUE001>15655501.89</VALUE001>
    <VALUE002>17172.50</VALUE002>
    <VALUE003>8345.31</VALUE003>
    <VALUE004>-117264374.12</VALUE004>
    <VALUE005>-160893.48</VALUE005>
</item>
</OUTPUT>

I need a query that returns the output as follows:

id      Element          Value
-----------------------------------
1   VALUE001     2023038.09
2   VALUE001    2023038.09
3   VALUE002    265127.97
4   VALUE003    459868.86
5   VALUE004    16000.01
6   VALUE005    552010.15 
7   VALUE006    21404.96
8   VALUE001    15655501.89
9   VALUE002    17172.50
10  VALUE003    8345.31
11  VALUE004    -117264374.12
12  VALUE005    -160893.48

Let me know if the question is not clear to you?

Read this section in the [faq#bounty] about our bounty system. — , Jul 22 '11 at 12:11

score 1 · Accepted Answer · edited May 23 '17 at 12:26

Here's one solution:

DECLARE @xml XML = 
'
<OUTPUT>
<item>
    <VALUE001>2023038.09</VALUE001>
    <VALUE002>265127.97</VALUE002>
    <VALUE003>459868.86</VALUE003>
    <VALUE004>16000.01</VALUE004>
    <VALUE005>552010.15</VALUE005>
    <VALUE006>21404.96</VALUE006>
</item>
<item>
    <VALUE001>15655501.89</VALUE001>
    <VALUE002>17172.50</VALUE002>
    <VALUE003>8345.31</VALUE003>
    <VALUE004>-117264374.12</VALUE004>
    <VALUE005>-160893.48</VALUE005>
</item>
</OUTPUT>
'

SELECT  T.c.value('local-name(.)[1]', 'varchar(100)') AS Element,
        T.c.value('./text()[1]', 'decimal(17,2)') AS Value
FROM   @xml.nodes('//item/child::node()') T(c);

Returning the ordinal position of a node is more difficult than it looks. This question suggests one solution but I haven't implemented it here as it's a bit of a hack.

This is absolutely perfect!! Thank you Harper. I just made few changes to your query for solving my needs.. SELECT T.c.value('local-name(.)[1]', 'varchar(100)') AS Element, T.c.value('./text()[1]', 'varchar(100)') AS Value FROM dbo.QueryResult QR WITH(NOLOCK) CROSS APPLY Result.nodes('//OUTPUT/item/child::node()') AS T(c) — Charan, Jul 23 '11 at 05:59

How to write a xquery in SQL Server to read all the childnodes from an XML data column in a table?

1 Answers1