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.

Cold you change the schema? as it would be easier if those IDS were in separate rows in a table i.e parse the XML before inserting the data in the table. — mmmmmm
– mmmmmm, Commented Jan 12, 2011 at 14:32
If your data are stored as XML, you should use an XML parser instead of trying to write one by hand. If you don't need it to be XML, use a simpler format. — Borealid
– Borealid, Commented Jan 12, 2011 at 14:38

Mikael Eriksson · Accepted Answer · 2014-06-09 07:31:58Z

8

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)

edited Jun 9, 2014 at 7:31

answered Jan 12, 2011 at 14:38

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

XML Parsing with SQL query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related