JavaScript Editor Ajax toolkit     Ajax tutorials 

Main Page

Previous Page
Next Page

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]



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


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


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




Defaults to attribute-centric mapping. 0 is the default if the flags option is not specified.


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.


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.


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.


The schema definition, in the form where:


The name of the column in the rowset.


The SQL data type of the column in the rowset.


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.


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


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




Unique ID of the XML document node. The root node has an id value of 0. Negative values are reserved.



The ID of the parent node. The root node has a parentid value of NULL.



The node type based on XML DOM node type numbering, where 1 = element node; 2= attribute node; 3 = text node.



The local name of the element or attribute. The localname value is NULL if the DOM object does not have a name.



The namespace prefix of the node name.



The namespace URI of the node. The namespaceuri value is NULL if a namespace is not present.



The actual data type of the element or attribute, and NULL otherwise. The data type is inferred from the inline DTD or schema.



The node ID of the previous sibling element. The prev value is NULL if there is no direct previous sibling.



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 
       [ , xmlText 
 ] [ , xpathNamespaces ]



The handle to the parsed internal representation of the XML document


The original XML document


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



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="
      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)
          <material>aluminum sheet MS-2341</material>
          into the
          <tool>T-85A framing tool</tool>


      <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>



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="
        adventure-works/ProductModelManuInstructions" />'

(@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

Previous Page
Next Page

JavaScript Editor Ajax toolkit     Ajax tutorials