OpenXML function has the following format:
OpenXML(idoc, rowpattern [,flags])
WITH (SchemaDeclaration | TableName)]
Where the arguments are:
- idoc – integer value returned by the sp_xml_preparedocument stored procedure and pointing to the memory-resident XML document
- rowpattern – in essence, this parameter determines which elements and attributes of an XML document should be converted to rowset format. This is done by specifying XPath formatted string identifying the target nodes to be processed as rows. In order to understand it better, the following section provides explanation of the basic principles of XPath.
XPath is an abbreviation of the term XML Path Language, which designates a language that is used for the processing of XML documents. XPath looks at XML elements as nodes of a tree (and in this aspect, it reflects the way we look at files and directories in the file system of a drive). Using its own set of symbols, XPath allows you to traverse the tree structure, consisting of XML elements, from the root to any element or attribute, as well as between any two elements or attributes. The root of this tree is referred to as the “/”. To access an element, you need to specify its path, which consists of all elements traversed on the way to it. When refering to an element, you simply use its name; references to an attribute are distinguished by adding the “@” symbol in front of it. Paths can be absolute (starting from the root – which means that the path has “/” as its first character) or relative (from one non-root element to another). To make it easier to understand, let’s look at some examples. First, consider the following sample XML document containing listing of Inventory items:
<Inventory>
<Product ID="00001">
<Name>"Very Small Widget"</Name>
<Type>"Hardware"</Type>
<QuantityInStock>20</QuantityInStock>
<Price Category="Wholesale">0.49</Price>
<Price Category="Retail">0.99</Price>
</Product>
<Product ID="00002">
<Name>"Not So Small Widget"</Name>
<Type>"Hardware"</Type>
<QuantityInStock>200</QuantityInStock>
<Price Category="Wholesale">0.99</Price>
<Price Category="Retail">1.49</Price>
</Product>
</Inventory>
In this case, the Product elements can be reached by:
/Inventory/Product
Note that this XPath expression refers to all Product elements. If you wanted to retrieve all Product elements and their sub-elements, you would use the following notation:
/Inventory
Similarly,
would refer to all elements in the entire XML tree. The notation
/Inventory/Product/@ID
would reference all of the ID attributes. If the @ID attribute were also used in elements other than the Product, you could reference all of these attributes by using the notation:
You can also traverse the tree in the opposite direction (towards the root) by using the “..” symbol, which designates the parent of the current element.
All of the XPath examples given so far return all of the elements or attributes, which occupy specific locations within the tree. If you want to be more specific and select them based on their value, you can use predicates. For example, to reference all products of the Hardware type, you would use the following :
//Product [Type="Hardware"]
To find a Product with the attribute ID equal to “00001”, you would specify:
//Product [@ID="00001"]
The remaining parameters of OpenXML function are:
- flags – integer value, which determines whether the returned rowsets should contain attributes (value of 0 or 1), elements (value of 2), or both attributes and elements (value of 3)
- SchemaDeclaration – specifies the schema (along with its properties) used to define the structure of the rowset that will be returned. You can think of XML schema as an equivalent of a SQL table definition.
- TableName – instead of specifying the schema to define the structure of the rowset, you can alternatively provide name of target table
