The Native XML Data Type

Without a doubt the most important XML related enhancement that Microsoft has added to SQL Server 2005 is support for a new native XML data type. The XML data type, literally named XML can be used as a column in a table or a variable or parameter in a stored procedure. It can be used to store both typed and untyped data. If the data stored in an XML column has no XSD schema, then it is considered untyped. If there is an associated XSD schema, then SQL Server 2005 will check the schema to make sure that the data store complies with the schema definition. In all cases, SQL Server 2005 checks data that is stored in the XML data type to ensure that the XML document is well formed. If the data is not well formed, SQL Server 2005 will raise an error and the data will not be stored. The XML data type can accept a maximum of 2GB of data and is stored like the varbinary(max) data type. The following listing illustrates creating a simple table that uses the new XML data type for one of its columns.

CREATE TABLE MyXMLDocs   (DocID INT PRIMARY KEY IDENTITY,    MyXmlDoc XML)

The most important thing to note in this example is the definition of the MyXmlDoc column, which uses the data type of XML to specify that the column will store XML data. You can store XML data into an XML column using the standard T-SQL INSERT statement. The following example shows how you can populate an XML column using a simple INSERT statement:

INSERT INTO MyXmlDocs Values ('<MyXMLDoc>     <DocumentID>1</DocumentID>     <DocumentText>Text</DocumentText> </MyXMLDoc>')

Note 

One important point to notice here is that because the XML data is untyped, any valid XML document can be inserted into the XML data type.

Strongly Typed XML Data Types

The native XML data type checks to ensure that any data that’s stored in an XML variable or column is a valid XML document. On its own, it doesn’t check any more than that. However, Microsoft designed the XML data type to be able to support more sophisticated document validation using an XSD schema. When an XSD schema is defined for an XML data type column, the SQL Server engine will check to make sure that all of the data that is stored in the XML column complies with the definition that’s contained in the XSD schema.

The following listing shows a sample XSD schema for the simple XML document that was used in the preceding example:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" targetNamespace="MyXMLDocSchema" xmlns="MyXMLDocSchema">   <xs:element name="MyXMLDoc">     <xs:complexType>       <xs:sequence>         <xs:element name="DocumentID" type="xs:string" />         <xs:element name="DocumentBody" type="xs:string" />       </xs:sequence>     </xs:complexType>   </xs:element> </xs:schema>

This XSD schema uses the namespace of MyXMLDocSchema and defines an XML document that has a complex element named MyXMLDoc. The MyXMLDoc complex element contains two simple elements. The first simple element must be named DocumentID, and a second simple element is named DocumentBody. Both elements contain XML string-type data.

To create a strongly typed XML column or variable, you first need to register the XSD schema with SQL Server using the CREATE XMLSCHEMA T-SQL DDL statement. The following listing shows how you combine the CREATE XML SCHEMA COLLECTION statement with the sample MyXMLDocSchema to register the schema with the SQL Server 2005 database:

CREATE XML SCHEMA COLLECTION MyXMLDocSchema AS N'<?xml version="1.0"?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"  elementFormDefault="qualified" targetNamespace="http://MyXMLDocSchema">   <xs:element name="MyXMLDoc">     <xs:complexType>       <xs:sequence>         <xs:element name="DocumentID" type="xs:string" />         <xs:element name="DocumentBody" type="xs:string" />       </xs:sequence>     </xs:complexType>   </xs:element> </xs:schema>' 

The CREATE XML SCHEMA COLLECTION DDL statement takes a single argument that names the collection. Next, after the AS clause it expects a valid XSD schema enclosed in single quotes. If the schema is not valid, an error will be issued when the statement is executed. The CREATE XML SCHEMA COLLECTION statement is database specific, and the schema that is registered can be accessed only in the database for which the schema is registered.

Once you’ve registered the XML schema with SQL Server 2005, you can go ahead and associate XML variables and columns with that schema. Doing so ensures that any XML documents that are contained in those variables or columns will adhere to the definition provided by the associated schema. The following example illustrates how you can create a table that uses a strongly typed XML column:

CREATE TABLE MyXMLDocs   (DocID INT PRIMARY KEY IDENTITY,    MyXmlDoc XML(MyXMLDocSchema))

Here you can see that the MyXMLDocs table is created using the CREATE TABLE statement much as in the preceding example. In this case, however, the MyXMLDoc column is created using an argument that specifies that name of the registered XSD schema definition. If you refer to the earlier listing, you can see that the schema was registered using the name MyXMLDocSchema. After the MyXMLDoc column has been associated with the schema that was registered, any data that’s inserted into this column will be strongly typed according to the schema definition and any attempt to insert data that doesn’t match the schema definition will be rejected. The following listing illustrates an INSERT statement that can add data to the strongly typed MyXMLDoc column:

INSERT INTO MyXMLDocs Values   ('<MyXMLDoc xmlns="http://MyXMLDocSchema">        <DocumentID>1</DocumentID>        <DocumentBody>"My text"</DocumentBody>        </MyXMLDoc>')

Note 

Because this example uses a typed XML data type, the data must conform to the definition provided by the associated XSD schema.

In this case, the XML document must reference the associated XML namespace http://MyXMLDocSchema. And the XML document must contain a complex element named MyXMLDoc, which in turn contains the DocumentID and DocumentBody elements. The SQL Server engine will reject any attempt to insert any other XML documents into the MyXMLDocs column. If the data does not conform to the supplied XSD schema, SQL Server will return an error message like the one shown in the following listing:

Msg 6965, Level 16, State 1, Line 1 XML Validation: Invalid content,expected element(s):MyXMLDocSchema:DocumentID where element 'MyXMLDocSchema:Do' was specified

Note 

As you might expect from their dependent relationship, if you assign a schema to a column in a table, that table must be altered or dropped before that schema definition can be updated.

Retrieving a Registered XML Schema

Once you import a schema using CREATE XML SCHEMA COLLECTION, the schema components are stored in SQL Server’s metadata. The stored schema can be listed by querying the sys.xml_namespaces system view, as you can see in the following example:

SELECT * FROM sys.xml_namespaces

This statement will return a result set showing all of the registered schemas in a database like the one that follows:

xml_collection_id name                               xml_namespace_id ----------------- ---------------------------------- ---------------- 1                 http://www.w3.org/2001/XMLSchema   1 65540             http://MyXMLDocSchema              1 (2 row(s) affected)

You can also use the new XML_SCHEMA_NAMESPACE function to retrieve the XML schema. The following query retrieves a schema from the database for a given namespace.

  SELECT XML_SCHEMA_NAMESPACE(N'dbo',N'MyXMLDocSchema')

This statement will return a result set showing all of the columns that use the registered schema, as you can see in the following listing:

---------------------------------------------------------------------- <xsd:schema xmlns:xsd=http://www.w3.org/2001/XMLSchema targetNamespace="http://MyXMLDocSchema" xmlns:t=http://MyXMLDocSchema elementFormDefault="qualified"><xsd:elementname="MyXMLDoc"> <xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:any (1 row(s) affected) 

The elementname attribute lists the columns that use the typed XML data type.

XML Data Type Methods

SQL Server 2005 provides several new built-in methods that work much like user- defined types for working with the XML data type. These methods enable you to drill down into the content of XML documents that are stored using the XML data type. On its own, just being able to store XML data in SQL Server has limited value. To really facilitate deep XML integration, you also need a way to query and manipulated the data that’s stored using the new XML data type, and that’s just what the following XML data type methods enable.

Exists(XQuery, [node ref])

The XML data type’s Exists method enables you to check the contents of an XML document for the existence of elements or attributes using an XQuery expression. (More information about the XQuery language is presented in the next section.) The following listing shows how to use the XML data type’s Exists method:

SELECT * FROM MyXMLDocs WHERE MyXmlDoc.exist('declare namespace xd=http://MyXMLDocSchema /xd:MyXMLDoc[xd:DocumentID eq "1"]') = 1

The first parameter of the XML Exists method is required and takes an XQuery expression. The second parameter is optional and specifies a node reference within the XML document. Here the XQuery tests for a DocumentID element equal to a value of 1. A namespace is declared because the MyXMLDoc column has an associated schema. The Exists method can return the value of TRUE (1) if the XQuery expressions returns a node, FALSE (0) if the expression doesn’t return an XML node, or NULL if the XML data type instance is null. You can see the results of the XML Exists method here:

DocID       MyXmlDoc ----------- --------------------------------------------------------- 1           <MyXMLDocxmlns="http://MyXMLDocSchema"><DocumentID>1 </DocumentID> <DocumentBody>"My text"</DocumentBody></MyXMLDoc> (1 row(s) affected) 

Modify(XML DML)

As you might guess, the XML data type’s Modify method enables you to modify a stored XML document. You can use the Modify method either to update the entire XML document or to update just a selected part of the document. You can see an example of using the Modify method in the following listing:

UPDATE MyXMLDocs SET MyXMLDoc.modify('declare namespace xd=http://MyXMLDocSchema   replace value of  (/xd:MyXMLDoc/xd:DocumentBody)[1] with "My New Body"') WHERE DocID = 1

The XML data type’s Modify method uses an XML Data Modification Language (XML DML) statement as its parameter. XML DML is a Microsoft extension to the XQuery language that enables modification of XML documents. The XQuery dialect supports the Replace value of, Insert, and Delete XML DML statements. In this example, since the MyXMLDoc XML column is typed, the XML DML statement must specify the namespace for the schema. Next, you can see where the Replace value of XML DML command is used to replace the value of the DocumentBody element with the new value of “My New Body” for the row where the relational DocID column is equal to one. The replace value of clause must identify only a single node, or it will fail. Therefore the first node is identified using the [1] notation.

Note 

While this example illustrates performing a replace operation, the Modify method also supports insert and delete operations.

Query(XQuery, [node ref])

The XML data type’s Query method can retrieve either the entire contents of an XML document or a selected section of the XML document. You can see an example of using the Query method in the following listing:

SELECT DocID, MyXMLDoc.query('declare namespace xd=http://MyXMLDocSchema   /xd:MyXMLDoc/xd:DocumentBody') AS Body FROM MyXMLDocs

This XQuery expression returns the values from the XML document’s DocumentBody element. Again, the namespace is specified because the MyXMLDoc Data type has an associated schema named MyXMLDocSchema. In this example, you can see how SQL Server 2005 easily integrates relational column data with XML data. Here, DocID comes from a relational column, while the DocumentBody element is queried out of the XML column. The following listing shows the results of the XQuery:

DocID  Body ------ -------------------------------------------------------------------------------- 1      <xd:DocumentBody xmlns:xd="http://MyXMLDocSchema">My New Body</xd:DocumentBody> 2      <xd:DocumentBody xmlns:xd="http://MyXMLDocSchema">"My 2nd text"</xd:DocumentBody> (2 row(s) affected)

Value(XQuery, [node ref])

The Value method enables the extraction of scalar values from an XML data type. You can see an example of how the XML data type’s Value method is used in the following listing:

SELECT MyXMLDoc.value('declare namespace xd=http://MyXMLDocSchema      (/xd:MyXMLDoc/xd:DocumentID)[1]', 'int') AS ID FROM MyXMLDocs

Unlike the other XML data type methods, the XML Value method requires two parameters. The first parameter is an XQuery expression, and the second parameter specifies the SQL data type that will hold the scalar value returned by the Value method. This example returns all of the values contained in the DocumentID element and converts them to the int data type, as shown in the following results:

ID ----------- 1 2 (2 row(s) affected)