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.