Select node from xml nodes

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" />  |

Answer

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

Advertisement

Answer