Section 7.4. Viewing XML Data as Relational Data


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




Programming SQL Server 2005
Programming SQL Server 2005
ISBN: 0596004796
EAN: 2147483647
Year: 2007
Pages: 147
Authors: Bill Hamilton

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net