Tutorial OpenXML

(1)

Transact-SQL Reference (SQL Server 2000)

OPENXML

OPENXML provides a rowset view

OPENXML provides a rowset view over an XML document. Because OPENXML is a rowset provider, over an XML document. Because OPENXML is a rowset provider, OPENXMLOPENXML can be used in Transact-SQL statements in which rowset providers such as a table,

can be used in Transact-SQL statements in which rowset providers such as a table, view, or theview, or the OPENROWSET function can appear.

OPENROWSET function can appear. Syntax

Syntax OPENXML

OPENXML((idoc idoc intint [[inin],],rowpatternrowpattern nvarcharnvarchar[[inin],[],[flagsflags bytebyte[[inin]]]])) [WITH (

[WITH (SchemaDeclarationSchemaDeclaration || TableNameTableName)])] Arguments

Arguments idoc

idoc

Is the document handle of the internal representation of

Is the document handle of the internal representation of an XML document. The internal representation of an XML document. The internal representation of an XML document is created by calling

an XML document is created by calling sp_xml_preparedocumentsp_xml_preparedocument.. rowpattern

rowpattern

Is the XPath pattern used to identify the

Is the XPath pattern used to identify the nodes (in the XML document whose handle is passed in nodes (in the XML document whose handle is passed in thethe idoc idoc parameter) to be processed as rows.

parameter) to be processed as rows. flags

flags

Indicates the mapping that should be used between

Indicates the mapping that should be used between the XML data and the relational rowset, and how thethe XML data and the relational rowset, and how the spill-over column should be filled.

spill-over column should be filled. flagsflags is an optional input parameter, and can be one ois an optional input parameter, and can be one o f these values.f these values.

Byte Byte Va

Valulue e DeDescscririptptioionn 0

0 Defaults to attribute-centric mapping.Defaults to attribute-centric mapping. 1

1 Use the attribute-centric mapping.Use the attribute-centric mapping.

Can be combined with XML_ELEMENTS; in which case, attribute-centric mapping is applied Can be combined with XML_ELEMENTS; in which case, attribute-centric mapping is applied first,first, and then element-centric mapping is applied for

and then element-centric mapping is applied for all columns not yet dealt with.all columns not yet dealt with. 2

2 Use the element-centric mapping.Use the element-centric mapping.

Can be combined with XML_ATTRIBUTES; in which case, attribute-centric mapping is applied first, Can be combined with XML_ATTRIBUTES; in which case, attribute-centric mapping is applied first, and then element-centric mapping is applied for

and then element-centric mapping is applied for all columns not yet dealt with.all columns not yet dealt with. 8

8 Can be combined (logical OR) with XML_ATTRIBUTES or XML_ELEMENTS.Can be combined (logical OR) with XML_ATTRIBUTES or XML_ELEMENTS.

In context of retrieval, this flag indicates that the consumed data should not be

In context of retrieval, this flag indicates that the consumed data should not be copied to thecopied to the overflow property

overflow property @mp:xmltext@mp:xmltext..

SchemaDeclaration SchemaDeclaration

Is the schema definition of the form: Is the schema definition of the form: ColName

ColName ColTypeColType [[ColPattern | MetaProperty ColPattern | MetaProperty ][,][, ColNameColName ColTypeColType [[ColPatternColPattern || MetaProperty MetaProperty ]...]]...] ColName

ColName

Is the column name in the rowset. Is the column name in the rowset. ColType

(2)

Is the SQL data type of the

Is the SQL data type of the column in the rowset. If the column types differ column in the rowset. If the column types differ from the underlyingfrom the underlying XML data type of the attribute, type coercion occurs. If the column is of type

XML data type of the attribute, type coercion occurs. If the column is of type timestamptimestamp, the, the present value in the XML document is disregarded when selecting from an OPENXML rowset, and present value in the XML document is disregarded when selecting from an OPENXML rowset, and the autofill values are returned.

the autofill values are returned. ColPattern

ColPattern

Is an optional, general XPath pattern that describes how the XML nodes should

Is an optional, general XPath pattern that describes how the XML nodes should be mapped to thebe mapped to the columns. If the

columns. If the ColPatternColPattern is not specified, the default mapping (is not specified, the default mapping ( attribute-centric or element-centricattribute-centric or element-centric mapping as specified by

mapping as specified by flagsflags) takes place.) takes place. The XPath pattern specified as

The XPath pattern specified as ColPatternColPattern is used to specify the special nature of the is used to specify the special nature of the mapping (in case of mapping (in case of attribute-centric and element-centric mapping) that overwrites or enhances the default mapping indicated attribute-centric and element-centric mapping) that overwrites or enhances the default mapping indicated by

by flagsflags..

The general XPath pattern specified as

The general XPath pattern specified as ColPatternColPattern also supports the metaproperties.also supports the metaproperties. MetaProperty

MetaProperty

Is one of the metaproperties provided

Is one of the metaproperties provided by OPENXML. If the metaproperty is specified, by OPENXML. If the metaproperty is specified, the columnthe column contains information provided by the metaproperty. The metaproperties allow you to extract contains information provided by the metaproperty. The metaproperties allow you to extract information (such as relative position, , namespace information) about XML nodes, which provides information (such as relative position, , namespace information) about XML nodes, which provides more information than is visible in the textual representation.

more information than is visible in the textual representation. TableName

TableName

Is the table name that can be give

Is the table name that can be give n (instead of n (instead of SchemaDeclarationSchemaDeclaration)) if a table with the desired schemaif a table with the desired schema already exists and no column patterns are required.

already exists and no column patterns are required.

The WITH clause provides a rowset format (and additional mapping information as necessary) using either The WITH clause provides a rowset format (and additional mapping information as necessary) using either SchemaDeclaration

SchemaDeclaration or specifying an existingor specifying an existing TableNameTableName. If the optional WITH clause is . If the optional WITH clause is not specified, thenot specified, the results are returned in an

results are returned in an edge tableedge table format. Edge tables represent the fine-grained XML documentformat. Edge tables represent the fine-grained XML document structure (e.g. element/attribute names, the document hierarchy, the namespaces, PIs etc.) in a single structure (e.g. element/attribute names, the document hierarchy, the namespaces, PIs etc.) in a single table.

table.

This table describes the structure of the edge table This table describes the structure of the edge table ..

C

Coolluummn n nnaamme e DDaatta a ttyyppe e DDeessccrriippttiioonn

iid d bbiiggiinntt Is the unique ID of the document node.Is the unique ID of the document node. The root element has an ID value

The root element has an ID value 0. The negative ID values are0. The negative ID values are reserved.

reserved. p

paarreennttiid d bbiiggiinntt Identifies the parent of the node. The Identifies the parent of the node. The parent identified by this ID isparent identified by this ID is not necessarily the parent element, but it depends on

not necessarily the parent element, but it depends on the NodeType of the NodeType of the node whose parent is identified by this ID. For example, if the the node whose parent is identified by this ID. For example, if the node is a text node, the parent of

node is a text node, the parent of it may be an attribute node.it may be an attribute node. If the node is at the top

If the node is at the top level in the XML document, itslevel in the XML document, its ParentIDParentID isis NULL.

NULL. n

nooddeettyyppe e iinntt Identifies the node type. Is an Identifies the node type. Is an integer that corresponds to the XMLinteger that corresponds to the XML DOM node type numbering (see DOM for node information).

DOM node type numbering (see DOM for node information). The node types are:

The node types are: 1

1 = Element node= Element node 2

2 = Attribute node= Attribute node 3

3 = Text node= Text node

llooccaallnnaamme e nnvvaarrcchhaarr Gives the local name of the eleGives the local name of the ele ment or attribute. Is NULL if the DOMment or attribute. Is NULL if the DOM object does not have a name.

object does not have a name. p

prreeffiix x nnvvaarrcchhaarr Is the namespace prefix of the node Is the namespace prefix of the node name.name. na

namemespspacaceeururi i nnvvararchcharar Is the namespace URI of the node. IIs the namespace URI of the node. I f the value is NULL, no namespacef the value is NULL, no namespace is present.

is present. d

(3)

otherwise. The data type is inferred from

otherwise. The data type is inferred from the inline DTD or from thethe inline DTD or from the inline schema.

inline schema. p

prreev v bbiiggiinntt Is the XML ID of Is the XML ID of the previous sibling element. Is NULL if the previous sibling element. Is NULL if there is nothere is no direct previous sibling.

direct previous sibling.

tteexxt t nntteexxtt Contains the attribute value or the element content in text form Contains the attribute value or the element content in text form (or is(or is NULL if the edge table entry does not need a value).

NULL if the edge table entry does not need a value).

Examples Examples

A. Use a simple

A. Use a simple SELECT statement with OPENXMLSELECT statement with OPENXML

This example creates an internal representation of the XML image using

This example creates an internal representation of the XML image using sp_xml_preparedocumentsp_xml_preparedocument. A. A SELECT statement using an OPENXML rowset provider is then executed against the internal representation SELECT statement using an OPENXML rowset provider is then executed against the internal representation of the XML document.

of the XML document. The

The flagflag value is set tovalue is set to 11 indicating attribute-centric mapping. Therefore, the XML attributes map to theindicating attribute-centric mapping. Therefore, the XML attributes map to the columns in the rowset. The

columns in the rowset. The rowpatternrowpattern specified asspecified as /ROOT/Customers/ROOT/Customers identifies the <Customers> nodesidentifies the <Customers> nodes to be processed.

to be processed. The optional

The optional ColPatternColPattern (column pattern) is not specified because the column name matches the XML(column pattern) is not specified because the column name matches the XML attribute names.

attribute names.

The OPENXML rowset provider creates a two-column rowset (

The OPENXML rowset provider creates a two-column rowset ( CustomerIDCustomerID andand ContactNameContactName) from which) from which the SELECT statement retrieves the necessary columns (in this case, all the columns).

the SELECT statement retrieves the necessary columns (in this case, all the columns).

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

04T00:00:00">

<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>

<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>

</Order>

</Customer>

<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">

<Order CustomerID="LILAS" EmployeeID="3"

OrderDate="1996-08-16T00:00:00">

16T00:00:00">

<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>

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

*

SELECT

*

FROM

OPENXML

(@idoc,

'/ROOT/Custo

mer',1)

WITH

(CustomerID

varchar(10),

ContactName varchar(20))

Here is the result set:

(4)

CustomerID ContactName

---

---VINET

VINET

Paul

Henriot

LILAS

Carlos

Gonzlez

If the same SELECT statement is executed with

If the same SELECT statement is executed with flagsflags set toset to 22, indicating element-centric mapping, the, indicating element-centric mapping, the values of

values of CustomerIDCustomerID andand ContactNameContactName for both of the customers in the XML document are returned asfor both of the customers in the XML document are returned as NULL, because the <Customers> elements do not have any

NULL, because the <Customers> elements do not have any subelements.subelements. Here is the result set:

Here is the result set:

CustomerID ContactName

---

---NULL

NULL

B. Specify ColPattern for mapping between columns and

B. Specify ColPattern for mapping between columns and the XML attributesthe XML attributes This query returns customer ID, order date, product ID and

This query returns customer ID, order date, product ID and quantity attributes from the XML document. Thequantity attributes from the XML document. The rowpattern

rowpattern identifies the <OrderDetails> elements.identifies the <OrderDetails> elements. ProductIDProductID andand QuantityQuantity are the attributes of theare the attributes of the <OrderDetails> element. However, the

<OrderDetails> element. However, the OrderIDOrderID,, CustomerIDCustomerID andand OrderDateOrderDate are the attributes of theare the attributes of the parent element (<Orders>).

parent element (<Orders>). The optional

The optional ColPatternColPattern is specified, indicating that:is specified, indicating that:

•

• TheThe OrderIDOrderID,, CustomerIDCustomerID andand OrderDateOrderDate in the rowset map to the attributes of the parent of in the rowset map to the attributes of the parent of the nodes identified by

the nodes identified by rowpatternrowpattern in the XML document.in the XML document.

•

• TheThe ProdIDProdID column in the rowset maps to thecolumn in the rowset maps to the ProductIDProductID attribute, and theattribute, and the QtyQty column in thecolumn in the rowset maps to the

rowset maps to the QuantityQuantity attribute of the nodes identified inattribute of the nodes identified in rowpatternrowpattern..

Although the element-centric mapping is specified by the

Although the element-centric mapping is specified by the flagsflags parameter, the mapping specified inparameter, the mapping specified in ColPattern

ColPattern overwrites this mapping.overwrites this mapping.

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="11" Quantity="12"/>

<OrderDetail ProductID="42" Quantity="10"/>

</Order>

</Customer>

<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">

<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"

OrderDate="1996-08-16T00:00:00">

<OrderDetail ProductID="72" Quantity="3"/>

</Order>

</Customer>

</ROOT>'

--Create an internal representation of the XML document.

exec sp_xml_preparedocument @idoc OUTPUT, @doc

(5)

-- SELECT stmt using OPENXML rowset provider

SELECT *

FROM

OPENXML

(@idoc,

'/ROOT/Custome

r/Order/OrderD

etail',2)

WITH

(OrderID

int

'../@OrderID

',

CustomerID

varchar(10)

'../@CustomerI

D',

OrderDate

datetime

'../@OrderDate

',

ProdID

int

'@ProductID',

Qty

int

'@Quantity')

Qty

int

'@Quantity')

This is the result: This is the result: OrderID

OrderID CustomerID CustomerID OrderDate OrderDate ProdID ProdID QtyQty

---10248

10248

VINET

1996-07-04

00:00:00.000

11

12

12 10248

10248

VINET

1996-07-04

00:00:00.000

42

10

10 10283

10283

LILAS

1996-08-16

00:00:00.000

72

3

C. Obtain result in

C. Obtain result in an edge table formatan edge table format In this example, the WITH clause is

In this example, the WITH clause is not specified in the OPENXML statement. As a result, the rowsetnot specified in the OPENXML statement. As a result, the rowset generated by OPENXML has an edge table

generated by OPENXML has an edge table format. The SELECT statement returns all the columns in theformat. The SELECT statement returns all the columns in the edge table.

edge table.

The sample XML document in

The sample XML document in the example consists of the example consists of <Customers>, <Orders>, and<Customers>, <Orders>, and <Order_0020_Details> elements.

<Order_0020_Details> elements.

First sp_xml_preparedocument is called to obtain a document handle. This document handle is p

First sp_xml_preparedocument is called to obtain a document handle. This document handle is p assed toassed to OPENXML.

OPENXML.

In the OPENXML statement In the OPENXML statement

•

• TheThe rowpatternrowpattern (/ROOT/Customers) identifies the <Customers> nodes to process.(/ROOT/Customers) identifies the <Customers> nodes to process.

•

• The WITH clause is not provided. Therefore The WITH clause is not provided. Therefore OPENXML returns the rowset in an edge table format.OPENXML returns the rowset in an edge table format.

Finally the SELECT statement retrieves all the columns in the edge Finally the SELECT statement retrieves all the columns in the edge table.table.

declare @idoc int

declare @doc varchar(1000)

set @doc ='

<ROOT>

<Customers CustomerID="VINET" ContactName="Paul Henriot">

<Orders CustomerID="VINET" EmployeeID="5" OrderDate=

"1996-07-04T00:00:00">

<Order_x0020_Details OrderID="10248" ProductID="11"

Quantity="12"/>

<Order_x0020_Details OrderID="10248" ProductID="42"

Quantity="10"/>

</Orders>

</Customers>

<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">

<Orders CustomerID="LILAS" EmployeeID="3" OrderDate=

(6)

"1996-08-16T00:00:00">

<Order_x0020_Details OrderID="10283" ProductID="72"

Quantity="3"/>

</Orders>

</Customers>

</ROOT>'

--Create an internal representation of the XML document.

exec sp_xml_preparedocument @idoc OUTPUT, @doc

-- SELECT statement using OPENXML rowset provider

SELECT

*

SELECT

*

FROM

OPENXML

(@idoc,

'/ROOT/Custo

mers')

EXEC sp_xml_removedocument @idoc

The result is returned as an edge table.

The result is returned as an edge table. See Also

Using OPENXML

The examples in this topic show how

The examples in this topic show how OPENXML is used in creating a rowset view OPENXML is used in creating a rowset view of an XML document. Forof an XML document. For information about the syntax of OPENXML, see

information about the syntax of OPENXML, see OPENXMLOPENXML[ http://msdn2.microsoft.com/en-[ http://msdn2.microsoft.com/en-us/library/aa276847(SQL.80,printer).aspx ] . The examples show all aspects of OPENXML e

us/library/aa276847(SQL.80,printer).aspx ] . The examples show all aspects of OPENXML e xcept specifyingxcept specifying metaproperties in OPENXML. For more information about specifying metaproperties in

metaproperties in OPENXML. For more information about specifying metaproperties in OPENXML, seeOPENXML, see Specifying Metaproperties in OPENXML

Specifying Metaproperties in OPENXML [ http://msdn2.microsoft.com/en-[ http://msdn2.microsoft.com/en-us/library/aa226531(SQL.80,printer).aspx ] .

us/library/aa226531(SQL.80,printer).aspx ] . Examples

Examples

In retrieving the data,

In retrieving the data, rowpatternrowpattern is used to identify the nodes in the XML document that determine theis used to identify the nodes in the XML document that determine the rows.

rows. rowpatternrowpattern is expressed in the XPath pattern language used in the MSXML XPath impleis expressed in the XPath pattern language used in the MSXML XPath imple mentation. Formentation. For example, if the pattern ends in an

example, if the pattern ends in an element or an attribute, a row is created for element or an attribute, a row is created for each element or attributeeach element or attribute node selected by

node selected by rowpatternrowpattern.. The

The flagsflags value provides default mapping. In thevalue provides default mapping. In the SchemaDeclarationSchemaDeclaration, if no, if no ColPatternColPattern is specified, theis specified, the mapping specified in

mapping specified in flagsflags is assumed. Theis assumed. The flagsflags value is ignored if value is ignored if ColPatternColPattern is specified inis specified in SchemaDeclaration

SchemaDeclaration. The specified. The specified ColPatternColPattern determines the mapping (determines the mapping (attribute-centricattribute-centric oror element- element-centric

centric) and also the behavior in dealing with overflow and unconsumed data.) and also the behavior in dealing with overflow and unconsumed data. A. Execute a simple SELECT statement with OPENXML

A. Execute a simple SELECT statement with OPENXML The XML document in this example consists of the <

The XML document in this example consists of the < Customer>, <Order>, and <OrderDetail> elements.Customer>, <Order>, and <OrderDetail> elements. The OPENXML statement retrieves customer information in a two-column rowset (

The OPENXML statement retrieves customer information in a two-column rowset ( CustomerIDCustomerID andand ContactName

ContactName) from the XML document.) from the XML document. First, the

First, the sp_xml_preparedocumentsp_xml_preparedocument stored procedure is called to obtain a document handle. Thisstored procedure is called to obtain a document handle. This document handle is passed to OPENXML.

document handle is passed to OPENXML. In the OPENXML statement:

In the OPENXML statement:

•

• rowpatternrowpattern (/ROOT/Customer) identifies the <Customer> nodes to process.(/ROOT/Customer) identifies the <Customer> nodes to process.

•

• TheThe flagsflags parameter value is set toparameter value is set to 11 indicatingindicating attribute-centricattribute-centric mapping. As a result, the XMLmapping. As a result, the XML attributes map to the columns in the rowset defined

attributes map to the columns in the rowset defined inin SchemaDeclarationSchemaDeclaration..

•

• InIn SchemaDeclarationSchemaDeclaration (in the WITH clause), the specified(in the WITH clause), the specified ColNameColName values match the correspondingvalues match the corresponding XML attribute names. Therefore, the

XML attribute names. Therefore, the ColPatternColPattern parameter is not specified inparameter is not specified in SchemaDeclarationSchemaDeclaration..

And then, the SELECT statement retrieves all the columns in the rowset provided by OPENXML. And then, the SELECT statement retrieves all the columns in the rowset provided by OPENXML.

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="11" Quantity="12"/>

(8)

<OrderDetail ProductID="42" Quantity="10"/>

</Order>

</Customer>

<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">

<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"

OrderDate="1996-08-16T00:00:00">

<OrderDetail ProductID="72" Quantity="3"/>

</Order>

</Customer>

</ROOT>'

-- Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement using OPENXML rowset provider.

SELECT *

FROM OPENXML (@idoc, '/ROOT/Customer',1)

WITH

(CustomerID

varchar(10),

ContactName varchar(20))

EXEC sp_xml_removedocument @idoc

This is the result:

CustomerID ContactName

---

---VINET

VINET

Paul

Henriot

LILAS

Carlos

Gonzlez

If the same SELECT statement is executed with

If the same SELECT statement is executed with flagsflags set toset to 22, indicating, indicating element-centricelement-centric mapping,mapping, because <Customer> elements do not have any subelements,

because <Customer> elements do not have any subelements, the values of the values of CustomerIDCustomerID andand ContactName

ContactName for both the customers are returned as NULL.for both the customers are returned as NULL. If in the XML document, the <

If in the XML document, the < CustomerID> and <ContactName> are subelements, theCustomerID> and <ContactName> are subelements, the element-centricelement-centric mapping retrieves the values.

mapping retrieves the values.

DECLARE @idoc int

DECLARE @doc varchar(1000)

SET @doc ='

<ROOT>

<Customer>

<CustomerID>VINET</CustomerID>

<ContactName>Paul Henriot</ContactName>

<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"

OrderDate="1996-07-04T00:00:00">

<OrderDetail ProductID="11" Quantity="12"/>

<OrderDetail ProductID="42" Quantity="10"/>

</Order>

</Customer>

<Customer>

<CustomerID>LILAS</CustomerID>

<ContactName>Carlos Gonzlez</ContactName>

<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"

OrderDate="1996-08-16T00:00:00">

<OrderDetail ProductID="72" Quantity="3"/>

</Order>

</Customer>

</ROOT>'

(9)

-- Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement using OPENXML rowset provider.

SELECT

*

SELECT

*

FROM

OPENXML

(@idoc,

'/ROOT/Custom

er',2)

WITH

(CustomerID

varchar(10),

ContactName varchar(20))

EXEC sp_xml_removedocument @idoc

This is the result:

CustomerID ContactName

---

---VINET

VINET

Paul

Henriot

LILAS

Carlos

Gonzlez

B. Specify

B. Specify ColPatternColPattern for mapping between rowset columns and the for mapping between rowset columns and the XML attributes/elementsXML attributes/elements This example shows how the XPath pattern is specified in the optional

This example shows how the XPath pattern is specified in the optional ColPatternColPattern parameter to provideparameter to provide mapping between rowset columns and the XML attributes (and elements).

mapping between rowset columns and the XML attributes (and elements). The XML document in this example consists of the <

The XML document in this example consists of the < Customer>, <Order>, and <OrderDetail> elements.Customer>, <Order>, and <OrderDetail> elements. The OPENXML statement retrieves customer and order information as a rowset (

The OPENXML statement retrieves customer and order information as a rowset ( CustomerIDCustomerID,, OrderDateOrderDate,, ProdID

ProdID, and, and QtyQty) from the XML document.) from the XML document. First, the

•

• rowpatternrowpattern (/ROOT/Customer/Order/OrderDetail) identifies the <OrderDetail> nodes to process.(/ROOT/Customer/Order/OrderDetail) identifies the <OrderDetail> nodes to process.

•

• For illustration purposes, theFor illustration purposes, the flagsflags parameter value is set toparameter value is set to 22 indicatingindicating element-centricelement-centric mapping.mapping. However, the mapping specified in

However, the mapping specified in ColPatternColPattern overwrites this mapping (the XPath pattern specified inoverwrites this mapping (the XPath pattern specified in ColPattern

ColPattern maps the columns in the rowset to attributes thus resulting in anmaps the columns in the rowset to attributes thus resulting in an attribute-centricattribute-centric mapping).

mapping).

In

In SchemaDeclarationSchemaDeclaration (in the WITH clause),(in the WITH clause), ColPatternColPattern is also specified with theis also specified with the ColNameColName andand ColTypeColType parameters. The optional

parameters. The optional ColPatternColPattern is the XPath pattern specified to indicate:is the XPath pattern specified to indicate:

•

• TheThe OrderIDOrderID,, CustomerIDCustomerID, and, and OrderDateOrderDate columns in the rowset map to the attributes of thecolumns in the rowset map to the attributes of the parent of the nodes identified by

parent of the nodes identified by rowpatternrowpattern.. rowpatternrowpattern identifies the <OrderDetail> nodes.identifies the <OrderDetail> nodes. Therefore, the

Therefore, the CustomerIDCustomerID andand OrderDateOrderDate columns map tocolumns map to CustomerIDCustomerID andand OrderDateOrderDate attributesattributes of the <Order> element.

of the <Order> element.

•

• TheThe ProdIDProdID andand QtyQty columns in the rowset map to thecolumns in the rowset map to the ProductIDProductID andand QuantityQuantity attributes of theattributes of the nodes identified in

nodes identified in rowpatternrowpattern..

And then the SELECT statement retrieves all the columns in the rowset provided by OPENXML. And then the SELECT statement retrieves all the columns in the rowset provided by OPENXML.

DECLARE @idoc int

DECLARE @doc varchar(1000)

(10)

SET @doc ='

<ROOT>

<Customer CustomerID="VINET" ContactName="Paul Henriot">

<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"

OrderDate="1996-07-04T00:00:00">

<OrderDetail ProductID="11" Quantity="12"/>

<OrderDetail ProductID="42" Quantity="10"/>

</Order>

</Customer>

<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">

<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"

OrderDate="1996-08-16T00:00:00">

<OrderDetail ProductID="72" Quantity="3"/>

</Order>

</Customer>

</ROOT>'

-- Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT stmt using OPENXML rowset provider.

SELECT *

FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)

WITH

(OrderID

int

'../@OrderID'

,

CustomerID

varchar(10)

'../@Customer

ID',

OrderDate

datetime

'../@OrderDat

e',

ProdID

int

'@ProductID',

Qty

int

'@Quantity')

Qty

int

'@Quantity')

This is the result: This is the result:

OrderID

CustomerID

OrderDate

ProdID

Qty

---10248

10248

VINET

1996-07-04

00:00:00.000

11

12

12 10248

10248

VINET

1996-07-04

00:00:00.000

42

10

10 10283

10283

LILAS

1996-08-16

00:00:00.000

72

3

The XPath pattern specified as

The XPath pattern specified as ColPatternColPattern can also be specified to map the XML elements to the rowsetcan also be specified to map the XML elements to the rowset columns (resulting in

columns (resulting in element-centricelement-centric mapping). In the following example, the mapping). In the following example, the XML documentXML document <CustomerID> and <OrderDate> are subelements of <Orders> element. Because

<CustomerID> and <OrderDate> are subelements of <Orders> element. Because ColPatternColPattern overwrites theoverwrites the mapping specified in

mapping specified in flagsflags parameter, theparameter, the flagsflags parameter is not specified in OPENXML.parameter is not specified in OPENXML.

DECLARE @idoc int

DECLARE @doc varchar(1000)

SET @doc ='

<ROOT>

<Customer CustomerID="VINET" ContactName="Paul Henriot">

<Order EmployeeID="5" >

<OrderID>10248</OrderID>

<CustomerID>VINET</CustomerID>

<OrderDate>1996-07-04T00:00:00</OrderDate>

<OrderDetail ProductID="11" Quantity="12"/>

<OrderDetail ProductID="42" Quantity="10"/>

</Order>

</Customer>

<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">

<Order

(11)

<OrderID>10283</OrderID>

<CustomerID>LILAS</CustomerID>

<OrderDate>1996-08-16T00:00:00</OrderDate>

<OrderDetail ProductID="72" Quantity="3"/>

</Order>

</Customer>

</ROOT>'

-- Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT stmt using OPENXML rowset provider.

SELECT *

FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail')

WITH

(CustomerID varchar(10)

'../CustomerI

D',

OrderDate

datetime

'../OrderDate

',

ProdID

int

'@ProductID',

Qty

int

'@Quantity')

EXEC sp_xml_removedocument @idoc

C. Combining attribute-centric and element-centric mapping C. Combining attribute-centric and element-centric mapping In this example, the

In this example, the flagsflags parameter is set toparameter is set to 33, indicating that both, indicating that both attribute-centricattribute-centric andand element- element-centric

centric mapping is to be applied. In this case, themapping is to be applied. In this case, the attribute-centricattribute-centric mapping is applied first, and thenmapping is applied first, and then element-centric

element-centric mapping is applied for all the columns not yet dealt with.mapping is applied for all the columns not yet dealt with.

DECLARE @idoc int

DECLARE @doc varchar(1000)

SET @doc ='

<ROOT>

<Customer

CustomerID="V

INET"

>

<ContactName>Paul Henriot</ContactName>

<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"

OrderDate="1996-07-04T00:00:00">

<OrderDetail ProductID="11" Quantity="12"/>

<OrderDetail ProductID="42" Quantity="10"/>

</Order>

</Customer>

<Customer CustomerID="LILAS" >

<ContactName>Carlos Gonzlez</ContactName>

<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"

OrderDate="1996-08-16T00:00:00">

<OrderDetail ProductID="72" Quantity="3"/>

</Order>

</Customer>

</ROOT>'

-- Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement using OPENXML rowset provider.

SELECT *

FROM OPENXML (@idoc, '/ROOT/Customer',3)

WITH

(CustomerID

varchar(10),

ContactName varchar(20))

(12)

EXEC sp_xml_removedocument @idoc

This is the result

CustomerID ContactName

---

---VINET

VINET

Paul

Henriot

LILAS

Carlos

Gonzlez

The

The attribute-centricattribute-centric mapping is applied formapping is applied for CustomerIDCustomerID. There is no. There is no ContactNameContactName attribute in theattribute in the <Customers> element; therefore,

<Customers> element; therefore, element-centrelement-centric mapping is applied.ic mapping is applied. D. Specify text() XPath function as

D. Specify text() XPath function as ColPatternColPattern The XML document in this example consists of the <

The XML document in this example consists of the < Customer> and <Order> elements. The OPENXMLCustomer> and <Order> elements. The OPENXML statement retrieves a rowset consisting of the

statement retrieves a rowset consisting of the oidoid attribute from the <Order> element, the ID attribute from the <Order> element, the ID of the parentof the parent of the node (identified by

of the node (identified by rowpatternrowpattern), and the leaf-value string of the element content.), and the leaf-value string of the element content. First, the

•

• rowpatternrowpattern (/root/Customer/Order) identifies the <Order> nodes to process.(/root/Customer/Order) identifies the <Order> nodes to process.

•

• TheThe flagsflags parameter value is set toparameter value is set to 11, indicating, indicating attribute-centricattribute-centric mapping. As a result, the XMLmapping. As a result, the XML attributes map to the rowset columns defined in

attributes map to the rowset columns defined in SchemaDeclarationSchemaDeclaration..

•

• InIn SchemaDeclarationSchemaDeclaration (in the WITH clause), the rowset column names,(in the WITH clause), the rowset column names, oidoid andand amountamount, match the, match the corresponding XML attribute names. Therefore, the

corresponding XML attribute names. Therefore, the ColPatternColPattern parameter is not specified. For theparameter is not specified. For the comment

comment column in the rowset, the XPath function (column in the rowset, the XPath function ( text()text()) is specified as) is specified as ColPatternColPattern. This overwrites. This overwrites the

the attribute-centricattribute-centric mapping specified inmapping specified in flagsflags, and the column contains the leaf-value string of the, and the column contains the leaf-value string of the element content.

element content.

And then, the SELECT statement retrieves all the columns in the rowset provided by OPENXML. And then, the SELECT statement retrieves all the columns in the rowset provided by OPENXML.

DECLARE @idoc int

DECLARE @doc varchar(1000)

--sample XML document

SET @doc ='

<root>

<Customer cid= "C1" name="Janine" city="Issaquah">

<Order oid="O1" date="1/20/1996" amount="3.5" />

<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very

satisfied

</Order>

</Customer>

<Customer cid="C2" name="Ursula" city="Oelde" >

<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue

white red">

<Urgency>Important</Urgency>

Happy Customer.

</Order>

(13)

<Order oid="O4" date="1/20/1996" amount="10000"/>

</Customer>

</root>

'

-- Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement using OPENXML rowset provider.

SELECT *

FROM OPENXML (@idoc, '/root/Customer/Order', 1)

WITH

(oid

char(5),

amount float,

comment ntext 'text()')

EXEC sp_xml_removedocument @idoc

This is the result:

oid

amount

comment

---

---O1

O1

3.5

3.5 NULL

NULL

O2

13.4

13.4 Customer

Customer

was

very

satisfied

O3

100.0

100.0 Happy

Happy

Customer.

O4

10000.0

NULL

E. Specify

E. Specify TableNameTableName in the WITH clausein the WITH clause This example specifies

This example specifies TableNameTableName in the WITH clause instead of in the WITH clause instead of SchemaDeclarationSchemaDeclaration in the WITH clause.in the WITH clause. This is useful if you have a

This is useful if you have a table with the structure you want and no column patterns (table with the structure you want and no column patterns ( ColPatternColPattern parameter)parameter) are required.

are required.

The XML document in this example consists of the <

The XML document in this example consists of the < Customer> and <Order> elements. The OPENXMLCustomer> and <Order> elements. The OPENXML statement retrieves order information in a three-column rowset (

statement retrieves order information in a three-column rowset ( oidoid,, datedate, and, and amountamount) from the XML) from the XML document.

document. First, the

•

• rowpatternrowpattern (/root/Customer/Order) identifies the <Order> nodes to process.(/root/Customer/Order) identifies the <Order> nodes to process.

•

• There is noThere is no SchemaDeclarationSchemaDeclaration in the WITH clause. Instead, a table name is in the WITH clause. Instead, a table name is specified. Therefore,specified. Therefore, the table schema is used as the rowset schema.

the table schema is used as the rowset schema.

•

• TheThe flagsflags parameter value is set toparameter value is set to 11, indicating, indicating attribute-centricattribute-centric mapping. Therefore, attributesmapping. Therefore, attributes of the elements (identified by

of the elements (identified by rowpatternrowpattern) map to the rowset columns with ) map to the rowset columns with the same name.the same name.

And then the SELECT statement retrieves all the columns in the rowset provided by OPENXML. And then the SELECT statement retrieves all the columns in the rowset provided by OPENXML.

-- Create a test table. This table schema is used by OPENXML as the

-- rowset schema.

CREATE TABLE T1(oid char(5), date datetime, amount float)

DECLARE @idoc int

DECLARE @doc varchar(1000)

(14)

-- Sample XML document

SET @doc ='

<root>

<Customer cid= "C1" name="Janine" city="Issaquah">

<Order oid="O1" date="1/20/1996" amount="3.5" />

<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very

satisfied</Order>

</Customer>

<Customer cid="C2" name="Ursula" city="Oelde" >

<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue

white red">

<Urgency>Important</Urgency>

</Order>

<Order oid="O4" date="1/20/1996" amount="10000"/>

</Customer>

</root>

'

--Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement using OPENXML rowset provider.

SELECT *

FROM OPENXML (@idoc, '/root/Customer/Order', 1)

WITH T1

EXEC sp_xml_removedocument @idoc

This is the result:

oid

date

amount

--- ---

---O1

O1

1996-01-20

00:00:00.000

3.5

3.5 O2

O2

1997-04-30

00:00:00.000

13.4

13.4 O3

O3

1999-07-14

00:00:00.000

100.0

100.0 O4

O4

1996-01-20

00:00:00.000

10000.0

F. Obtain the result in an

F. Obtain the result in an edge table formatedge table format In this example, the WITH clause is

In this example, the WITH clause is not specified in the OPENXML statement. As a result, the rowsetnot specified in the OPENXML statement. As a result, the rowset generated by OPENXML has an edge table

generated by OPENXML has an edge table format. The SELECT statement returns all the columns in theformat. The SELECT statement returns all the columns in the edge table.

edge table.

The sample XML document in the example

The sample XML document in the example consists of the <Customer>, <Order>, and <OrderDetail>consists of the <Customer>, <Order>, and <OrderDetail> elements.

elements. First, the

•

• rowpatternrowpattern (/ROOT/Customer) identifies the <Customer> nodes to process.(/ROOT/Customer) identifies the <Customer> nodes to process.

•

• The WITH clause is not provided; therefore, The WITH clause is not provided; therefore, OPENXML returns the rowset in an edge table format.OPENXML returns the rowset in an edge table format.

And then the SELECT statement retrieves all the columns in the edge table. And then the SELECT statement retrieves all the columns in the edge table.

(15)

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="11" Quantity="12"/>

<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>

</Order>

</Customer>

<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">

<Order CustomerID="LILAS" EmployeeID="3" OrderDate=

"1996-08-16T00:00:00">

<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>

</Order>

</Customer>

</ROOT>'

--Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement using OPENXML rowset provider.

SELECT *

FROM OPENXML (@idoc, '/ROOT/Customer')

EXEC sp_xml_removedocument @idoc

The result is returned as an edge table.

The result is returned as an edge table. You can write queries against the edge table You can write queries against the edge table to obtain information:to obtain information:

•

• The following query returns the number of The following query returns the number of CustomerCustomer nodes in the document. Because the WITHnodes in the document. Because the WITH clause is not specified, OPENXML returns an edge table. The SELECT statement queries the edge table. clause is not specified, OPENXML returns an edge table. The SELECT statement queries the edge table.

•

SELECT count(*)

•

FROM OPENXML(@idoc, '/')

•

WHERE localname = 'Customer'

•

• This query returns local names of XML nodes of element type.This query returns local names of XML nodes of element type. •

•

SELECT distinct localname

•

FROM OPENXML(@idoc, '/')

•

WHERE nodetype = 1

•

ORDER BY localname

G. Specify

G. Specify rowpatternrowpattern ending with an attributeending with an attribute The XML document in this example consists of the <

The XML document in this example consists of the < Customer>, <Order>, and <OrderDetail> elements.Customer>, <Order>, and <OrderDetail> elements. The OPENXML statement retrieves order details information in a three-column rowset (

The OPENXML statement retrieves order details information in a three-column rowset ( ProductIDProductID,, Quantity

Quantity, and, and OrderIDOrderID) from the XML document.) from the XML document. First, the

First, the sp_xml_preparedocumentsp_xml_preparedocument is called to obtain a document handle. This document handle isis called to obtain a document handle. This document handle is passed to OPENXML.

passed to OPENXML.

In the OPENXML statement: In the OPENXML statement: