Microsoft started supporting OPENXML in SQL Server
2000. OPENXML is a SQL Server function, which accepts a stream of XML
data and provides an in-memory relational rowset view of the XML data.
You specify the rowset using the WITH clause. OPENXML is an extension of the SELECT statement. OPENXML is a memory-intensive process.
The basic syntax for OPENXML is shown in Figure 1. Table 1 lists the argument available to the OPENXML function.
Figure 1. OPENXML Syntax
OPENXML (idoc int [in], rowpatten nvarchar[in],flags byte[in]) WITH
SchemaDeclaration|TableName]
|
Table 1. OPENXML Arguments
| OPENXML Arguments | Description |
|---|
| Idoc | Document handle of the internal representation of an XML document |
| Rowpattern | XPath query used to identify the nodes to be processed as rows |
| Flags | Mapping between the XML data and the relational rowset (Optional Parameter)0 – Defaults to attribute centric mapping 1 – Attribute-centric mapping. (Combined with XML_ELEMENTS) 2 – Element-centric mapping. (Combined with XML_ATTRIBUTES) 8 – Combined with XML_ATTRIBUTES or XML_ELEMENTS
|
| SchemaDeclaration | The schema definition of the form |
| TableName | The table name that can be given, instead of Schema Declaration, if a table exists |
The WITH clause provides a table format using SchemaDeclaration or specifying an existing TableName. The WITH
clause is optional, and if not specified the results are returned in an
edge table format. Edge tables represent the fine-grained XML document
structure in a single table.
The system stored procedure,
sp_xml_preparedocument, reads an XML text, parses it, and provides a
document ready to be used. This document is a tree representation of
elements, attributes, text, comments, and so on, in an XML document.
This procedure needs to be run before your OPENXML query. The syntax for
sp_xml_preparedocument is shown in Figure 2. Arguments for sp_xml_prepare-document are listed in Table 2.
Figure 2. sp_xml_preparedocument Syntax
sp_xml_preparedocument hdoc OUTPUT
[, xml]
[, xpath_namespaces]
|
Table 2. sp_xml_preparedocument Arguments
| Argument | Description |
|---|
| hdoc | Is the handle to the newly created document; hdoc is an integer. |
| xml | Is
the original XML document. The MSXML parser parses this XML
document.xmltext in a text (char, nchar, varchar, nvarchar, text, or
ntext) parameter. The default value is NULL, in which case an internal
representation of an empty XML document is created. |
| xpath_namespaces | Specifies
the namespace declarations that are used in row and column XPath
expressions in OPENXML. The default value is <root
xmlns:mp=”urn:schemas- microsoft-com:xml-metaprop”>. |
| | xpath_namespaces
provides the namespace URIs for the prefixes used in the XPath
expressions in OPENXML by means of a well-formed XML document.
xpath_namespaces declares the prefix that must be used to refer to the
namespace urn:schemas-microsoft- com: xml-metaprop, which provides meta
data about the parsed XML elements. Although you can redefine the
namespace prefix for the metaproperty namespace using this technique,
this namespace is not lost. The prefix mp is still valid for
urn:schemas-microsoft- com:xml-metaprop even if xpath_namespaces
contains no such declaration. xpath_namespaces is a text (char, nchar,
varchar, nvarchar, text, or ntext) parameter. |
The
sp_xml_removedocument removes the XML document that is specified by the
stored procedure sp_xml_preparedocument. The syntax for
sp_xml_remove-document is shown in Figure 3. Arguments for sp_xml_removedocument are listed in Table 3.
Figure 3. sp_xml_removedocument Syntax
sp_xml_removedocument hdoc
|
Table 3. sp_xml_removedocument Arguments
| Argument | Description |
|---|
| Hdoc | Is the handle to the newly created document. An invalid handle returns an error. hdoc is an integer. |
