7.4. Viewing XML Data as Relational Data

OPENXML is a T-SQL function that provides access to in-memory XML documents through a mechanism similar to a relational result set. OPENXML can be used in SELECT and SELECT INTO statements wherever rowset providers such as a table or view, or the OPENROWSET function, can appear.

Before you can use OPENXML, you must call the system stored procedure sp_xml_preparedocument to parse the XML document and return the handle of the parsed internal representation of the document. The document handle is passed to OPENXML, which provides a relational rowset view of the document.

The syntax of the OPENXML keyword is:

    OPENXML( idoc, 
 rowpattern, [flags 
] )
      [ WITH ( <schemaDeclaration>  [ ,...n ] | tableName ) ]

    <schemaDeclaration> ::=
      colName colType [colPattern | metaProperty]

where:

idoc

The document handle of the internal representation of the XML document. The handle is obtained using the sp_xml_preparedocument system stored procedure.

rowpattern

The XPath pattern that identifies the nodes in the XML document to be processed as rows.

flags

Optionally specifies the mapping between the XML data and the rowset, and how the spillover column should be filled. The flags option is a byte created from the values described in Table 7-2.

Table 7-2. OPENXML flags option values

Value	Description
0	Defaults to attribute-centric mapping. 0 is the default if the flags option is not specified.
1	Attribute-centric mappingXML attributes map to the columns defined in schemaDeclaration . When combined with XML_ELEMENTS, attribute-centric mapping is applied first followed by element-centric mapping for all unmapped columns.
2	Element-centric mappingXML elements map to the columns specified in schemaDeclaration. When combined with XML_ATTRIBUTES, element-centric mapping is applied first followed by attribute-centric mapping for all unmapped columns.
8	In the context of retrieval, indicates that consumed data should not be copied to the overflow property @mp:xmltext. This flag can be combined with XML_ATTRIBUTES or XML_ELEMENTS.

schemaDeclaration

The schema definition, in the form where:

colName

The name of the column in the rowset.

colType

The SQL data type of the column in the rowset.

colPattern

An XPath pattern specifying how XML nodes are mapped to columns in the rowset. The colPattern mapping overrides the mapping specified by the flags option.

metaProperty

An OPENXML metaproperty that lets you extract information about XML nodes, including relative position and namespace information.

tableName

A table name can be specified instead of a schema definition if a table with the desired schema exists and column patterns are not needed.

If the WITH clause is not specified, the results are returned in edge table format instead of a rowset format. An edge table represents an XML document in a single table with the structure described in Table 7-3.

Table 7-3. Edge table schema

Column name	Data type	Description
id	bigint	Unique ID of the XML document node. The root node has an id value of 0. Negative values are reserved.
parentid	bigint	The ID of the parent node. The root node has a parentid value of NULL.
nodetype	int	The node type based on XML DOM node type numbering, where 1 = element node; 2= attribute node; 3 = text node.
localname	nvarchar	The local name of the element or attribute. The localname value is NULL if the DOM object does not have a name.
prefix	nvarchar	The namespace prefix of the node name.
namespaceuri	nvarchar	The namespace URI of the node. The namespaceuri value is NULL if a namespace is not present.
datatype	nvarchar	The actual data type of the element or attribute, and NULL otherwise. The data type is inferred from the inline DTD or schema.
prev	bigint	The node ID of the previous sibling element. The prev value is NULL if there is no direct previous sibling.
text	ntext	The element content or attribute value in text form. The text value is NULL if the edge table does not need a value for the entry.

The syntax of the sp_xml_preparedocument system stored procedure is:

    sp_xml_preparedocument hDoc 
 OUTPUT
       [ , xmlText 
 ] [ , xpathNamespaces ]

where:

hDoc

The handle to the parsed internal representation of the XML document

xmlText

The original XML document

xPathNamespaces

The namespaces used in row and column XPath expressions in OPENXML

Once you have finished using the internal representation of the document, call the sp_xml_removedocument system stored procedure to remove it and invalidate the document handle. The syntax of sp_xml_removedocument is:

    sp_xml_removedocument hDoc

where:

hDoc

The handle to the parsed internal representation of the XML document

The following example uses OPENXML to extract manufacturing location information for product model 7 from the Instructions xml data type column in the Production.ProductModel table in AdventureWorks. An excerpt of the data follows:

    <root xmlns="http://schemas.microsoft.com/sqlserver/2004/07/
      adventure-works/ProductModelManuInstructions">
      Adventure Works CyclesFR-210B Instructions for Manufacturing HL Touring
      FrameSummaryThis document contains manufacturing instructions for
      manufacturing the HL Touring Frame, Product Model 7. Instructions are

    ...

      <Location LaborHours="2.5" LotSize="100" MachineHours="3" SetupHours="0.5"
        LocationID="10">Work Center - 10 Frame FormingThe following instructions
        pertain to Work Center 10. (Setup hours = .5, Labor Hours = 2.5,
        Machine Hours = 3, Lot Sizing = 100)
        <step>Insert
          <material>aluminum sheet MS-2341</material>
          into the
          <tool>T-85A framing tool</tool>
          .
        </step>

    ...

      </Location>
      <Location LaborHours="1.75" LotSize="1" MachineHours="2" SetupHours="0.15"
        LocationID="20">Work Center 20 - Frame WeldingThe following instructions
        pertain to Work Center 20. (Setup hours = .15, Labor Hours = 1.75,
    . </Location>

    ...

    </root>

Execute the following statement to extract manufacturing location information for product model ID 7 as a tabular result set. Note that you must enter the emphasized line in the example on a single line rather than on two lines, done here only to fit the page width.

    USE AdventureWorks

    DECLARE @idoc int
    DECLARE @instructions xml
    SET @instructions = (SELECT Instructions FROM Production.ProductModel
      WHERE ProductModelID = 7)

    EXEC sp_xml_preparedocument @idoc OUTPUT, @instructions,
      N'<root xmlns:ns="http://schemas.microsoft.com/sqlserver/2004/07/
        adventure-works/ProductModelManuInstructions" />'

    SELECT * FROM OPENXML 
(@idoc, N'/ns:root/ns:Location')
    WITH (
      LaborHours float N'@LaborHours',
      LotSize float '@LotSize',
      MachineHours float '@MachineHours',
      SetupHours float '@SetupHours',
      LocationID int '@LocationID'
    )

    EXEC sp_xml_removedocument @idoc

The result set is shown in Figure 7-10.

Figure 7-10. Result set for OPENXML example