there! there is a small xml.
x
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" /> |
Advertisement
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.