SQL processing XML

Use sp_xml_preparedocument to read XML text.

Reads the XML text provided as input, then parses it using the MSXML parser (Msxmlsql.dll) and provides the parsed document for use. The parsed document is a tree representation of each node (element, attribute, text, comment, and so on) in the XML document.

Prepare an internal representation for a properly formatted XML document

--The first parameter:

--a handle to the newly created document. @hdoc is an integer.

DECLARE @hdoc INT

--The second parameter:

--is the original XML document. The MSXML parser parses the XML document.

--@doc is a text parameter: char, nchar, varchar, nvarchar, text, ntext, or XML.

--The default value is null, in which case an internal representation of an empty XML document is created.

DECLARE @doc VARCHAR (1000)

--A third parameter:

--[xpath_namespaces]

--Specifies the namespace declaration that is used in the OpenXML row and column XPath expressions. Xpath_namespaces is a text parameter: char, nchar, varchar, nvarchar, text, ntext, or XML.

--The default value is <root xmlns:mp= "Urn:schemas-microsoft-com:xml-metaprop" >.

--Xpath_namespaces provides a namespace URI for the prefix used in XPath expressions in OPENXML through a well-formed XML document.

--The Xpath_namespaces declaration must use a prefix to reference the namespace Urn:schemas-microsoft-com:xml-metaprop; This provides metadata about the parsed XML element.

--although you can use this technique to redefine the namespace prefix for a meta-attribute namespace, the namespace is not lost.

--even if xpath_namespaces does not contain such declarations, the prefix MP is still valid for Urn:schemas-microsoft-com:xml-metaprop.

SET @doc = '

<ROOT>

<customer customerid= "vinet" contactname= "Paul Henriot" >

<order customerid= "Vinet" employeeid= "5" orderdate= "1996-07-04t00:00:00" >

<orderdetail orderid= "10248" productid= "one" quantity= "/>"

<orderdetail orderid= "10248" productid= "ten" quantity= "/>"

</Order>

</Customer>

<customer customerid= "Lilas" contactname= "Carlos Gonzlez" >

<order customerid= "Lilas" employeeid= "3" orderdate= "1996-08-16t00:00:00" >

<orderdetail orderid= "10283" productid= "3" quantity=

</Order>

</Customer>

</ROOT> '

EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

EXEC sp_xml_removedocument @hdoc

Places to be aware of

• Returns 0 success, >0 failed. Membership in the public role is required.
• The parsed document is stored in the internal cache of SQL Server 2005. The MSXML parser consumes one-eighth of the total memory available for SQL Server. To avoid low memory, run sp_xml_removedocument to free up memory.
• sp_xml_preparedocument limits the maximum number of elements that can be opened at the same time to 256.

Using OPENXML to provide a rowset view through an XML document

Grammar:

OPENXML (idoc int [in], rowpattern nvarchar [in], [flags byte [in]])

[With (SchemaDeclaration | TableName)]

Parameters:

• IDoc

  The document handle in the form of an internal form of an XML document. Create an internal form of the XML document by calling sp_xml_preparedocument.

• Rowpattern

  The XPath pattern, which identifies the nodes to be processed as rows (the handles of the document are passed by the IDoc parameter in the XML document).

• Flags

  Indicates whether the mapping should be used between the XML data and the relational rowset and how the overflow column should be populated. Flags is an optional input parameter and can be one of the following values:

  • 0

  The default is attribute-centric mapping.

  • 1

  Use the attribute-centric mapping. Can be used with xml_elements. In this case, the attribute-centric mapping is applied first, and then the element-centric mapping is applied to all the unhandled columns.

  • 2

  Use "element-centric" mapping. Can be used with xml_attributes. In this case, the attribute-centric mapping is applied first, and then the element-centric mapping is applied to all the unhandled columns.

  • 8

  Can be used in combination with xml_attributes or xml_elements (logical OR). In the context of the retrieval, this flag indicates that the data that has been used should not be copied to the overflow property @mp: xmltext.

• SchemaDeclaration

  Schema definition for form: colname Coltype [ColPattern | MetaProperty] [, Colnamecoltype [ColPattern | MetaProperty] [...]

• ColName

The column name in the rowset.

• Coltype

The SQL Server data type for the columns in the rowset. Type coercion occurs if the column type differs from the underlying XML data type of the property.

• ColPattern

An optional generic XPath pattern that shows how XML nodes should be mapped to columns. If ColPattern is not specified, a default mapping occurs (the "attribute-centric" or "element-centric" mapping specified by Flags).

The XPath pattern specified as ColPattern is used to specify special mapping properties (if "attribute-centric" and "element-centric" mappings occur), these special mapping properties can override or enhance the default mappings indicated by flags.

The generic XPath pattern specified as ColPattern also supports meta attributes.

• MetaProperty

One of the meta attributes provided by OPENXML. If MetaProperty is specified, the column contains the information provided by the META attribute. You can use Meta attributes to extract information about an XML node, such as relative location and namespace information. It provides more detailed information than the text representation.

• TableName

If the table with the desired schema already exists and does not require a column pattern, the given table name (not schemadeclaration).

Tip: Provide a rowset format by using SchemaDeclaration or specifying an existing TABLENAME,WITH clause (additional mapping information can be provided as needed). If an optional with clause is not specified, the result is returned as an "edge" table. The edge table represents the fine-grained structure of an XML document in a single table (for example, element/attribute names, document hierarchies, namespaces, processing instructions, and so on).

The following table describes the structure of the edge table.

Column Name	Data type	Description
Id	bigint	The unique ID of the document node. The ID value of the root element is 0. Retains the negative ID value.
ParentID	bigint	Identifies the parent node of the node. The parent node identified by this ID is not necessarily a parent element, but is dependent on the NodeType of the child nodes of the node identified by this ID. For example, if a node is a text node, its parent node might be an attribute node. If the node is at the top level of the XML document, its parentid is NULL.
NodeType	Int	Identifies the node type. An integer that corresponds to the XML DOM node type number. The node types are: 1 = element node 2 = attribute Node 3 = text node
LocalName	nvarchar	gives the local name of the element or attribute. Null if the DOM object does not have a name.
Prefix	nvarchar	namespace prefix for the node name.
NamespaceURI	nvarchar	The namespace URI of the node. If the value is NULL, the namespace does not exist.
DataType	nvarchar	The actual data type of the element or attribute row, otherwise null. Infer data types from inline DTDs or from inline schemas.
Prev	bigint	The XML ID of the previous sibling element. Null if there is no sibling element in front of it.
Text	ntext	A property value or element content that contains text formatting (or NULL if the edge table item does not require a value).

Using a simple SELECT statement with OPENXML

DECLARE @idoc int

DECLARE @doc varchar (1000)

SET @doc = '

<ROOT>

<customer customerid= "vinet" contactname= "Paul Henriot" >

<order customerid= "Vinet" employeeid= "5" orderdate= "1996-07-04t00:00:00" >

<orderdetail orderid= "10248" productid= "one" quantity= "/>"

<orderdetail orderid= "10248" productid= "ten" quantity= "/>"

</Order>

</Customer>

<customer customerid= "Lilas" contactname= "Carlos Gonzlez" >

<order customerid= "Lilas" employeeid= "3" orderdate= "1996-08-16t00:00:00" >

<orderdetail orderid= "10283" productid= "3" quantity=

</Order>

</Customer>

</ROOT> '

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT

*

From OPENXML (@idoc, '/root/customer ', 1)

With

(

CustomerID varchar (10),

ContactName varchar (20)

)

Execution Result:

CustomerID	ContactName
Vinet	Paul Henriot
Lilas	Carlos Gonzlez

The above uses sp_xml_preparedocument to create an internal representation of the XML image. The SELECT statement that uses the OPENXML rowset provider is then executed on the internal representation of the XML document.

The flag value is set to 1. This value indicates a "property-centric" mapping. Therefore, the XML attribute is mapped to a column in the rowset. The rowpattern that is specified as/root/customer identifies the <Customers> node to be processed.

The optional ColPattern (column mode) parameter is not specified because the column name matches the XML property name.

The OPENXML rowset provider creates a two-column rowset (CustomerID and ContactName), and the SELECT statement retrieves the necessary columns from the rowset.

Specify ColPattern for mappings between columns and XML attributes

DECLARE @idoc int

DECLARE @doc varchar (1000)

SET @doc = '

<ROOT>

<customer customerid= "vinet" contactname= "Paul Henriot" >

<order orderid= "10248" customerid= "Vinet" employeeid= "5"

Orderdate= "1996-07-04t00:00:00" >

<orderdetail productid= "One" quantity= "one"/>

<orderdetail productid= "quantity="/>

</Order>

</Customer>

<customer customerid= "Lilas" contactname= "Carlos Gonzlez" >

<order orderid= "10283" customerid= "Lilas" employeeid= "3"

Orderdate= "1996-08-16t00:00:00" >

<orderdetail productid= "quantity=" 3 "/>

</Order>

</Customer>

</ROOT> '

--create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

--SELECT stmt using OPENXML rowset provider

SELECT *

From OPENXML (@idoc, '/root/customer/order/orderdetail ', 2)

With (OrderID int ' ... /@OrderID ',

CustomerID varchar (10) '. /@CustomerID ',

OrderDate datetime '. /@OrderDate ',

ProdID int ' @ProductID ',

Qty int ' @Quantity ')

Execution Result:

OrderID	CustomerID	OrderDate	ProdID	Qty
10248	Vinet	1996-7-4	11	12
10248	Vinet	1996-7-4	42	10
10283	Lilas	1996-8-16	72	3

The above query returns attributes such as Customer ID, order date, product ID, and quantity from an XML document. rowpattern identification <OrderDetails> elements. ProductID and Quantity are attributes of the <OrderDetails> element. OrderID, CustomerID, and OrderDate are attributes of the parent element (<Orders>).

Specifies an optional ColPattern. This includes the following:

• The OrderID, CustomerID, and OrderDate in the rowset map to the parent node property of the rowpattern identified node in the XML document.
• The ProdID column in the rowset is mapped to the ProductID property, and the Qty column in the rowset maps to the Quantity property of the node identified in Rowpattern.

Although the element-centric mapping is specified by the flags parameter, the mapping specified in ColPattern has a higher precedence than the mapping.

And look at the other one:

DECLARE @idoc int

DECLARE @doc varchar (1000)

SET @doc = '

<ROOT>

<Customer>

<CustomerID>1</CustomerID>

<contactname>paul henriot</contactname>

<order customerid= "1" employeeid= "5" >

<orderdetail orderid= "10248" productid= "one" quantity= "/>"

<orderdetail orderid= "10248" productid= "ten" quantity= "/>"

</Order>

</Customer>

<Customer>

<CustomerID>2</CustomerID>

<contactname>carlos gonzlez</contactname>

<order customerid= "2" employeeid= "3" >

<orderdetail orderid= "10283" productid= "3" quantity=

</Order>

</Customer>

</ROOT> '

--create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

--Execute A SELECT statement that uses the OPENXML rowset provider.

SELECT *

From OPENXML (@idoc, '/root/customer ', 2)

With (---CustomerID int,

ContactName varchar (20),

CustomerID int ' order/@CustomerID ',

EmployeeID int ' order/@EmployeeID ',

OrderID INT ' order/orderdetail/@OrderID ',

ProductID INT ' order/orderdetail/@ProductID ',

Quantity INT ' order/orderdetail/@Quantity '

)

EXEC sp_xml_removedocument @idoc

Execution Result:

ContactName	CustomerID	EmployeeID	OrderID	ProductID	Quantity
Paul Henriot	1	5	10248	11	12
Carlos Gonzlez	2	3	10283	72	3

This result has only two lines of record, this is because the choice of XPath is different, the above option is/root/customer, so the time to take this XPath is based on this.

Transferred from: http://www.cnblogs.com/sherrys/archive/2007/06/07/775511.html

SQL processing XML