Microsoft® SQL Server™ 2000 Unleashed, Second Edition

Using OPENXML to Read XML

Inside stored procedures, you retrieve values from your XML input into your local variables using the OPENXML extension. Following is the syntax for OPENXML:

OPENXML(ixml int, pattern nvarchar,[flags byte]) 
[WITH (SchemaDeclaration | TableName)]

OPENXML is known as a rowset provider: Based on its parameters, it maps XML into a rowset against which you can perform queries by specifying FROM OPENXML as you would FROM tablename(s).

Before you can use OPENXML, you need to call the system-stored procedure sp_xml_preparedocument and pass it @xml as input and @ixml as an OUTPUT parameter. It returns a reference to @xml in @ixml for use as OPENXML's first parameter (ixml). Free the memory allocated for this reference when ...