XML Parsing with SQL query

Question

I am trying to parse the below xml in sql server to get all the 3 Ids

<Configuration>
  <ID>1000</ID>
  <ID>1001</ID>
  <ID>1002</ID>
</Configuration>

using the query

SELECT  CONFIGURATION.value('/', 'varchar(200)') as SID FROM SCHEDULE 

am getting the results as 100010011002 but i would like to have the results in a column or in a CSV format.

any help would be appriciated.

Answer 1

Using MS SQL Server, this will give you rows.

declare @xml as xml
set @xml = 
'<Configuration>
  <ID>1000</ID>
  <ID>1001</ID>
  <ID>1002</ID>
</Configuration>'

select
    x.i.value('.', 'int') as ID
from @xml.nodes('/Configuration/ID') as x(i)

---

If you only need one value like in this example it is a lot faster (three times) to shred on the text() node.

select
    x.i.value('.', 'int') as ID
from @xml.nodes('/Configuration/ID/text()') as x(i)