OPENXML to Publish and Process XML Data

Syntax

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

Arguments

idoc

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

rowpattern

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

flags

Indicates the mapping that should be used between the XML data and the relational rowset, and how the spill-over column should be filled. flags is an optional input parameter, and can be one of these values.

Byte Value	Description
0	Defaults to attribute-centric mapping.
1	Use the attribute-centric mapping. Can be combined with XML_ELEMENTS; in which case, attribute-centric mapping is applied first, and then element-centric mapping is applied for all columns not yet dealt with.
2	Use the element-centric mapping. Can be combined with XML_ATTRIBUTES; in which case, attribute-centric mapping is applied first, and then element-centric mapping is applied for all columns not yet dealt with.
8	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 copied to the overflow property @mp:xmltext.

SchemaDeclaration

Is the schema definition of the form:
ColName ColType [ColPattern | MetaProperty][, ColName ColType [ColPattern | MetaProperty]...]

ColName

Is the column name in the rowset.

ColType

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

ColPattern

Is an optional, general XPath pattern that describes how the XML nodes should be mapped to the columns. If the ColPattern is not specified, the default mapping (attribute-centric or element-centric mapping as specified by flags) takes place.

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

The general XPath pattern specified as ColPattern also supports the metaproperties.

MetaProperty

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

TableName

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

The WITH clause provides a rowset format (and additional mapping information as necessary) using either SchemaDeclaration or specifying an existing TableName. If the optional WITH clause is not specified, the results are returned in an edge table format. Edge tables represent the fine-grained XML document structure (e.g. element/attribute names, the document hierarchy, the namespaces, PIs etc.) in a single table.

This table describes the structure of the edge table.

Column name	Data type	Description
id	bigint	Is the unique ID of the document node. The root element has an ID value 0. The negative ID values are reserved.
parentid	bigint	Identifies the parent of the node. The parent identified by this ID is not necessarily the parent element, but it depends on the NodeType of the node whose parent is identified by this ID. For example, if the node is a text node, the parent of it may be an attribute node. If the node is at the top level in the XML document, its ParentID is NULL.
nodetype	int	Identifies the node type. Is an integer that corresponds to the XML DOM node type numbering (see DOM for node information). The node types are: 1 = Element node 2 = Attribute node 3 = Text node
localname	nvarchar	Gives the local name of the element or attribute. Is NULL if the DOM object does not have a name.
prefix	nvarchar	Is the namespace prefix of the node name.
namespaceuri	nvarchar	Is the namespace URI of the node. If the value is NULL, no namespace is present.
datatype	nvarchar	Is the actual data type of the element or attribute row and is NULL otherwise. The data type is inferred from the inline DTD or from the inline schema.
prev	bigint	Is the XML ID of the previous sibling element. Is NULL if there is no direct previous sibling.
text	ntext	Contains the attribute value or the element content in text form (or is NULL if the edge table entry does not need a value).

declare @i int exec sp_xml_preparedocument @i output, '<mydata> <test xmlID="3" xmlData="blah blah blah"/> <test xmlID="1" xmlData="blah"/> <SportType> <type>College Football</type> <Schedule> <team_name1>Fresno St.</team_name1> <team_name2>Louisiana Tech</team_name2> <game_date>2005-12-02</game_date> <game_start>12-02-2005 9:00 pm</game_start> </Schedule> </SportType> <SportType> <Schedule> <team_name1>Fresno St.</team_name1> <team_name2>Louisiana Tech</team_name2> <game_date>2005-12-02</game_date> <game_start>12-02-2005 9:00 pm</game_start> </Schedule> </SportType> </mydata>' select xmlID, xmlData from OpenXml(@i, 'mydata/test') with (xmlID int, xmlData nvarchar(30)) select team_name1, game_date from OpenXml(@i, 'mydata/SportType[type=*]/Schedule',2) with (team_name1 nvarchar(30),game_date datetime ) exec sp_xml_removedocument @i

 

DECLARE @idoc int; DECLARE @doc varchar(4000); SET @doc ='<?xml version="1.0" encoding="GB2312" ?> <FLIGHT> <DATETIME> 2007-11-22 09:27:04 </DATETIME> <METHOD> MOD </METHOD> <TYPE> A </TYPE> <FLIGHTINFOR> <FLIGHTATTR> 21 </FLIGHTATTR> <FLIGHTTYPE> 0 </FLIGHTTYPE> <AIRCRAFTTYPE> E145 </AIRCRAFTTYPE> <REGISTRATION> 3062 </REGISTRATION> <FLIGHTNUMBER> CZ3321 </FLIGHTNUMBER> <PRIMARYFLT /> <RELATEDFLT /> <FLIGHTSTATUS> COM </FLIGHTSTATUS> <PORTNO /> <FLIGHTDATETIME> <PLANDATE> 2007-11-22 </PLANDATE> <ADT /> <EDT> 2007-11-22 10:33:00 </EDT> <SDT> 2007-11-22 09:30:00 </SDT> </FLIGHTDATETIME> <STATION> <ORIGIN> CAN </ORIGIN> <DESTINATION> YIH </DESTINATION> <VIA /> </STATION> </FLIGHTINFOR> <DEP> <CHECKIN> <COUNTEROPENDT /> <COUNTER /> </CHECKIN> <DEPTERMINAL /> <GATE /> </DEP> <ARR> <BELT> 1 </BELT> </ARR> </FLIGHT> ' EXEC sp_xml_preparedocument @idoc OUTPUT, @doc -- ===================================================== -- With (ColName ColType [ColPattern | MetaProperty]) -- Meta Property --@mp:id Provides system-generated, document-wide identifier of the DOM node. An XML ID of 0 indicates that the element is a root element and its @mp:parentid is NULL. --@mp:parentid Same as @mp:id, only for the parent. --@mp:localname Provides the non-fully qualified name of the node. It is used with prefix and namespace URI to name element or attribute nodes. --@mp:parentlocalname Same as @mp:localname, only for the parent. --@mp:namespaceuri Provides the namespace URI of the current element. If the value of this attribute is NULL, no namespace is present. --@mp:parentnamespaceuri Same as @mp:namespaceuri, only for the parent. --@mp:prefix The namespace prefix of the current element name. --@mp:prev Stores the @mp:id of the previous sibling relative to a node. Using this, we can tell something about the ordering of the elements at the current level of the hierarchy. --@mp:xmltext This Meta property is used for processing purposes, and contains the actual XML text for the current element as used in the overflow handling of OPENXML. -- -- ===================================================== --SELECT * FROM OPENXML (@idoc, '/FLIGHT',2) -- WITH (DATETIME DATETIME ,METHOD varchar(20),TYPE char(1)) SELECT * FROM OPENXML (@idoc, '/FLIGHT/FLIGHTINFOR',2) WITH (FLIGHTATTR varchar(20) 'FLIGHTATTR' ,FLIGHTTYPE varchar(20),REGISTRATION varchar(20)) EXEC sp_xml_removedocument @idoc

 

-- Create tables for later population using OPENXML. CREATE TABLE Customers (CustomerID varchar(20) primary key, ContactName varchar(20), CompanyName varchar(20)) GO CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime) GO DECLARE @docHandle int DECLARE @xmlDocument nvarchar(max) -- or xml type SET @xmlDocument = N'<ROOT> <Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1"> <Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/> <Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/> </Customers> <Customers CustomerID="XYZBB" ContactName="Steve" CompanyName="Company2">No Orders yet! </Customers> </ROOT>' EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument -- Use OPENXML to provide rowset consisting of customer data. INSERT Customers SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers') WITH Customers -- Use OPENXML to provide rowset consisting of order data. INSERT Orders SELECT * FROM OPENXML(@docHandle, N'//Orders') WITH Orders -- Using OPENXML in a SELECT statement. SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers/Orders') WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime) -- Remove the internal representation of the XML document. EXEC sp_xml_removedocument @docHandle

 

The following illustration shows the parsed XML tree of the previous XML document that was created by using sp_xml_preparedocument.

 

Using OPENXML

DECLARE @h int EXEC sp_xml_preparedocument @h OUTPUT, N' <root xmlns:a="urn:1"> <a:Elem abar="asdf"> T<a>a</a>U </a:Elem> </root>', '<ns xmlns:b="urn:1" />' SELECT * FROM openxml(@h, '/root/b:Elem') WITH (Col1 varchar(20) 'text()') EXEC sp_xml_removedocument @h Declare @DetailsXML XML SET @DetailsXML = ' <OrderDetails> <OrderDetail LineNumber = "1" ProductID = "99" IsNew = "0" IsDirty = "-1" IsDeleted = "0"/> <OrderDetail LineNumber = "2" ProductID = "1" IsNew = "0" IsDirty = "0" IsDeleted = "-1"/> <OrderDetail LineNumber = "5" ProductID = "7" IsNew = "-1" IsDirty = "0" IsDeleted = "0"/> </OrderDetails>' Declare @idoc int Exec sp_xml_preparedocument @idoc OUTPUT, @DetailsXML SELECT LineNumber, ProductID FROM OpenXML(@idoc, 'OrderDetails/OrderDetail[@IsNew="-1"]', 1) WITH (LineNumber INT, ProductID INT, IsNew INT, IsDirty INT, IsDeleted INT) EXEC sp_xml_removedocument @idoc

http://msdn.microsoft.com/en-us/library/aa226522(v=sql.80).aspx