Using OPENXML to Read XML

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 you are finished reading the XML by passing the variable represented by ixml to sp_xml_removedocument .

In the second parameter, pattern, specify an XPath query that identifies the elements or attributes in the input XML that OPENXML will map into rows. Each element that matches pattern creates a row in the rowset that is produced by OPENXML . In this chapter's example, the simple pattern 'sp' generates a single row that has no columns , because only one element is named sp . The example uses the WITH clause to add columns to this row by matching more of the XML input. This happens in the SchemaDeclaration parameter ”so called because its tuples establish the column names , datatypes, and values of the table that OPENXML creates. The syntax of the tuples is as follows :

  ColumnName datatype  [  ColumnPattern   MetaProperty  ]  [,  ColumnName datatype  [  ColumnPattern   MetaProperty  ]...] 

Each tuple adds a column ColumnName of type datatype to the rowset created by OPENXML when the XPath query specified in ColumnPattern matches. This chapter's example has two such tuples: CustomerID nvarchar(5) '@CustomerID' and EmployeeID int '@EmployeeID' .

The select statement below your call to OPENXML then uses the values selected FROM OPENXML into local variables in a subsequent query, which in turn returns an XML document that tells you which Orders were handled by EmployeeID 7 for CustomerID 'ANTON'. ( MetaProperty is an advanced parameter that provides detailed information about the input XML such as would be found in an XML schema.)

By far, the simplest way to use OPENXML is to specify a TableName parameter and no tuples in the WITH clause. Do this if your input XML will match all the required columns in your query in either an attribute or element-centric manner. This comes in handy in the case of insert or update queries, although your input XML is now strongly tied to the underlying table structure. Listing 41.12 provides an example of an insert query using OPENXML and a tablename.

Listing 41.12 An Insert Query Using OPENXML

 CREATE PROCEDURE dbo.I_NEW_SHIPPERS_XML (@xml varchar(1000)) as declare       @ixml int,        @ShipperID nvarchar(5),        @rc int EXECUTE sp_xml_preparedocument @ixml OUTPUT, @xml INSERT INTO Shippers SELECT CompanyName, Phone FROM OPENXML(@ixml, 'sp/Shippers') WITH Shippers SELECT @rc = @@ROWCOUNT EXEC sp_xml_removedocument @ixml IF @rc > 0 begin        select '<Success rv=''0'' msg=''Created ' +                         cast(@rc as varchar(4)) + ' new Shippers''/>'        return 0 end else begin        select '<Failure rv=''0'' msg=''Failed to create any new Shippers''/>'        return 1 end 

The input XML and its result are as follows:

 I_NEW_SHIPPERS_XML '<sp>        <Shippers CompanyName=''House of Shipping'' Phone=''555-5555''/>        <Shippers CompanyName=''World of Shipping'' Phone=''555-5554''/> </sp>' go XML_F52E2B61-18A1-11d1-B105-00805F49916B ---------------------------------------------- <Success rv='0' msg='Created 2 new Shippers'/> 

Notice how the example input looks a bit like AUTO mode output? The XPath pattern used in OPENXML first matches every Shippers element, then uses attribute-centric mapping (the default flags parameter) to create a column value for every attribute that matches Shippers columns. In addition, it's a good idea to send an XML-formatted string indicating success or failure back to the calling application.

OPENXML 's flags parameter can also be changed to alter how you want OPENXML to map the various types of XML input you might have into rowsets when you specify TableName or use SchemaPattern tuples but don't specify a column pattern.

OPENXML uses the value of flags to map your XML into a rowset based on the following possible values:

• 0 ”The default: Apply attribute-centric mapping to the XML input.

• 1 ”Apply attribute-centric mapping to the XML input. Combinable with 2.

• 2 ”Apply element-centric mapping to the XML input. Combinable with 1.

• 8 ”For use with metaproperties ”Indicates that the XML should not be copied to the overflow metaproperty @mp:xmltext . Combinable with 1 or 2.

As you can see, SQL Server 2000 is flexible in terms of the type of XML input you can send to your stored procedures and the ways it can be unpacked and read using OPENXML .