Microsoft® SQL Server 2008 R2 Unleashed

XML As Relational Data: Using OPENXML

This section covers what might be called the inverse of FOR XML: OPENXML. You use OPENXML in T-SQL queries to read XML data and shred (or decompose) it into relational result sets. OPENXML is part of the SELECT statement, and you use it to generate a table from an XML source.

The first step required in this process is a call to the system stored procedure sp_xml_preparedocument. sp_xml_preparedocument creates an in-memory representation of any XML document tree for use in querying. It takes the following parameters:

• An integer output parameter for storing a handle to the document tree

• The XML input data

• An optional XML namespace declaration, used in subsequent OPENXML queries

sp_xml_preparedocument ...