Export (0) Print
Expand All
Automate Testing of Your Stored Procs
Streamline Your Database Setup Process with a Custom Installer
Stop SQL Injection Attacks Before They Stop You
Get a Lean, Mean Dev Machine with the Express Editions of Visual Basic and SQL Server 2005
Keep Bad Guys at Bay with the Advanced Security Features in SQL Server 2005
AMO Lets You Dig Deeper into Your Data from Your Own Applications
Make Sense of Your Web Feedback using SQL Server 2005
Fuzzy Lookups and Groupings Provide Powerful Data Cleansing Capabilities
Unearth the New Data Mining Features of Analysis Services 2005
Using XQuery, New Large DataTypes, and More
Expand Minimize
182 out of 228 rated this helpful - Rate this topic

XML Support in Microsoft SQL Server 2005

SQL Server 2005

Shankar Pal, Mark Fussell, and Irwin Dolobowsky
Microsoft Corporation

December 2005

Applies to:
   Microsoft SQL Server
   Microsoft .NET Framework 2.0

Summary: This paper provides an overview of the XML support that is built into Microsoft SQL Server 2005. It includes a discussion of how XML integrates with client-side programming support in both the .NET Framework 2.0 and native code such as OLEDB and SQLXML. (40 printed pages)

Click here to download the Word document of this article, XML_Support_in_SQL.doc.


Motivating Scenarios for XML Storage
Server-Side XML Processing in SQL Server 2005
Client-Side XML Processing in SQL Server 2005


eXtensible Markup Language (XML) has been widely adopted as a platform-independent format for data representation. It is useful for exchanging information among loosely coupled, disparate systems, such as in business-to-business (B2B) applications and workflow situations. Data interchange has been a major driver of XML technologies.

XML is increasingly present in enterprise applications that are used for modeling semi-structured and unstructured data. One such application is document management. Documents (e-mail messages, for example) are semi-structured by nature. If documents are stored inside a database server as XML, powerful applications can be developed such as:

  • Applications that retrieve documents based on their content.
  • Applications that query for partial content, such as finding the section whose title contains the word "Background."
  • Applications that aggregate documents.

Such scenarios are becoming feasible with the increase in the development and availability of applications that generate and consume XML. For example, the Microsoft Office 2003 System allows users to generate Microsoft Word, Excel, Visio, and Infopath documents as XML markup.

Why Use Relational Databases for XML Data?

Storing XML data in a relational database provides benefits in the areas of data management and query processing. Microsoft SQL Server provides powerful query and data modification capabilities over relational data. In SQL Server 2005, these capabilities are extended to querying and modifying XML data. This allows your company to leverage investments made over past releases, such as investments in the areas of cost-based optimizations and data storage. For example, indexing techniques in relational databases are well-known. These have been extended to indexing XML data so that queries can be optimized using cost-based decisions.

XML data can interoperate with existing relational data and SQL applications. This means that XML can be introduced into the system as data modeling needs arise without disrupting existing applications. The database server also provides administrative functionality for managing XML data (for example, backup, recovery, and replication).

Native XML support within SQL Server 2005 is necessary to address increasing XML usage. Enterprise application development benefits from the XML support in SQL Server 2005.

The following sections give an overview of XML support in SQL Server 2000 and 2005, describe some of the motivating scenarios for XML usage, and include detailed discussions of the server-side and client-side XML feature sets.

XML Support in SQL Server 2000

This section provides a brief, high-level overview of the XML support in SQL Server 2000 and subsequent Web releases of the SQLXML client-side programming platform that provides rich support for mapping data back and forth between the relational and the XML data models.

Server-Side Support

At the server, XML data is generated from tables and query results by using a FOR XML clause in a SELECT statement. This is ideal for data interchange and Web service applications. The converse of FOR XML is a relational rowset generator function named OpenXML; it extracts values from the XML data into columns of a rowset by evaluating XPath 1.0 expressions. OpenXML is used by applications that shred incoming XML data into tables or for querying by using the Transact-SQL language.

Client-Side Support

The client programming support for SQL Server 2000 is referred to as SQLXML. (For more information, see SQLXML on the Microsoft Developer Network (MSDN)). At the center of this technology is XML view, which is a bi-directional mapping of an XML Schema to relational tables. SQL Server 2000 supports only the mapping of XDR schemas, though support for XSD was added in later Web releases. The XML view allows querying by using a subset of XPath 1.0 where the mapping is used to translate the path expressions into SQL queries on the underlying tables, and the query results are packaged into XML results.

SQL XML also enables you to create XML templates that you can use to create an XML document that has dynamic sections. Within the XML document, you can embed FOR XML queries and XPath 1.0 expressions over mapping queries. When the XML template is executed, the query block is replaced with the result of the query. In this way you can create XML documents that include both some static content and some dynamic content that is data-driven.

In SQL Server 2000, there are two main ways to access SQLXML functionality.

  • SQLXMLOLEDB Provider—The SQLXMLOLEDB Provider is an OLE DB provider that exposes Microsoft SQLXML functionality through ADO.
  • HTTP Access—SQLXML functionality in SQL Server 2000 can also be accessed via HTTP by using the SQLXML ISAPI filter. By using our configuration tool, you can set up a Web site to receive incoming requests to execute the XML templates, and the FOR XML and XPath 1.0 statements over XML views using HTTP.

Limitations in XML Support

The server and client programming platforms provide rich support for the generation and consumption of XML data based on mapping between tabular and XML data. This handles fairly structured XML data well. In SQLXML, the query language is a subset of XPath 1.0 and has some limitations. For example, the descendant axes (//-operator) are not supported. Consequently, restrictions exist that affect the development of certain solutions. For example, XML document order is not preserved, which is so crucial for applications such as document management. Furthermore, recursive XML schemas are not supported. In spite of these limitations, client SQLXML and server XML functionality have been widely used in application development. SQL Server 2005 addresses many of these limitations, extends the relational-XML interchange features, and provides native XML support.

Overview of XML Support in SQL Server 2005

This section provides a brief, high-level overview of the new XML support in SQL Server 2005, which is complemented by support in the .NET Framework 2.0 and by native client data access such as OLE DB.

XML Data Type

The XML data model has characteristics that make it very hard if not practically impossible to map to the relational data model. XML data has a hierarchical structure that may be recursive; relational databases provide weak support for hierarchical data (modeled as foreign key relationships). Document order is an inherent property of XML instances and must be preserved in query results. This is in contrast with relational data, which is unordered; order must be enforced with additional ordering columns. Re-assembling the result during querying is costly for realistic XML schemas that decompose the XML data into a large number of tables.

SQL Server 2005 introduces a native data type called XML. A user can create a table that has one or more columns of type XML in addition to relational columns. XML variables and parameters are also allowed. XML values are stored in an internal format as large binary objects (BLOBs) in order to support the XML model characteristics, such as document order and recursive structures, more faithfully.

SQL Server 2005 provides XML schema collections as a way to manage W3C XML Schemas as metadata. An XML data type can be associated with an XML schema collection to enforce schema constraints on XML instances. When the XML data is associated with an XML schema collection, it is called typed XML; otherwise it is called untyped XML. Both typed and untyped XML are accommodated within a single framework, the XML data model is preserved, and query processing enforces XML semantics. The underlying relational infrastructure is used extensively for this purpose. It supports interoperability between relational and XML data, thereby making way for more widespread adoption of the XML features.

XML Data Type Query and Data Modification

XML instances can be retrieved by using a Transact-SQL SELECT statement. Five built-in methods on the XML data type are provided to query and modify XML instances.

The XML data type methods accept XQuery. XQuery is an emerging W3C standard language (currently in Last Call). It includes the navigational language XPath 2.0. A language for modifying XML data, such as adding or deleting subtrees and updating scalar values, is also available. Together with a large set of functions, embedded XQuery and data modification languages provide rich support for manipulating XML data.

The XQuery type system is aligned with that of W3C XML schema types. Most of the SQL types are compatible with the XQuery type system (for example, decimal). A handful of types (for example, xs:duration) are stored in an internal format and suitably interpreted to be compatible with the XQuery type system.

The compilation phase checks static type correctness of XQuery expressions and data modification statements, and uses XML schemas for type inferences in the case of typed XML. Static type errors are raised if an expression could fail at run time due to a type safety violation.

XML Indexing

Query execution processes each XML instance at run time. This becomes expensive whenever the XML value is large in size or the query is evaluated on a large number of rows in a table. Consequently, a mechanism for indexing XML columns is provided to speed up queries.

B+trees have been extensively used for indexing relational data. A primary XML index on an XML column creates a B+tree index on all tags, values, and paths of the XML instances in the column. The primary XML index provides efficient evaluation of queries on XML data and reassembly of the XML result from the B+tree, while preserving document order and document structure.

Secondary XML indexes can be created on an XML column to speed up different classes of commonly occurring queries: PATH index for path-based queries, PROPERTY index for property bag scenarios, and VALUE index for value-based queries.

XML Schema Processing

XML columns, variables, and parameters can optionally be typed according to a collection of XML schemas that may be related (for example, by using <xs:import>) or unrelated to one another. Each typed XML instance specifies the target namespace from the XML schema collection it conforms to. During data assignment and modification, the database engine validates the instance according to the XML schema.

XML schema information is used in storage and query optimizations. Typed XML instances contain typed values in the internal, binary representation as well as in XML indexes. This provides efficient processing of typed XML data.

Relational and XML Integration

Users can store both relational and XML data within the same database. Briefly, the database engine knows how to honor the XML data model in addition to the relational data model. Relational data and SQL applications continue to behave correctly upon upgrade to SQL Server 2005. XML data residing in files and text or image columns can be moved into XML data type columns at the server. The XML column can be indexed, queried, and modified by using the XML data type methods.

The database leverages existing relational infrastructure and engine components such as the storage engine and the query processor for XML processing. For example, XML indexes create B+trees and query plans can be viewed in Showplan output. Because data management functionality, such as backup/restore and replication, is integrated into the relational framework, this functionality is available on XML data. In addition, the new data management features, such as database mirroring and snapshot isolation, work with the XML data type to provide a seamless user experience.

Structured data should be stored in tables and relational columns. The XML data type is a suitable choice for semi-structured and markup data using XML when the application needs to perform fine-grained query and modification of the data.

FOR XML and OpenXML enhancements

The existing FOR XML functionality has been enhanced in several ways. It works over XML data type instances and other new SQL types such as [n]varchar(max). For more information about FOR XML enhancements, see What's New in FOR XML in Microsoft SQL Server 2005 on MSDN.

The new TYPE directive generates an XML data type instance that can be assigned to an XML column, variable, or parameter, or queried by using XML data type methods. This allows the nesting of SELECT… FOR XML TYPE statements.

The PATH mode allows users to specify the path in the XML tree where a column's value should appear and—together with the aforementioned nesting—is more convenient to write than FOR XML EXPLICIT.

The directive XSINIL, used in conjunction with ELEMENTS, maps NULL to an element with the attribute xsi:nil="true". Also, the new ROOT directive allows a root node to be specified in all modes of FOR XML. The new XMLSCHEMA directive generates an XSD inline schema. FOR XML in SQL Server 2005 also allows users to specify element names to replace the default <row> in FOR XML RAW mode.

OpenXML functional enhancements consist of the ability to accept the XML data type in sp_preparedocument, and to generate XML and new SQL type columns in the rowset.

Client Access to XML Data Type

Clients can access XML data in the server in several ways. Native SQL client access by using ODBC and OLE DB delivers XML data as a Unicode string. OLE DB also provides ISequentialStream access to XML data for streaming Unicode data.

Managed access via ADO.NET in the .NET Framework 2.0 delivers XML data as a new class called SqlXml. It supports a method named CreateReader() that returns an XmlReader instance to read the returned XML. Equally, DataSet can load instances of the XML data type into columns on the mid-tier that can be edited as an XML document and saved back to SQL Server. Both of these enable SQL queries to be issued to the server to retrieve XML columns for manipulation on the mid-tier.

SOAP access directly to HTTP endpoints in SQL Server 2005 can be used to query, retrieve, and modify XML data.

Both native and managed client technologies provide new interfaces for retrieving the XML schema collection that types an XML column.

Motivating Scenarios for XML Storage

XML data is becoming more pervasive. It can represent customer data, with or without XML schemas that describe the data. The XML data and the XML schemas must be managed together. Quite often, the XML schemas for realistic applications are complex. Mapping such XML schemas to tables and columns is a complex task. Maintaining this mapping over time, when XML schemas change or new ones are added to the system, is troublesome. Quite often, XML data is stored in the file system or in text columns at the database server. Using a text column provides the benefits of data management capabilities, such as replication and backup/restore, but it does not allow query support based on the XML structure of the data. With native XML support, application development using XML becomes faster.

Custom Property Management

Some applications, such as user interface software, allow users to choose from a fixed set of properties. Others allow users to define their own custom properties of interest. These custom properties can be managed nicely if they are stored in XML format. Applications can support more types of properties than just scalar properties. They can also support:

  • Multi-valued properties on objects, such as multiple phone numbers.
  • Complex properties; for example, the author property of a document might be the author's contact information.

Object properties can be stored in an XML data type column and indexed for efficient query processing.

Data Exchange and Workflow

XML allows a platform-independent way of exchanging data among applications. The data can be modeled as messages with XML markup. Instead of constantly shredding and generating XML messages, it is prudent to store messages in XML format. This fits well with data flow requirements. An XML message reaching a workflow stage carries the current state. Each message is processed, the progress is recorded in the XML content (for example, state change), and the XML data is forwarded to the next stage of workflow processing. Because messages might be of different types or even semi-structured, and have different XML schemas associated with them, mapping them to tables is not always an easy task.

XML-based standards are emerging for different, vertical domains, such as for financial and geo-spatial data. These standards describe the structure of the data, based on which instance data can be queried and updated. Quite often, the actual data is in binary form, while the XML data provides metadata information regarding them.

As a simple example, to pass a table of input parameters to a stored procedure or function, an application converts the data to XML and passes it in as an XML data type parameter. Within the stored procedure or function, the rowset is regenerated from the XML parameter.

Document Management

Suppose a call center maintains patient records and conversations as an XML document. When a patient calls in, the call center wants to recall the previous conversation to set the context of the incoming call. This is possible by querying XML markup, which benefits the application. Furthermore, viewing the details of conversations that occurred on earlier occasions and recording the current conversation are facilitated.

Documents, such as e-mail messages, are semi-structured in nature. Documents with XML markup are becoming easier to create, for example, with Microsoft Office 2003. These XML documents can be stored in XML columns, indexed, queried, and updated. Thus, developers can do more with native XML support.

Server-Side XML Processing in SQL Server 2005

SQL Server 2005 support consists of providing one database in which you can store both relational and XML data.

The XML Data Type

You can create a table with an XML column by using the usual CREATE TABLE statement. The XML column can then be indexed in a special way.

Untyped XML

The SQL Server 2005 XML data type implements the ISO SQL-2003 standard XML data type. As such, it can store not only well-formed XML 1.0 documents but also so-called XML content fragments with text nodes and an arbitrary number of top-level elements. Checks for well-formedness of the data are performed, which does not require the XML data type to be bound to XML schemas. Data that is not well-formed is rejected.

Untyped XML is useful when the schema is not known a priori so that a mapping-based solution is not possible. It is also useful when the schema is known but mapping to a relational data model is very complex and hard to maintain, or multiple schemas exist and are late bound to the data based on external requirements.

Example: Untyped XML Column in Table

The following statement creates a table named docs with an integer primary key named pk and an untyped XML column named xCol.

CREATE TABLE docs (pk INT PRIMARY KEY, xCol XML not null)

A table can also be created with more than one XML or relational column with or without a primary key.

Typed XML

If you have XML schemas that describe your XML data in an XML schema collection, you can associate the XML schema collection with the XML column to yield typed XML. The XML schemas are used to validate the data, perform more precise type checks than untyped XML during compilation of query and data modification statements, and optimize storage and query processing.

Type information is stored in both the XML BLOB and the XML indexes, and uses more space for XML BLOBs. Typed XML yields better performance with value-based queries (that is, where the search value is more selective than, say, the path in which the value occurs) by avoiding run-time value conversions so that index seeks into the XML indexes become possible.

Typed XML columns, parameters, and variables can store XML documents or content, which you can specify as an option (DOCUMENT or CONTENT, respectively, with CONTENT as the default) at the time of declaration. Furthermore, you must provide the collection of XML schemas. Specify DOCUMENT if each XML instance has exactly one top-level element; otherwise, use CONTENT. The query compiler uses the DOCUMENT flag in type checks to infer Singleton top-level elements.

Example: Typed XML Column in Table

XML columns, variables, and parameters can be bound to a collection of XML schemas. (For more details and examples, see XML schema processing later in this paper.) Suppose myCollection is the name of one such collection. The following statement creates a table named XmlCatalog with an XML column Document typed using myCollection. The typed XML column is also specified to accept XML fragments, not just XML documents.

CREATE TABLE XmlCatalog ( 
   Document XML(CONTENT myCollection))

Constraining XML Data Type Columns

In addition to typing an XML column, you can use relational (column or row) constraints on typed and untyped XML data type columns. Most SQL constraints are applicable to XML columns as well. The notable exceptions to this are unique, primary key, and foreign key constraints, since XML data type instances cannot be compared. Thus, you can specify an XML column to be nullable or non-nullable, supply a default value, and define CHECK constraints on the column. For example, an untyped XML column can have a CHECK constraint to verify that the stored XML instances confirm to an XML schema.

Use constraints under the following conditions:

  • Your business rules cannot be expressed in XML schemas. For example, the delivery address of a flower shop must be within 50 miles of its business location. This can be written as a constraint on the XML column. The constraint may involve XML data type methods.
  • Your constraint involves other XML or non-XML columns in the table. An example is the enforcing the constraint so that the value of Customer ID (that is, the result of the path expression /Customer/@CustId) in an XML instance equals the value of an integer in the CustomerID column.
  • You want to limit an XML schema collection to the top-level elements or the schema namespaces that are to be permitted in a typed XML column. This is useful when the same XML schema collection is used to type multiple XML columns and each XML column should contain a different top-level element. It is also useful when the insertion of XML instances conforming to only the latest version of an XML schema is allowed.

Example: Constraining XML Column

To ascertain that the <last-name> of an <author> of a <book> is different from the <author>'s <first-name>, the following CHECK constraint CK_name is specified. XML data type methods must be provided within a user-defined function, for which udf_Check_Names() is introduced.

CREATE FUNCTION udf_Check_Names (@xmlData XML) 
RETURN (SELECT @xmlData.exist('/book/author[first-name = last-name]'))

xCol XML not null
CONSTRAINT CK_name CHECK (dbo.udf_Check_Names(xCol) = 0))

Example: Limiting XML Schema Collection

Suppose the XML column Document in the table XmlCatalog (described in the typed XML column in table example) is to be constrained to allow the storage of only a single <dvdstore> element per row and to prevent the insertion of <bookstore> elements. The following CHECK constraint achieves this.

CREATE FUNCTION udf_Check_Top_Level_Nodes (
     @xmlData XML(CONTENT myCollection)) 
     RETURN (@xmlData.value ('count(/*)', 'int'))

CREATE FUNCTION udf_Check_Dvdstore_Nodes (
     @xmlData XML(CONTENT myCollection)) 
RETURN (@xmlData.value('declare default element namespace "http://myDVD";
      count(/dvdstore)', 'int'))

ADD CONSTRAINT dvdstore_only_check 
CHECK (dbo.udf_Check_Top_Level_Nodes (Document) = 1 AND 
      dbo.udf_Check_Dvdstore_Nodes (Document) = 1)

Text Encoding

SQL Server 2005 stores XML data as Unicode (UTF-16). XML data retrieved from the server comes out in UTF-16 encoding as well. If you want a different encoding, you must perform the necessary conversion, either by casting or on the mid-tier, after retrieving the data. For example, you could cast your XML data to varchar type on the server, in which case the database engine serializes the XML with an encoding determined by the collation of the varchar.

Storing XML Data

You can supply an XML value for an XML column, parameter, or variable in multiple ways:

  • As a character or binary SQL type that is implicitly converted to XML data type.
  • As the content of a file.
  • As the output of the XML publishing mechanism FOR XML with the TYPE directive that generates an XML data type instance.

The supplied value is checked for well-formedness. An XML column by default allows both XML documents and XML fragments to be stored. If the data fails the well-formedness check, it is rejected with an appropriate error message.

For typed XML, the supplied value is checked for conformance to XML schemas that are registered with the XML schema collection that is typing the XML column. The XML instance is rejected if it fails this validation. Furthermore, the DOCUMENT flag on typed XML restricts accepted values to XML documents only while CONTENT allows both XML document and content to be supplied.

Example: Inserting Data into Untyped XML Column

The following statement inserts a new row into the table named docs with the value 1 for the integer column pk and a <book> instance for the XML column. The <book> data, supplied as a string, is implicitly converted to XML data type and checked for well-formedness during insertion.

INSERT INTO docs VALUES (1, '<book genre="security"
publicationdate="2002" ISBN="0-7356-1588-2">
   <title>Writing Secure Code</title>
'<doc id="123">
   <section num="1"><title>XML Schema</title></section>
   <section num="3"><title>Benefits</title></section>
   <section num="4"><title>Features</title></section>

Example: Inserting Data into Untyped XML Column from File

The INSERT statement in the following code segment reads the content of file C:\temp\xmlfile.xml as a BLOB by using OPENROWSET. A new row is inserted into the table named docs with a value of 10 for the primary key and the BLOB for the XML column xCol. A well-formedness check occurs when file content is assigned to the XML column.

SELECT 10, xCol
      (BULK 'C:\temp\xmlfile.xml',
      SINGLE_BLOB) AS xCol) AS R(xCol)

Example: Inserting Data into Typed XML Column

Typed XML columns require that the XML instance data specify the target namespace of the XML schema that is used to type it (the namespace may be empty). In the following code segment, this is done via the namespace declaration xmlns=http://myDVD.

INSERT XmlCatalog VALUES(2, 
'<?xml version="1.0"?>
<dvdstore xmlns="http://myDVD">
  <dvd genre="Comedy" releasedate="2003">
    <title>My Big Fat Greek Wedding</title>

Example: Storing XML Data Generated Using FOR XML with TYPE Directive

FOR XML has been enhanced with a TYPE directive to generate the result as an XML data type instance. The resulting XML can be assigned to an XML column, variable, or parameter. In the following statement, the XML instance generated by using FOR XML TYPE is assigned to an XML data type variable @xVar. The variable can be queried using XML data type methods.


Storage Representation

XML data type instances are stored in an internal, binary representation that is streamable and optimized for efficient parsing. Tags are mapped to integer values and the mapped values are stored in the internal representation. This yields some compression of the data, as well.

For untyped XML, node values are stored as Unicode (UTF-16) strings, so that run-time type conversion is required to perform operations. For example, to evaluate the predicate /book/price > 9.99, the value of the book's price is converted to decimal. On the other hand, for typed XML, values are encoded in the type specified in the XML schemas. This makes parsing of the data much more efficient and avoids run-time conversion.

The stored binary form is limited to 2 GB per XML instance, which can accommodate most XML data. Furthermore, the depth of XML hierarchy is limited to 128 levels.

The InfoSet content of the XML data is preserved. It may not be an exact copy of the text XML, since the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration. (For more information about InfoSet, see the WC3 recommendations for XML information at http://www.w3.org/TR/xml-infoset).

Data Modeling Considerations

Quite often, a combination of relational and XML data type columns is appropriate for data modeling. Some of the values from your XML data can be stored in relational columns, and the rest, or the entire XML value, stored in an XML column. This may yield better performance and locking characteristics.

Values within the XML data can be promoted to computed columns in the same table for Singleton values (that is, single-valued properties). A multi-valued property requires a separate table for the property, which must be populated and maintained by using triggers. Queries need to be written directly against the property table.

The granularity of the XML data stored in an XML column is critical for locking and update characteristic. SQL Server employs the same locking mechanism for both XML and non-XML data. When the granularity is large, locking large XML instances for updates causes throughput to decline in a multi-user scenario. On the other hand, severe decomposition loses object encapsulation and raises re-assembly cost.

Querying and Modifying XML Data

Querying XML instances stored in an XML column requires parsing binary XML data in the column. Parsing binary XML is much faster than parsing the text form of the XML data. XML indexing avoids reparsing and is discussed in Indexing XML data later in this paper.

Methods on XML Data Type

You can retrieve entire XML values or you can retrieve parts of XML instances. This is possible by using four XML data type methods that take an XQuery expression as argument: query(), value(), exist() and nodes(). A fifth method, modify(), allows modification of XML data and accepts an XML data modification statement as input.

  • The query() method is useful for extracting parts of an XML instance. The XQuery expression evaluates to a list of XML nodes. The subtree rooted at each of these nodes is returned in document order. The result type is untyped XML.
  • The value() method extracts a scalar value from an XML instance. It returns the value of the node the XQuery expression evaluates to. This value is converted to a Transact-SQL type specified as the second argument of the value() method.
  • The exist() method is useful for existential checks on an XML instance. It returns 1 if the XQuery expression evaluates to non-null node list; otherwise it returns 0.
  • The nodes() method yields instances of a special XML data type, each of which has its context set to a different node that the XQuery expression evaluates to. The special XML data type supports the query(), value(), nodes(), and exist() methods, and can be used in count(*) aggregations and NULL checks. All other uses result in an error.
  • The modify() method permits modifying parts of an XML instance, such as adding or deleting subtrees, or replacing scalar values such as the price of a book from 9.99 to a 39.99.

Example: Using Query() Method

Consider the following query on the XML column named xCol of table docs that extracts <section> elements anywhere under the <doc> element whose id is 123. The query also retrieves the value from the integer primary key column. The query() method in the SELECT list is evaluated for each row in the table yielding a sequence of <section> elements, which, together with their subtrees, are retrieved in document order. Each XML instance without a <doc> element having id 123 or without a <section> element below it returns no result; the return value of the query() method is empty XML.

SELECT pk, xCol.query('/doc[@id = 123]//section')   
FROM   docs

Empty XML values can be filtered in an outer SELECT statement. Alternatively, the exist() method may be used, as shown in the next example.

Example: Using Exist() Method

Consider the following query. It involves the query() and exist() methods on the XML column xCol of table docs. The exist() method evaluates the path expression /doc[@id = 123], checking for the existence of a top-level <doc> element that has an attribute named id with the value 123. For each such row, the query() method in the SELECT clause is evaluated; in this example, the query() method yields a sequence of <section> elements anywhere under the <doc> element. Any row that returns 0 from the exist() method is skipped.

SELECT xCol.query('/doc[@id = 123]//section')   
FROM   docs
WHERE  xCol.exist ('/doc[@id = 123]') = 1

Example: Using Value() Method

The following query extracts the title of the third section of a document as Unicode string using the value() method. The SQL type nvarchar(max) of the result is specified as the second argument of the value() method. The XQuery function data() extracts a scalar value from the <title> node.

SELECT xCol.value(
   'data((/doc//section[@num = 3]/title)[1])', 'nvarchar(max)') 
FROM   docs

Example: Using GROUP BY with XML Data Type Methods

XML data type methods are not allowed in the Transact-SQL GROUP BY clause. However, you can extract values from an XML column in a subquery, alias the grouping columns, and use the aliases in the GROUP BY clause. The following query illustrates this by computing the number of books published by authors with the same first name.

SELECT Fname, count(Fname)
  (SELECT nref.value('(author/first-name)[1]', 'nvarchar(max)') Fname
   FROM   docs CROSS APPLY xCol.nodes('/book') T(nref)
   WHERE  nref.exist ('author/first-name') = 1) Result

Example: Executing SQLCMD

Executing XQuery and XML data modification statements requires that the connection option QUOTED_IDENTIFIER be ON. The default value of this option in SQLCMD is OFF. This must be changed to ON by using the –I switch.

sqlcmd -E -I -d <database> -Q "SELECT xCol.query('//author') FROM docs"

Example: Difference in Behavior Between cast() and value() Method

The query() method returns XML data type instances, which have special XML characters entitized when converted to a string type. A value() method, on the other hand, returns an SQL type value, which does not entitize the special characters. This difference is evident in event notification. While the first of the following two queries may contain entitized carriage returns:


the second query does not:

  /EVENT_INSTANCE/TSQLCommand/CommandText/text()') AS nvarchar(max)) 

The XQuery Language

There are numerous sources of XML from Office documents stored on the file system, Web services, or configuration files. In fact, data is being increasingly generated either in XML format or as virtual XML documents. To cope with this increasing amount of data with a powerful query language, XQuery was conceived. The justification for XQuery is described in the W3C XQuery Language specification as:

  • A query language that uses the structure of XML intelligently can express queries across all kinds of data, whether physically stored in XML or viewed as XML via middleware. This specification describes a query language called XQuery, which is designed to be broadly applicable across many types of XML data sources.
  • XQuery is designed to meet the requirements identified by the W3C XML Query Working Group XML Query 1.0 Requirements and the use cases in XML Query Use Cases. It is designed to be a language in which queries are concise and easily understood. It is also flexible enough to query a broad spectrum of XML information sources, including both databases and documents.
  • XQuery can also be summarized in the following statement: The XQuery language is to XML as the SQL language is to relational databases.

A subset of XQuery embedded in Transact-SQL is the language supported for querying the XML data type. The language is under development (currently in Last Call) by the World Wide Web Consortium (W3C) with the participation of all major database vendors including Microsoft Corporation. Our implementation is aligned with the July 2004 draft of XQuery.

XQuery includes XPath 2.0 as a navigation language. The implementation of XQuery by SQL Server 2005 provides constructs for iteration over nodes (FOR), node check (WHERE), returning values (RETURN), and sorting (ORDER BY). It also provides element construction for reshaping data during querying.

SQL Server 2005 also provides language constructs for data modification language (DML) of the XML data type. (For more information, see XML Data Modification later in this document). The following example shows how to use XQuery on the XML data type.

Example: Using Rich Language Constructs in XQuery

The following query shows several XQuery language constructs used together. It returns the title, wrapped in a new tag <topic>, of sections with section number 3 and higher from a document with id 123.

SELECT pk, xCol.query('
   FOR $s in /doc[@id = 123]//section
   WHERE $s/@num >= 3
   RETURN <topic>{data($s/title)}</topic>')   
FROM docs

The FOR iterates over all <section> elements under <doc> elements with id 123, and binds each such <section> to the variable $s. The WHERE clause ensures that the section number (@num attribute of the <section> element) is 3 or higher. The query returns the value of the section <title> in document order wrapped in a constructed element called <topic>.

Query Compilation and Execution

The SQL statement is parsed by the SQL parser. When it encounters the XQuery expression, it jumps into the XQuery compiler, which then compiles the XQuery expression. This yields a query tree that is grafted into the tree for the overall query.

The overall query tree undergoes query optimization and produces a physical query plan that it picked based on cost-based estimates. The Showplan output shows mostly relational operators and some new operators such as UDX for XML processing.

Query execution is tuple-oriented as in the rest of the relational framework. The WHERE clause is evaluated on each row of the table named docs; this involves parsing the XML BLOB at run time to evaluate XML data type methods. If the condition is satisfied, the row is locked and the SELECT clause is evaluated in the row. The result is produced as XML data type for query() method and converted into the specified target type for value() method.

If, on the other hand, the row does not satisfy the conditions in the WHERE clause, it is skipped and execution moves to the next row.

XML Data Modification

SQL Server 2005 provides constructs for data modification as an extension to XQuery. Subtrees can be inserted before or after a specified node, or as the leftmost or rightmost child. Furthermore, a subtree can be inserted into a parent node, in which case it becomes the rightmost child of the parent. Attribute, element, and text node insertions are all supported.

Deletion of subtrees is supported. In this case, the entire subtree is removed from the XML instance.

Scalar values can be replaced with new scalar values.

Example: Insertion of Subtree into XML Instances

This example shows the use of the modify() method to insert a new <section> element to the right of the <section> element whose number is 1.

UPDATE docs SET xCol.modify('
   <section num="2">   
  after (/doc//section[@num=1])[1]')

Example: Update Price of this Book to $49.99

The following UPDATE statement replaces the <price> of a book whose ISBN is 1-8610-0311-0 to $49.99. The XML instance is typed with the XML schema http://myBooks, hence the namespace declaration in the XML data modification statement.

UPDATE XmlCatalog
SET    Document.modify ('
    declare namespace bk = "http://myBooks";
    replace value of (/bk:bookstore/bk:book
[@ISBN="1-861003-11-0"]/bk:price)[1] with 49.99')

Type Checking and Static Errors

XQuery introduces type checking. The compilation phase checks the static type correctness of XQuery expressions and data modification statements, and uses XML schemas for type inferences in case of typed XML. It raises static type errors if an expression could fail at run time due to a type safety violation. Examples of static errors are the addition of a string to an integer, receiving a sequence of values where the operation expects a single value, and querying for a non-existent node for typed data. Explicit casting to the proper type is a workaround for static errors resulting from type mismatches. XQuery run-time errors are converted into empty sequences.

Location steps, function parameters, and operators (for example, eq) that require Singletons return an error if the compiler cannot determine whether a Singleton is guaranteed at run time. The problem arises often with untyped data. For example, the lookup of an attribute requires a Singleton parent element; an ordinal selecting a single parent node is adequate.

Example: Type Checks in value() Method

The following query on an untyped XML column requires an ordinal specification on //author/last-name since the value() method expects a Singleton node as the first argument. Without it, the compiler cannot determine whether only one <last-name> node will occur at run time.

SELECT xCol.value('(//author/last-name)[1]',
   'nvarchar(50)') LastName
FROM   docs

The evaluation of the node()-value() combination to extract attribute values may not require the ordinal specification, as shown in the next example.

Example: Known Singleton

The nodes() method in the following statement generates a separate row for each <book> element. The value() method evaluated on a <book> node extracts the value of @genre, which, being an attribute, is a Singleton.

SELECT nref.value('@genre', 'varchar(max)') LastName
FROM   docs CROSS APPLY xCol.nodes('//book') AS R(nref)

Binding Relational Data in XQuery and XML DML

When your data resides in a combination of relational and XML data type columns, you might want to write queries that combine relational and XML data processing. You can convert the data in relational and XML columns to an XML data type instance using FOR XML with the TYPE directive and query it by using XQuery. Conversely, you can generate a rowset from XML values and query it by using Transact-SQL.

A more convenient and efficient way of writing cross-domain queries is to use the value of an SQL variable or column within XQuery or XML data modification expressions as follows:

  • Apply the value of a SQL variable in your XQuery or XML DML expression using sql:variable().
  • Use the values from a relational column in XQuery or XML DML context with sql:column().

This approach allows applications to parameterize queries, as shown in the following example. sql:column() is used in a similar way and provides additional benefits. Indexes over the column can be used for efficiency as decided by the cost-based query optimizer. Furthermore, a computed column can be used.

XML and user-defined types cannot be used with sql:variable() and sql:column().

Example: Binding Relational Data Using sql:variable()

In this query, the ISBN of a <book> element is passed in using a SQL variable named @isbn. Instead of using a constant, sql:variable() supplies the value of the ISBN, and the query can be used to search for any ISBN, not just the one whose ISBN is 0-7356-1588-2.

DECLARE @isbn varchar(20)
SET     @isbn = '0-7356-1588-2'
FROM    docs
WHERE   xCol.exist ('/book[@ISBN = sql:variable("@isbn")]') = 1

Example: Specifying Ordinal Position Using sql:variable()

The following query retrieves the <last-name> of the N-th <author> of a <book> where the value of N is specified by using sql:variable():

DECLARE @aNum int
SET     @aNum = 2
SELECT  xCol.value ('(/book/author,
FROM    docs

Rowset Generation from XML Data

In custom property management and data interchange scenarios, applications quite often map some part of the XML data to a rowset. For example, to pass a table of input parameters to a stored procedure or function, an application converts the data to XML and passes it in as an XML data type parameter. Within the stored procedure or function, the rowset is regenerated from the XML parameter.

SQL Server 2000 provides OpenXml() for this purpose. It is a facility for generating a rowset from an XML instance by specifying the relational schema for the rowset and how values inside the XML instance map to columns in the rowset.

Alternatively, the nodes() method can be used to generate node contexts within an XML instance, and use the node contexts in value(), query(), exist(), and nodes() methods to generate the desired rowset. The nodes() method accepts an XQuery expression, evaluates it on each XML instance in an XML column, and uses XML indexes effectively. The next example illustrates the use of the nodes() method for rowset generation.

Example: Extract Properties from XML Instance

Suppose you want to extract first and last names of authors, whose first name is not "David", as a rowset consisting of two columns, FirstName and LastName. Using the nodes() and value() methods, you can achieve this as follows:

SELECT nref.value('first-name[1]', 'nvarchar(50)') FirstName,
       nref.value('last-name[1]', 'nvarchar(50)') LastName
FROM   docs CROSS APPLY xCol.nodes('//author') AS R(nref)
WHERE  nref.exist('.[first-name != "David"]') = 1

In this example, nodes('//author') yields a rowset of references to <author> elements for each XML instance. The first and last names of authors are obtained by evaluating value() methods relative to those references. For good performance, the XML column should be indexed, which is the topic of the next section.

Example: Extract Properties from XML Variable

The CROSS APPLY operator in the previous query is not required when properties are extracted from an XML variable or parameter. This example considers an XML variable named @xVar to which a <book> instance is assigned and retrieves the <first-name> and <last-name> of authors.

SET @xVar = 
  '<book genre="security" publicationdate="2002" ISBN="0-7356-1588-2">
   <title>Writing Secure Code</title>

SELECT nref.value('first-name[1]', 'nvarchar(50)') FirstName,
       nref.value('last-name[1]', 'nvarchar(50)') LastName
FROM   @xVar.nodes('//author') AS R(nref)
WHERE  nref.exist('.[first-name != "David"]') = 1

Indexing XML Data

XML data is stored in an internal binary form and can be up to 2 GB in storage. Each query parses the XML blob at run time one or more times in each row of the table. This makes query processing slow. If querying is common in the workload, it is beneficial to index the XML column, although the cost of XML index maintenance during data modification must be taken into account.

XML indexes are created by using a new DDL statement on typed and untyped XML columns. This creates a B+tree for all XML instances in the column. The first index on an XML column is the primary XML index. Using it, three types of secondary XML indexes are supported on the XML column to speed up common classes of queries, as described in the following section.

Primary XML Index

The primary XML index requires a clustered index on the primary key of the base table (that is, the table in which the XML column is defined). It creates a B+tree on a subset of the Infoset items of XML nodes. Columns of the B+tree represent tags such as element and attribute names, node values, and node types. Other columns capture the document order and structure in the XML data, and the path from the root of the XML instance to each node for efficient evaluation of path expressions. The primary key of the base table is duplicated in the primary XML index to correlate index rows with base table rows.

Tags and type names given in XML schemas are mapped to integer values and the mapped values are stored in the B+tree to optimize storage. The path column in the index stores a concatenation of the mapped values in reversed order, that is, from a node to the root of the XML instance. The reverse representation allows path values to be matched when the path suffix is known (for example, in a path expression such as //author/last-name).

If the base table is partitioned, the primary XML index is partitioned the same way; that is, using the same partitioning function and partitioning scheme.

Full XML instances are retrieved from XML columns (for example, SELECT * FROM docs or SELECT xCol FROM docs). Queries involving XML data type methods use the primary XML index, returning scalar values, or XML subtrees from the index itself.

Example: Creating Primary XML Index

The following statement creates an XML index named idx_xCol on the XML column xCol of table docs.

CREATE PRIMARY XML INDEX idx_xCol on docs (xCol)

Secondary XML Indexes

Once the primary XML index is created, secondary XML indexes can be created to speed up different classes of queries within a workload. Three types of secondary XML indexes—PATH, PROPERTY, and VALUE—benefit path-based queries, custom property management scenarios, and value-based queries, respectively.

  • The PATH index builds a B+-tree on the columns (path, value) of the primary XML index. A value for the path is computed from a path expression; a node's value, if one is provided, is used as well. Since the leading fields of the PATH index are known, index seek into the PATH index speeds up evaluation of the path expression. The most common case is the use of the exist() method on XML columns in the WHERE clause of a SELECT statement.
  • The PROPERTY index creates a B+-tree on the columns (PK, path, value) of the primary XML index, where PK is the primary key of the base table. This index benefits property value lookups within an XML instance.
  • The VALUE index creates a B+-tree on the columns (value, path) of the primary XML index. This index benefits queries where a node's value is known but its path is imprecisely specified in the query. This typically occurs with descendant axes lookups, such as //author/last-name [. ="Howard"], where <author> elements can occur at any level of the hierarchy. It also occurs in wildcard queries, such as /book [@* = "novel"], where the query looks for <book> elements with some attribute having the value "novel". Furthermore, the VALUE index is useful for value-based range scan of typed XML.

Up to 128 levels of the XML hierarchy are accommodated; XML instances containing longer paths are rejected during insertion and modification.

Similarly, up to the first 128 bytes of a node's value are indexed; longer values are accommodated within the system and are not indexed.

Example: Path-Based Lookup

Suppose the following query is common in your workload.

FROM   docs
WHERE  xCol.exist ('/book[@genre = "security"]') = 1

The path expression /book/@genre and the value securitycorrespond to the key fields of the PATH index. Consequently, a secondary XML index of type PATH is helpful for this workload.

CREATE XML INDEX idx_xCol_Path on docs (xCol)

Example: Fetching Properties of an Object

Consider the following query that retrieves the properties genre, title, and ISBN of a book from each row in table T.

SELECT xCol.value ('(/book/@genre)[1]', 'varchar(50)'),
    xCol.value ('(/book/title)[1]', 'varchar(50)'),
    xCol.value ('(/book/@ISBN)[1]', 'varchar(50)')
FROM    docs

The property index is useful in this case and is created as follows:

CREATE XML INDEX idx_xCol_Property on docs (xCol)

Example: Value-Based Query

In the following query, the descendant-or-self axis (//-operator) specifies a partial path so that the lookup based on the value of ISBN benefits from the use of the VALUE index.

FROM    docs
WHERE    xCol.exist ('//book/@ISBN[. = "0-7356-1588-2"]') = 1

The VALUE index is created as follows:

CREATE XML INDEX idx_xCol_Value on docs (xCol)

Content Indexing

You can create a full-text index on XML columns; this indexes the content of the XML values while ignoring the XML markup. Attribute values are not full-text indexed (since they are considered part of the markup) and element tags are used as token boundaries. You can create both XML and full-text indexes on an XML column, and combine full-text search with XML index usage. Full-text search using CONTAINS() and XQuery fn:contains() have different semantics. The latter as implemented is a case-sensitive, substring search, while the former is a token match using stemming.

Use the full-text index as the first filter to narrow down the choices and then apply XQuery to filter further. The overall semantics is that of case-sensitive substring searches. Alternatively, a part of the XML data can be promoted to a computed XML column XC on which a full-text index is created. This defines XC as the full-text search context.

Example: Create Full-Text Index on XML Column

The steps for creating full-text index on an XML column are the same as those for other SQL type columns. A unique key column on the base table is required. The DDL statements are as follows, in which PK__docs__7F60ED59 is the single-column primary key index of the table.


Example: Combining Full-Text Search with XML Querying

The following query checks that an XML value contains the word Secure in the title of a book.

FROM   docs 
WHERE  CONTAINS(xCol,'Secure') 
AND    xCol.exist('/book/title/text()[fn:contains(.,"Secure")]') =1

The CONTAINS() method uses the full-text index to subset the XML values that contain the word Secure anywhere in the document. The exist() method ensures that the word Secure occurs in the title of a book.

Example: Search Context Defined by XML Data Type Methods

An alternative to the exist() method is to use the LIKE operator on the text value of the context node as shown in the following code. This yields a case-insensitive, substring search.

SELECT pk, xCol 
FROM     docs CROSS APPLY xCol.nodes('/book/title/text()') title(tRef)
WHERE  CONTAINS (xCol, 'Secure')   
AND    title.tRef.value ('.', 'NVARCHAR(MAX)') LIKE '%Secure%'

Query Execution Using XML Indexes

XML indexes speed up query execution. Queries are always compiled against the primary XML index on an XML column, if one exists. A single query plan is produced for the entire query (both relational and XML parts), which is optimized by using the database engine's cost-based optimizer. Secondary XML indexes are selected based on the query optimizer's cost estimates.

Catalog Views for XML Indexes

Catalog views exist to provide metadata information regarding XML indexes. The catalog view sys.indexes contains entries for XML indexes with the index "type" 3. The name column contains the name of the XML index.

XML indexes are also recorded in the catalog view sys.xml_indexes, which contains all the columns of sys.indexes and a few special ones meaningful for XML indexes. The value NULL in the column secondary_type indicates a primary XML index; the values 'P', 'R', and 'V' stand for PATH, PROPERTY, and VALUE secondary XML indexes, respectively. The column secondary_type_desc contains NULL for a primary XML index, and the strings "PATH," "PROPERTY," and "VALUE" for the three types of secondary XML indexes.

Space usage of XML indexes can be found by using the table-valued function sys.dm_db_index_physical_stats(). This function provides information such as the number of disk pages occupied, average row size in bytes, number of records, and other information for all index types, including XML indexes. This information is available for each database partition; XML indexes use the same partitioning scheme and partitioning function of the base table.

Example: Space Usage of XML Indexes

SELECT sum (page_count)
FROM    sys.dm_db_index_physical_stats (db_id(), object_id('docs'), 
    JOIN sys.xml_indexes SXI ON (SXI.index_id = SDPS.index_id)
WHERE    SXI.name = 'idx_xCol_Path'

This statement yields the number of disk pages occupied by the XML index idx_xCol_Path in table T across all partitions. Without the sum() function, the result would return the disk page usage per partition.

XML Schema Processing

XML schemas are optional in the system. As previously mentioned, XML data not bound to XML schemas is considered to be untyped—XML node values are stored as Unicode strings, and XML instances are checked for well-formedness. An untyped XML column can be indexed.

XML typing is done by associating an XML data type with XML schemas that are registered with an XML schema collection. A new DDL statement allows the creation of an XML schema collection with which one or more XML schemas may be registered. An XML column, parameter, or variable bound to an XML schema collection is typed according to all the XML schemas in the collection. Within an XML schema collection, the type system identifies each XML schema using its target namespace.

Each top-level XML element in an XML instance must specify a possible empty target namespace it conforms to. Data is validated during insertion and modification according to the target namespace of each top-level element. The binary XML representation encodes typed values based on the associated XML schema information and is fully described, so that reparsing it is more efficient as compared to reparsing untyped XML. Values are properly typed in XML indexes as well (for example, /book/price is stored as decimal if it is defined in the XML schema as xs:decimal).

During query compilation, XML schemas are used for type checking and static errors are issued for type mismatches. The query compiler also uses XML schemas for query optimizations.

The database engine's metadata subsystem contains XML type information such as XML schema collections and their contained XML schemas, and the mapping between the primitive XSD and relational type systems. Almost all of the W3C XML Schema 1.0 specification is supported. (For more information on the W3C XML Schema 1.0 specification, see http://www.w3.org/TR/2001/REC-xmlschema-1-20010502/ and http://www.w3.org/TR/2001/REC-xmlschema-2-20010502/) Comments and annotations in XML schema documents are not preserved, and key/keyref is not supported.

XML Schema Collections

An XML schema collection is a metadata entity, scoped by a relational schema. It contains one or more XML schemas that may be related (for example, using <xs:import>) or unrelated. Individual XML schemas within an XML schema collection are identified by using their target namespace. XML schema collections are securable entities, much like tables.

An XML schema collection is created by using CREATE XML SCHEMA COLLECTION syntax and providing one or more XML schemas. You can then type an XML column by using the XML schema collection. This design yields a flexible data model in which XML that is typed according to different XML schemas can be stored in the same column. This is especially convenient when the number of XML schemas is large. Furthermore, this design supports XML schema evolution to some extent.

Additionally, the option DOCUMENT or CONTENT on a typed XML column specifies whether XML trees or fragments, respectively, can be stored in the XML column. The default behavior is CONTENT. For DOCUMENT, each XML instance must specify the target namespace of its top-level element, according to which it is validated and typed. For CONTENT, on the other hand, each top-level element can specify any one of the target namespaces in the XML schema collection. The XML instance is validated and typed according to all the target namespaces occurring in the instance.

Example: Creating an XML Schema Collection

Suppose you want to use an XML schema with target namespace http://myBooks to type your XML instances. Create an XML schema collection named myCollection and supply the XML schema as the content of myCollection as shown in the following code segment.

'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
  <xsd:element name="bookstore" type="bookstoreType" />
  <xsd:complexType name="bookstoreType">
   <xsd:sequence maxOccurs="unbounded">
     <xsd:element name="book" type="bookType" />
  <xsd:complexType name="bookType">
     <xsd:element name="title" type="xsd:string" />
     <xsd:element name="author" type="authorName" />
     <xsd:element name="price" type="xsd:decimal" />
   <xsd:attribute name="genre" type="xsd:string" />
   <xsd:attribute name="publicationdate" type="xsd:string" />
   <xsd:attribute name="ISBN" type="xsd:string" />
  <xsd:complexType name="authorName">
     <xsd:element name="first-name" type="xsd:string" />
     <xsd:element name="last-name" type="xsd:string" />

A new metadata entity is created for myCollection in which the XML schema is registered. A new row can be added to the table named XmlCatalog (for the table definition, see Example: typed XML column in table earlier in this document) as follows:

INSERT XmlCatalog VALUES(1, '<?xml version="1.0"?>
<bookstore xmlns="http://myBooks">
  <book genre="autobiography" publicationdate="1981" 
    <title>The Autobiography of Benjamin Franklin</title>
  <book genre="novel" publicationdate="1967" 
    <title>The Confidence Man</title>
  <book genre="philosophy" publicationdate="1991" 
    <title>The Gorgias</title>

Modifying XML Schema Collections

The ALTER XML SCHEMA COLLECTION statement supports extending an XML schema in an XML schema collection with new top-level schema components, and registering new XML schemas with the XML schema collection. This is illustrated in the following example.

Example: Altering an XML Schema Collection

The following statement shows how a new XML schema with target namespace http://myDVD can be added to the XML schema collection myCollection.

'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
  <xsd:element name="dvdstore" type="dvdstoreType" />
  <xsd:complexType name="dvdstoreType">
   <xsd:sequence maxOccurs="unbounded">
     <xsd:element name="dvd" type="dvdType" />
  <xsd:complexType name="dvdType">
     <xsd:element name="title" type="xsd:string" />
     <xsd:element name="price" type="xsd:decimal" />
   <xsd:attribute name="genre" type="xsd:string" />
   <xsd:attribute name="releasedate" type="xsd:string" />

Catalog Views for XML Schema Collections

SQL catalog views of XML schema collections allow users to reconstruct the contents of individual XML schema namespaces. XML schema collections are enumerated in the catalog view sys.xml_schema_collections. The XML schema collection "sys" is defined by system and contains predefined namespaces that can be used in all user-defined XML schema collections without having to load them explicitly. This list contains the namespaces for xml, xs, xsi, fn, and xdt.

Two other catalog views worth mentioning are sys.xml_schema_namespaces, which enumerates all namespaces within each XML schema collection; and sys.xml_schema_components, which enumerates all XML schema components within each XML schema.

The built-in function XML_SCHEMA_NAMESPACE() accepts the names of a relational schema, an XML schema collection, and optionally the target namespace of an XML schema. It returns an XML data type instance containing the XML schema. If the target namespace argument is left out, the built-in function returns an XML instance that contains all the XML schemas in the XML schema collection, except for the predefined XML schemas.

Example: Enumerate XML Namespaces in XML Schema Collection

Use the following query for the XML schema collection myCollection.

FROM    sys.xml_schema_collections XSC 
    JOIN sys.xml_schema_namespaces XSN ON 
        (XSC.xml_collection_id = XSN.xml_collection_id)
WHERE    XSC.name = 'myCollection'

Example: Output a Specified XML Schema from an XML Schema Collection

The following statement outputs the XML schema with target namespace http://myBooks from the XML schema collection myCollection within the (relational) schema dbo.

SELECT XML_SCHEMA_NAMESPACE (N'dbo', N'myCollection', 

Access Control on XML Schema Collections

XML schema collections can be secured like any SQL object by using the security model in SQL Server 2005. You can grant a principal the privilege to create XML schema collections within a database. Each XML schema collection supports the permissions ALTER, CONTROL, TAKE OWNERSHIP, REFERENCES, EXECUTE, and VIEW DEFINITION.

  • The ALTER permission is required to execute an ALTER XML SCHEMA COLLECTION statement.
  • The TAKE OWNERSHIP permission is required to transfer ownership of the XML schema collection from one principal to another by executing an ALTER AUTHORIZATION statement.
  • The REFERENCES permission authorizes the principal to use the XML schema collection wherever schema binding is required, such as to type or constraint XML columns and parameters.
  • The EXECUTE permission is required to validate values inserted or updated by the principal against the XML schema collection. It is also required to query values from a typed XML column, variable, or parameter using the XML data type.
  • The VIEW DEFINITION permission allows the principal to access rows in catalog views corresponding to the XML schema collection, all XML schemas contained in it, and all schema components contained in those XML schemas.
  • The CONTROL permission gives the principal the permission to perform any operation on the XML schema collection, including dropping the XML schema collection by using the DROP XML SCHEMA COLLECTION statement. It implies other permissions on the XML schema collection.

Permission on an XML schema collection is required in addition to other permissions on a table or an XML column. For example, to create a table T with an XML column X typed according to an XML schema collection C, the principal requires permission to create tables and REFERENCES permission on the XML schema collection C. A principal with permission to insert data into column X can do so provided the principal has EXECUTE permission on the XML schema collection C. Similarly, a principal requires SELECT permission on column X and EXECUTE permission on C to query the data in column X using XML data type methods. However, SELECT permission on X is adequate to retrieve entire XML values from column X, such as in SELECT X FROM T or SELECT * FROM T.

Permissions can be revoked from a principal, and a principal can be denied permissions as allowed by the security model of SQL Server 2005.

Visibility of Catalog Views

A principal having ALTER, TAKE OWNERSHIP, REFERENCES, VIEW DEFINITION, or CONTROL permission on an XML schema collection can access catalog view rows for the XML schema collection, its contained XML schemas, and their XML schema components. With any of these permissions, the principal can also access the contents of the XML schema collection by using the built-in function XML_SCHEMA_NAMESPACE() and in FOR XML… XMLSCHEMA.

If the principle is denied VIEW DEFINITION permission, the principal cannot access the XML schema collection in catalog views, or using XML_SCHEMA_NAMESPACE() or FOR XML… XMLSCHEMA.

Enhancements to FOR XML

The TYPE directive generates an XML data type instance that can be assigned to an XML column, variable, or parameter, or queried using XML data type methods. This allows the nesting of SELECT… FOR XML TYPE statements.

The PATH mode allows users to specify the path in the XML tree where a column's value should appear and—together with the aforementioned nesting—is more convenient to write than FOR XML EXPLICIT. However, it may not perform as well for deep hierarchies.

The directive XSINIL used in conjunction with ELEMENTS maps NULL to an element with the attribute xsi:nil="true". The new ROOT directive allows a root node to be specified in all modes of FOR XML. The new XMLSCHEMA directive generates an XSD inline schema.

Performance Guidelines

The XML data model is richer and more complex than the relational one. Not only does the XML data model allow you to model complex data, but it also must preserve hierarchical relationships and document order within the data. Document order is maintained by sorting based on XML node identifiers; this simultaneously maintains hierarchical relationships. These contribute to a more complex query plan.

Structured data should be stored in relational columns of tables for better performance. Choose the XML data model for modeling needs when your data is semi-structured, or unstructured, and contains XML markup but not with the expectation of better performance. XML schemas aid in query optimization.

XML Support in SQL Server CLR

Using the SQL Server CLR (common language runtime) support, you can write server-side logic in managed code to enforce business rules. This business logic can be added to XML data in several ways:

  • You can write SQLCLR functions in managed code to which you pass XML values, and use XML processing capabilities provided by System.Xml namespace. An example is to apply an XSL transformation to XML data, as shown in the next code example. Alternatively, you can deserialize the XML into one or more managed classes and operate on them using managed code.
  • You can write Transact-SQL stored procedures and functions that invoke processing on the XML column for your business needs.

Example: Applying an XSL Transformation

Consider a CLR function TransformXml.ApplyXslTransform() that accepts an XML data type instance and the file path for an XSL transformation, applies the transformation to the XML data, and returns the transformed XML in the result. A skeleton function written in Microsoft Visual C# is as follows:

using System;
using System.Data.SqlTypes;
using System.Xml;
using System.Xml.XPath;
using System.Xml.Xsl;

public class TransformXml
  public static SqlXml ApplyXslTransform (SqlXml XmlData,
  string xslPath){
   // Load XSL transformation
      XslCompiledTransform xform = new XslCompiledTransform();
      xform.Load (xslPath);

   System.IO.MemoryStream ms = new System.IO.MemoryStream ();
      xform.Transform (XmlData.CreateReader(), null, ms);
      ms.Seek (0, System.IO.SeekOrigin.Begin);
// Return the transformed value
SqlXml retSqlXml = new SqlXml(ms);
      return (retSqlXml);

The transformed result is written into the in-memory stream ms. The stream pointer is reset using the ms.Seek() call before passing ms to the SqlXml class constructor.

The assembly must be registered in the database by using the CREATE ASSEMBLY statement. A user-defined Transact-SQL function SqlXslTransform() corresponding to ApplyTransformXml() must be created by using the statement CREATE FUNCTION. For more information about these statements, see SQL Server 2005 Books Online. Then the SQL function can be invoked from Transact-SQL as in the following query.

SELECT SqlXslTransform (xCol, 'C:\temp\xsltransform.xsl')
FROM docs
WHERE  xCol.exist('/book/title/text()[contains(.,"secure")]') =1

The query result contains a rowset of the transformed XML.

SQLCLR opens up a whole new world that can be used for decomposing XML data into tables or property promotion, and querying XML data using managed classes in the System.Xml namespace. For more information, see SQL Server 2005 Books Online and Visual Studio 2005 Books Online.

Client-Side XML Processing in SQL Server 2005

Client-Side Support for the XML Data Type

Client access to the XML data type is provided by using ADO.NET, SQL native client (SQLNCLI), and SOAP over HTTP. The first two are discussed below; for SOAP access, see SQL Server Books Online.

ADO.NET XML Support in the .NET Framework V2.0

The XML data type is exposed as a class SqlXml in the System.Data.SqlTypes namespace from the SqlDataReader.GetSqlXml() method. You can obtain XmlReader from the SqlXml object by using the SqlXml.CreateReader() function.

The three-part name of the XML schema collection typing an XML column can be obtained from the XML column metadata (by using GetSchemaTable() or GetSqlMetaData (int) on the SqlDataReader object) as three properties indicating the names of the database (XmlSchemaCollectionDatabase), relational schema (XmlSchemaCollectionOwingSchema), and the XML schema collection (XmlSchemaCollectionName).

A new schema rowset named XMLSCHEMA is available for clients to retrieve XML schemas from the server. The XMLSCHEMA rowset contains three columns for an XML schema collection, target namespace, and the XML schema content itself.

The following examples show skeletal code for managed access to XML data type.

Example: In-Process Access to XML Data Type

The following Visual C# code illustrates how the XML data type can be accessed from the in-process provider. The context connection in the example allows you to execute Transact-SQL statements in the same context that the CLR code was invoked. For out-of-process access, a new connection to the database must be established.

using System;
using System.Xml;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
class xmldtADONETReadAccessInProc
   static void ReadXmlDataType () {
      // in-proc connection to server
      SqlConnection conn = 
         new SqlConnection("context connection=true");

      // prepare query to select xml data
      SqlCommand cmd = conn.CreateCommand();
      cmd.CommandText = 
"SELECT xCol.query('//section') FROM docs";

      // execute query and retrieve incoming data
      SqlDataReader r = cmd.ExecuteReader();

      // access XML data type field in rowset
      SqlXml xml = r.GetSqlXml(0);
      new XmlTextWriter(Console.Out).WriteNode( 
xml.CreateReader(), true);

Example: Accessing the Result of FOR XML TYPE

FOR XML with the TYPE directive yields an XML data type instance that a managed client can retrieve by using the SqlXml class. Thus, the code in the previous example remains the same when the cmd.CommandText is modified as follows:

cmd.CommandText = 
   "SELECT xCol.query('//section') FROM docs FOR XML AUTO, TYPE";

Example: Updating XML Data Type Column Using SQL Client Provider

The following code segment shows a method WriteXmlDataType() that replaces the value in an XML column using the SQL client provider. The code for the in-process provider is similar.

using System;
using System.Xml;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;

class xmldtADONETUpdateAccess
   static void WriteXmlDataType () {
      // connection to server
      SqlConnection conn = new SqlConnection("server=server1;" +
" database=XMLtest; Integrated Security=SSPI");
   // update XML column at the server
   SqlCommand cmd = conn.CreateCommand();
   cmd.CommandText = "UPDATE docs SET xCol=@x WHERE id=1";
// set value of XML parameter
   SqlParameter p = cmd.Parameters.Add("@x", SqlDbType.Xml);
p.Value = new SqlXml(new XmlTextReader("<hello/>", 
XmlNodeType.Document, null));

// execute update and close connection

SQL Native Client Access

In the OLE DB provider of the new SQL native access (SQLNCLI), an XML data type column can be retrieved as Unicode text (DBTYPE_XML, DBTYPE_BYTES, DBTYPE_BSTR, DBTYPE_WSTR, and DBTYPE_VARIANT) or as a Unicode character stream (DBTYPE_UNKNOWN) by using ISequentialStream. The default is DBTYPE_XML. XML data can be sent to the server in UTF-16 encoding, in which case the application must ensure that it is already UTF-16 encoded. The first byte must be a byte-order mark 0xFFFE. An application can send XML data to the server in some other encoding, provided the encoding is compatible with that of the database server. Encoding specified within the XML data is honored. XML should be sent as binary data in all other cases.

The three-part XML schema collection name is carried in three new columns of COLUMNS schema rowset returned by IDBSchemaRowset::GetRowset(): SS_XML_SCHEMACOLLECTION_CATALOGNAME gives the name of the catalog; SS_XML_ SCHEMACOLLECTION SCHEMANAME, the name of the relational schema in which the XML schema collection resides; and SS_XML_SCHEMACOLLECTIONNAME, the name of the XML schema collection. These names are of type DBTYPE_WSTR. These columns have NULL values for untyped XML column for both data retrieval and update.

Similar changes have been made to PROCEDURE_PARAMETERS schema rowset and IColumnRowset:GetColumnRowset().

To retrieve the contents of the XML schema collection, the client can make a separate access to the server by using these names in a call to XML_SCHEMA_NAMESPACE() and get back XML schemas as XML data type. Alternatively, IDBSchemaRowset with the new SS_XMLSCHEMA schema rowset returns catalog name, relational schema name, XML schema collection name, target namespace, and XML schema.

For ODBC access using SQLNCLI, the XML data type is mapped to Unicode variable-length character data called SQL_SS_XML. The 3-part XML schema collection name is surfaced through SQLColAttribute for the XML column as CharacterAttributePtr. These field identifiers are SQL_CA_SS_XML_SCHEMACOLLECTION_CATALOG_NAME, SQL_CA_SS _XML_SCHEMACOLLECTION_SCHEMA_NAME, and SQL_CA_SS _XML_SCHEMACOLLECTION_NAME for the names of the database, relational schema, and XML schema collection, respectively.

Users must install the database server or client tools of SQL Server 2005 to get the SQL native client.

XML data from SQL Server 2005 is available to MDAC APIs by using SQLOLEDB as DBTYPE_WSTR by default. XML columns cannot be returned as the result of a user-defined function to clients that are running versions of SQL Server previous to SQL Server 2005. If this is attempted, an error is returned.

SQLXML - Mapping Between XML and Relational Schemas

You can create logical XML views of your relational data by using SQLXML mapping technology. An XML view, also referred to as a mapping or annotated schema, is created by adding special annotations to a given XSD schema. Other SQLXML technologies can then use this annotated schema to transform queries and updates against the logical XML view to queries and updates against relational tables:

  • When the XML view is combined with an XPath query, SQLXML generates a FOR XML query to find the requested data and shape it as specified in the schema.
  • SQLXML updategrams represent changes to an XML instance that, when combined with an annotated schema, persist these changes back to relational changes by using optimistic concurrency to ensure that the proper data is updated.
  • SQLXML bulkload uses a XML view to "shred" XML data into relational tables.

More information about any of these topics can be found in the SQLXML documentation. This is available on MSDN or by downloading SQLXML from the MSDN SQLXML site.

Creating an XML view of Relational Tables

To create an XML view of the database, you begin with an XSD Schema for your XML data. The rows in the database table/view map to complex-type elements in the schema. The column values in the database map to attributes or simple-type elements.

By default, if no explicit annotations are given, SQLXML assumes that complex-type elements map to tables and simple-type elements, and attributes map to columns. This only works if the names of the elements and attributes are exactly the same as the names of your tables and columns in your database.

If the name of an element/attribute is not the same name as the table (view) or column name to which it maps, an explicit mapping must be created. The following annotations are used to specify the mapping between an element or attribute in an XML document and the table (view) or column in a database.

  • sql:relation—Maps an XML element to a database table.
  • sql:field—Maps an element or attribute to a database column.

Mapping Relationships to Create Hierarchies in XML Views

In your database, tables may be related by foreign key relationships. In XML, these relationships are represented by a nested hierarchy of the elements. In order to construct a proper nesting in your mapping, you must specify how the elements are related. You can establish these relationships among mapping schema elements by using the sql:relationship annotation. Within this annotation, you can specify the parent and child tables as well as which columns in each table should be used to perform the join. SQLXML uses this information to construct the proper nesting hierarchy for your mapping.

Using Overflow to Store Unconsumed Data

Mapping works when your XML data has a regular structure. However, there may be some data in your XML that is unstructured or some data that does not map to a specific column. To store this data and later retrieve it, the sql:overflow annotation can be used. The sql:overflow annotation specifies the column into which all unconsumed data is stored and from where it is retrieved when querying.

The overflow column also allows for expansion of your XML without having to add to your database. Elements and attributes can be added at any time to your XML structure without adding columns to store them in your database. They will simply be stored in the overflow field and retrieved at the proper time.

For More Information

For more information about creating XML views and to see examples of mappings, see Creating XML Views by Using Annotated XSD Schemas on MSDN.

Querying the XML View by Using XPath

Once you've created an XML view of your database, you can query that view as if it were an actual XML document by using the XPath query language. SQLXML supports a subset of the XPath 1.0 query language. When an XPath query is issued against a mapping, SQLXML composes them together and creates a FOR XML EXPLICIT statement that is sent to SQL Server. The proper data is retrieved and then shaped according to the mapping.

For details on the subset of XPath supported over XML views, see the SQLXML documentation. This is available on MSDN or by downloading SQLXML from the MSDN SQLXML page.

Updating Through XML Views Using Updategrams

You can modify (insert, update, or delete) a database in SQL Server through an XML view by using an updategram against an XML view of your database.

The Structure of an Updategram

An updategram is an XML document with <sync>, <before>, and <after> elements that form the syntax of the updategram. Each <sync> block contains one or more <before> and <after> blocks. The <before> block identifies the existing state (also referred to as "the before state") of the record instance. The <after> block identifies the new state to which data is to be changed. Whether an updategram deletes, inserts, or updates a record instance depends on the contents of the <before> and <after> blocks.

Insert Operations

An updategram indicates an insert operation when a record instance appears in the <after> block, but not in the corresponding <before> block. In this case, the updategram inserts the record in the <after> block into the database.

Delete Operations

An updategram indicates a delete operation when a record instance appears in the <before> block with no corresponding records in the <after> block. In this case, the updategram deletes the record in the <before> block from the database.

If an element that is specified in the updategram either matches more than one row in the table or does not match any table row, the updategram returns an error and cancels the entire <sync> block. Only one record at a time can be deleted by an element in the updategram.

Update Operations

When you are updating existing data, you must specify both the <before> and <after> blocks. The updategram uses the elements that are specified in the <before> block to identify the existing records in the database. The corresponding elements in the <after> block indicate how the records should look after the update operation is executed.

An element in the <before> block must match only one table row in the database. If the element either matches multiple table rows or does not match any table row, the updategram returns an error and cancels the entire <sync> block.

For More Information

For more information about creating and using updategrams to modify data through your XML views, see Using Updategrams to Modify Data on MSDN.

Bulk Loading XML Data Through the XML View

XML Bulk Load is a COM object that allows you to load XML data into SQL Server tables. You could insert XML data into a SQL Server database by using an INSERT statement and the OPENXML function; however, the bulk load utility provides higher performance when you need to insert large amounts of XML data. XML Bulk Load interprets the mapping schema and identifies the tables into which the XML data is to be inserted. It then shreds your XML data into your relational tables.

Because the source XML document can be large, the entire document is not read into memory for bulk load processing. Instead, XML Bulk Load interprets the XML data as a stream and interprets it reads it. As the utility reads the data, it identifies the database table, generates the appropriate record from the XML data source, and then sends the record to SQL Server for insertion.

For details about how XML Bulk Load works and how to use it, see Performing Bulk Load of XML Data on MSDN.

SQLXML Data Access Methods

Since SQL Server 2000, two new ways to access SQLXML functionality have been added:

  • SQLXML Managed Classes
  • SQLXML Web Services

In addition, HTTP access to SQL Server has been enhanced to provide support for updategrams within templates.

SQLXML Managed Classes

SQLXML Managed Classes expose the functionality of SQLXML 3.0 inside the Microsoft .NET Framework. With SQLXML Managed Classes, you can write a C# application to access XML data from an instance of SQL Server, bring the data into the .NET Framework environment, process the data, and send the updates back to SQL Server to apply the updates.

For more details on how to use SQLXML managed classes, see SQLXML .NET Support on MSDN.


This article describes complementary technologies for XML in SQL Server 2005. Server-side features include a native implementation for XML storage, indexing, and query processing. Existing features such as FOR XML and OpenXML have also been enhanced. Client-side support consists of enhancements to ADO.NET to support the XML data type and in the System.Xml to support XQuery for querying differing sources of XML. In addition, the SQLXML mapping technology Web release enhancements have been incorporated into SQL Server 2005.

The server-side and the client-side support are useful in different scenarios. The XML data type provides a simple mechanism for storing XML data by inserting XML data into an untyped XML column. Using XML schemas to define typed XML helps the database engine optimize storage and query processing in addition to providing data validation.

The XML data type preserves document order and is useful for applications such as document management applications. It can also handle recursive XML schemas. The relational data model is still the best choice for structured data with a known schema. Even [n]varchar(max) is suitable for scenarios where fine-grained query and update are unimportant.

The SQLXML mapping technology is useful whenever you want to use an XML-centric programming model over relational data stored in tables at the server. The mapping is based on defining an XML schema as an XML view. The mapping can be used for bulk loading XML data into tables and for querying the tables by using XPath 1.0. Document order is not preserved, so the mapping technology is useful for XML data processing as opposed to XML document processing.

The core XML classes in System.Xml in the .NET Framework 2.0 release enable you to read, write, manipulate, and transform XML. With improvements in performance, usability, typing, and querying, the XML support in the 2.0 release continues to lead the industry in innovation, standards support, and ease of use.

The server- and client-side technologies complement one another. The mapping technology can leverage server-side features to augment features such as maintenance of document order and recursive schemas, and find more areas of applications. On the other hand, the CLR brings extensibility and the wealth of existing XML tools such as XSLT transformation to the XML data type. The XQuery implementation in the server is aligned with the July 2004 draft of the XQuery specification. The XML schema implementations at the server and the client are aligned with each other.


XML Best Practices for Microsoft SQL Server 2005

Indexing XML Data Stored in a Relational Database

XML Options in Microsoft SQL Server 2005

What's New in FOR XML in Microsoft SQL Server 2005


About the authors

Shankar Pal is Program Manager in SQL Server engine working on server-side XML technologies. His blog is http://blogs.msdn.com/spal.

Mark Fussell is Lead Program Manager in the Microsoft XML Messaging team. He has worked on data access technologies including the components within the System.Xml and System.Data namespaces of the .NET Framework, Microsoft XML Core Services (MSXML), and Microsoft Data Access Components (MDAC). His blog is http://weblogs.asp.net/mfussell.

Irwin Dolobowsky is Program Manager in the Microsoft MSN team.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
© 2014 Microsoft. All rights reserved.