How to shred XML value in to a column in MSSQL?

Question

Here is my code:

DECLARE @xml XML
set @xml ='
<settings>
<value>1306</value>
<value>1307</value>
<value>1310</value>
</settings>'


select tabl.col.value ('value[1]','int')
from @xml.nodes ('/settings') tabl (col)
cross apply tabl.col.nodes ('value') as tabl1 (col1)

Output received :

1306

Excepted output:

1306

1307

1310

Please point out where my mistake is...

score 2 · Accepted Answer · answered Jun 07 '18 at 10:22

2

You don't need to use cross apply, you can directly express it as

select tabl.col.value ('.','int')
from @xml.nodes ('settings/value') tabl (col);

answered Jun 07 '18 at 10:22

Yogesh Sharma

49,870
5
26
52

This answer is a good one (+1 from my side), but I'd replace the `.` with `text()[1]`. You might [read this](https://stackoverflow.com/a/43242238/5089204) to understand why. In this easy case it's perfectly okay though. And - in general - it's recommended to start with a single `/` in order to mark the entry point as the root element (`/settings/value`). – Shnugo Jun 07 '18 at 11:28
Thanks for the solution – Mar1009 Jun 08 '18 at 04:45

score 1 · Answer 2 · answered Jun 07 '18 at 11:32

Please point out where my mistake is...

select tabl.col.value ('value[1]','int')
from @xml.nodes ('/settings') tabl (col)
cross apply tabl.col.nodes ('value') as tabl1 (col1)

Your mistake: from @xml.nodes() is not pulling the repeating element <value> but is calling for <settings>. There is one single element. Now you pick the first value ('value[1]'). This would return 1 row with "1306".

But you add a cross apply with a relativ reference to <value> below <settings>. This does return 3 rows, but the results are not used anywhere.

Therefore you get the single result 1306 repeated for each row.

The solution how to solve this is given in the other answer.

How to shred XML value in to a column in MSSQL?

2 Answers2