The OPENXML in SQL uses

DECLARE @idoc intDECLARE @doc varchar( +)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 by One" quantity= "/&G"      T <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>< /c0>'--Create An internal representation of the XML document.EXECsp_xml_preparedocument@idocOUTPUT,@doc--SELECT stmt using OPENXML rowset providerSELECT * fromOPENXML (@idoc,'/root/customer/order/orderdetail',2)          with(OrderIDint         '.. /@OrderID', CustomerIDvarchar(Ten)'.. /@CustomerID', OrderDatedatetime    '.. /@OrderDate', ProdIDint         '@ProductID', Qtyint         '@Quantity')

DECLARE @idoc intDECLARE @doc varchar( +)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= "12"/ > <orderdetail orderid= "10248" productid= "quantity=/>" </order></customer><customer Customerid= "Lilas" contactname= "Carlos Gonzlez" > <order customerid= "Lilas" employeeid= "3" orderdate= " 1996-08-16t00:00:00 "> <orderdetail orderid=" 10283 "productid=" quantity= "3"/> </order></custom Er></root>'--Create An internal representation of the XML document.EXECsp_xml_preparedocument@idocOUTPUT,@doc--Execute A SELECT statement that uses the OPENXML rowset provider.SELECT    * fromOPENXML (@idoc,'/root/customer',1)             with(CustomerIDvarchar(Ten), ContactNamevarchar( -))

DECLARE @idoc intDECLARE @doc varchar( +)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= "/> <order_x0020_details orderid=" 10248 "productid=" "quantity="/> </orders></cu Stomers><customers customerid= "Lilas" contactname= "Carlos Gonzlez" > <orders customerid= "LILAS" Employeeid= "3" orderdate= "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.EXECsp_xml_preparedocument@idocOUTPUT,@doc--SELECT statement that uses the OPENXML rowset provider.SELECT    * fromOPENXML (@idoc,'/root/customers')EXECSp_xml_removedocument@idoc

Declare @idoc int Declare @GoodsXml nvarchar(Max);Set @GoodsXml='<orderitemgoods><goods><goodsattributeid>21</goodsattributeid><goodscount>2 </GoodsCount><GoodsPrice>1.1</GoodsPrice><GoodsId>1</GoodsId></Goods>< Goods><goodsid>2</goodsid><goodsattributeid>22</goodsattributeid><goodscount> 2</goodscount><goodsprice>1.1</goodsprice></goods><goods><goodsid>3</ goodsid><goodsattributeid>23</goodsattributeid><goodscount>2</goodscount>< Goodsprice>1.1</goodsprice></goods></orderitemgoods>'execsp_xml_preparedocument@idocOutput@GoodsXml ;SelectT.goodsid, T.goodsattributeid, T.goodscount, T.goodsprice fromOpenXML (@idoc,'/orderitemgoods/goods',2)  with(Goodsidbigint 'Goodsid', Goodsattributeidbigint, Goodscountint, Goodspricefloat) asT--where T.goodsid=1--system sp_xml_removedocument Delete XML data from SQL Server memoryexecSp_xml_removedocument@idoc;

OPENXML usage

OPENXML (idoc int [in], rowpattern nvarchar [in], [flags byte [in]]) [With (SchemaDeclaration | TableName)]

Description

Provides a rowset format by using SchemaDeclaration or specifying an existing TABLENAME,WITH clause (additional mapping information can also 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).

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.

byte value	Description
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: colnamecoltype [ColPattern | MetaProperty] [, Colnamecoltype [ColPattern | MetaProperty] [...]

colname

coltype

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

ColPattern

Optional generic XPath pattern that shows how the XML node 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

The meta-attributes provided by  OPENXML  One. 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).

The OPENXML in SQL uses