Menu
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.
756
You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in subqueries. The top-level FOR XML clause can be used only in the SELECT statement.
First, the sp_xml_preparedocument stored procedure parses the XML document. The parsed document is a tree representation of the nodes (elements, attributes, text, and comments) in the XML document. OPENXML then refers to this parsed XML document and provides a rowset view of all or parts of this XML document.
Many SQL statements support the XML data type. This enables you to perform many common database operations with XML data, such as creating tables with XML columns, adding XML columns to existing tables, creating triggers on tables with XML columns, and inserting, updating, or deleting XML documents.
Read the XML file using OPENROWSET, and save the data in the table. In the below code, we create a table for our raw XML data. Then read the XML file and save the same data into the "PointsXML" table. CREATE TABLE PointsXML -- Create table for raw XML file.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
