SQL Server 2008 : OPENXML, sp_xml_preparedocument, and sp_xml_removedocument

2/5/2011 5:15:00 PM
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

Table 1. OPENXML Arguments
OPENXML ArgumentsDescription
IdocDocument handle of the internal representation of an XML document
RowpatternXPath query used to identify the nodes to be processed as rows
FlagsMapping 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

SchemaDeclarationThe schema definition of the form
TableNameThe 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
hdocIs the handle to the newly created document; hdoc is an integer.
xmlIs 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_namespacesSpecifies 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
HdocIs the handle to the newly created document. An invalid handle returns an error. hdoc is an integer.
  •  SQL Server 2008 : Retrieving and Transforming XML Data
  •  SQL Azure: Building a Shard (part 4) - Updating and Deleting Records in the Shard & Adding Records to the Shard
  •  SQL Azure: Building a Shard (part 3) - Reading Using the Shard & Caching
  •  SQL Azure: Building a Shard (part 2) - Managing Database Connections
  •  SQL Azure: Building a Shard (part 1) - Designing the Shard Library Object
  •  SQL Azure: Designing for High Performance - General Performance Concepts
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Creating and Altering Tables
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Managing Internationalization Considerations
  •  Programming Microsoft SQL Server 2005 : Deployment (part 2) - Testing Your Stored Procedures
  •  Programming Microsoft SQL Server 2005 : Deployment (part 1) - Deploying Your Assembly
    Video tutorials
    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Install Windows Server 2012 On VirtualBox

    - How To Disable Windows 8 Metro UI

    - How To Install Windows Store Apps From Windows 8 Classic Desktop

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
    programming4us programming4us