Select Data From XML in MS SQL Server (T-SQL)
- by Doug Lampe
So you have used XML to give you some schema flexibility in your database, but now you need to get some data out. What do you do? The solution is relatively simple:
DECLARE @iDoc INT /* Stores a pointer to the XML document */
DECLARE @XML VARCHAR(MAX) /* Stores the content of the XML */
set @XML = (SELECT top 1 Xml_Column_Name FROM My_Table
where Primary_Key_Column = 'Some Value')
EXEC sp_xml_preparedocument @iDoc OUTPUT, @XML
SELECT *
FROM OPENXML(@iDoc,'/some/valid/xpath',2)
WITH (output_column1_name varchar(50) 'xml_node_name1',
output_column2_name varchar(50) 'xml_node_name2')
EXEC sp_xml_removedocument @iDoc
In this example, the XML data would look something like this:
<some>
<valid>
<xpath>
<xml_node_name1>Value1</xml_node_name1>
<xml_node_name2>Value2</cml_node_name2>
</xpath>
</valid>
</some>
The resulting query should give you this:
output_column1_name output_column2_name
------------------------------------------
Value1 Value2
Note that in this example we are only looking at a single record at a time. You could use a cursor to iterate through multiple records and insert the XML data into a temporary table.
