Select node from xml nodes

Question

there! there is a small xml.

Declare @xmlDoc xml = '
<ROW ID="471869" USER_ID="40161" NAME="James" CNT="2" />
<ROW ID="482047" USER_ID="40836" NAME="Nick" CNT="2" />
'

I'm selecting data via

SELECT x.value('(@ID)[1]', 'int') AS ID,
       x.value('(@USER_ID)[1]', 'int') AS USER_ID,
       x.value('(@NAME)[1]', 'varchar(100)') AS NAME,
       x.value('(@CNT)[1]', 'int') AS CNT,
       @xmlDoc.query('/row[1]') as cc
from @xmlDoc.nodes('/ROW') AS t(x)

And result like this

|    ID    |  USER_ID |   NAME  | CNT |
| -------- | -------- | ------- | --- |
| 471869   | 40161    | James   | 2   |
| 482047   | 40836    | Nick    | 2   |

But, need to have one more column with the contents of the entire row, like this

| ID       | USER_ID  | NAME    | CNT | ROW                                                      |
| -------- | -------- | ------- | --- | -------------------------------------------------------- |
| 471869   | 40161    | James   | 2   | <ROW ID="471869" USER_ID="40161" NAME="James" CNT="2" /> |
| 482047   | 40836    | Nick    | 2   | <ROW ID="482047" USER_ID="40836" NAME="Nick" CNT="2" />  |

Serg · Accepted Answer · 2021-05-05T11:01:35.120

0

Use the query method

Declare @xmlDoc xml = '
<ROW ID="471869" USER_ID="40161" NAME="James" CNT="2" />
<ROW ID="482047" USER_ID="40836" NAME="Nick" CNT="2" />
'


SELECT x.value('(@ID)[1]', 'int') AS ID,
       x.value('(@USER_ID)[1]', 'int') AS USER_ID,
       x.value('(@NAME)[1]', 'varchar(100)') AS NAME,
       x.value('(@CNT)[1]', 'int') AS CNT,
       x.query('.') cc
from @xmlDoc.nodes('/ROW') AS t(x)

cc type is XML.

db<>fiddle

edited May 05 '21 at 11:01

answered May 05 '21 at 10:53

Serg

22,285
5
21
48

TBF they are using query. They are just not using the correct syntax to bring back the current node. – Bee_Riii May 05 '21 at 10:56
sorry, works well, x.query instead of @xmlDoc.query – noanother May 05 '21 at 11:01

score 0 · Answer 2 · answered May 05 '21 at 10:54

This works for me.

SELECT x.value('(@ID)[1]', 'int') AS ID,
       x.value('(@USER_ID)[1]', 'int') AS USER_ID,
       x.value('(@NAME)[1]', 'varchar(100)') AS NAME,
       x.value('(@CNT)[1]', 'int') AS CNT,
       @xmlDoc.query('.') as cc
from @xmlDoc.nodes('/ROW') AS t(x)

The . selects the current node. I highly recommend checking out the XML tutorials on W3Schools. I spent a few years messing with xml on and off but never took the time to dive into it. It's so much easier once you get your head around how it works.

score 0 · Answer 3 · answered May 05 '21 at 11:11

Good example in here: selecting individual xml node using SQL

 Declare @xmlDoc xml = '
    <ROW ID="471869" USER_ID="40161" NAME="James" CNT="2" />
    <ROW ID="482047" USER_ID="40836" NAME="Nick" CNT="2" />
    '
    
    
    SELECT x.value('(@ID)[1]', 'int') AS ID,
           x.value('(@USER_ID)[1]', 'int') AS USER_ID,
           x.value('(@NAME)[1]', 'varchar(100)') AS NAME,
           x.value('(@CNT)[1]', 'int') AS CNT,
           x.query('.') as EntireXmlRow
    from @xmlDoc.nodes('/ROW') AS t(x)

Select node from xml nodes

3 Answers3