How to Open XML data using SQL

Problem: How to Open XML data using SQL

Solution:

OPENXML (Transact-SQL)

OPENXML provides a rowset view over an XML document. Because OPENXML is a rowset provider, OPENXML can be used in Transact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear.

Example:

In this article I am going to explain how to extract values from an xml data. If you can see the below xml I have a pattern on the nodes ‘row/values’ for each row in the variable.

We are passing a varchar to sp_xml_preparedocument system stored procedures which internally represents the xml and gives an integer value as output to handle the document.

For using OPENXML we need to pass few arguments in the document handle output from sp_xml_preparedoucment so it knows which one to use, the row pattern and lastly the flags (0,1,2,8).

Please see below example code:

Declare @xml  VARCHAR(1000)

SELECT @xml=

'<Row>

  <values col1="Zone5" col2="4" col3="1445">

  </values>

  <values col1="Zone6" col2="7" col3="3250">

  </values>

  <values col1="Zone7" col2="5" col3="4440">

  </values>

</Row>'

declare @a int

exec sp_xml_preparedocument @a out, @xml

select  * FROM OPENXML (@a,'/Row/values',1)

with (col1 varchar(10), col2 int, col3 int);

Ouptut: