Lesson 5 XML integration with SQL Server 2005

Introduction

XML has become a key technology in enterprise business solutions. SQL Server 2000 introduced XML-related functionality in the database engine through the FOR XML clause of the SELECT statement and through the OPENXML function.

Microsoft SQL Server 2005 builds on and extends the XML support in SQL Server 2000, making it easier to build solutions that use both relational and XML data. SQL Server 2005 includes several enhancements to this functionality, and this lesson describes these enhancements.

Objectives

After completing this lesson, you will be able to:

Use enhanced XML features in SQL Server 2005.
Use the xml data type for typed and untyped XML.
Use XQuery expressions with XML data.

Enhancements to the FOR XML Clause
Enhancements to the OPENXML Function
Storing XML in the Database
Using Untyped XML
Managing XML Schemas
Using Typed XML
Managing XML Indexes
What Is XQuery?
Check Your Understanding

Enhancements to the FOR XML Clause

Introduction

SQL Server 2000 added the FOR XML clause to the SELECT statement as a means of generating XML from a row set. The FOR XML clause has been extended in SQL Server 2005 to implement sophisticated solutions for scenarios that require the generation of XML from data in a SQL Server database.

ELEMENTS directive in RAW mode queries

In SQL Server 2000, RAW mode FOR XML queries can return only attribute-centric XML. In SQL Server 2005, you can use the ELEMENTS directive with RAW mode queries to return element-centric XML. The following example shows how to use the ELEMENTS directive with RAW mode.

SELECT ProductID, Name, ListPrice 
FROM Production.Product 
FOR XML RAW, ELEMENTS

Support for null elements

When element-centric XML is returned in SQL Server 2000, null columns are omitted in the results. In SQL Server 2005, you can specify that null columns should yield empty elements with the xsi:nil attribute by using the XSINIL option with the ELEMENTS directive in AUTO, RAW, and PATH mode queries or by using the elementxsinil column mode in EXPLICIT mode queries. The following examples show how to retrieve empty elements for null values.

-- Using XSINIL with ELEMENTS 
SELECT ProductID, Name, Color 
FROM Production.Product Product 
FOR XML AUTO, ELEMENTS XSINIL

-- Using elementsxinil in EXPLICIT mode 
SELECT 1 AS Tag, 
       NULL AS Parent, 
       ProductID AS [Product!1!ProductID], 
       Name AS [Product!1!ProductName!element], 
       Color AS [Product!1!Color!elementxsinil] 
FROM Production.Product 
FOR XML EXPLICIT

Inline XSD schemas

In SQL Server 2000, you can return an inline XDR schema. SQL Server 2005 adds the XMLSCHEMA directive, which returns an inline XSD schema. The following example shows how to retrieve an inline XSD schema.

SELECT ProductID, Name, ListPrice 
FROM Production.Product Product 
FOR XML AUTO, XMLSCHEMA

TYPE directive for xml data type

SQL Server 2005 introduces a new data type, xml. Specifying the TYPE directive in a FOR XML query returns the results as an xml value instead of as a varchar string. The most significant impact of this is the ability to nest FOR XML queries to return multilevel XML results in AUTO and RAW mode queries. The following example shows how to use the TYPE directive to nest FOR XML queries.

SELECT ProductID, Name, ListPrice, 
         (SELECT ReviewerName, Comments 
          FROM Production.ProductReview ProductReview 
          WHERE ProductReview.ProductID = Product.ProductID 
          FOR XML AUTO, ELEMENTS, TYPE) 
FROM Production.Product Product 
FOR XML AUTO

PATH mode

SQL Server 2005 introduces PATH mode FOR XML queries. When using PATH mode, you specify column names using an XPath-like syntax to map values to attributes, elements, subelements, text nodes, and data values. This makes it possible to generate complex XML without an EXPLICIT mode query. The following example shows how to use PATH mode.

SELECT ProductID AS "@ProductID", 
       Name AS "*", 
       Size AS "Description/@Size", 
       Color AS "Description/text()" 
FROM Production.Product 
FOR XML PATH

ROOT directive for well-formed results

FOR XML queries return XML fragments, not well-formed XML documents. You can wrap the fragment returned by a FOR XML query in a root element to make it well formed by specifying the ROOT directive with the name you want to assign to the root element. The following example shows how to use the ROOT directive.

SELECT ProductID, Name, ListPrice 
FROM Production.Product Product 
FOR XML AUTO, ROOT('Products')

Enhancements to the OPENXML Function

Introduction

SQL Server 2000 introduced the OPENXML function to provide a way to shred an XML document into a row set. SQL Server 2005 introduces enhancements to the OPENXML function, which will help you to build solutions that generate tabular data from XML.

Documents can be xml data type values

In SQL Server 2000, you can use a varchar, nvarchar, text, or ntext variable to generate a document handle using the sp_xml_preparedocument stored procedure. In SQL Server 2005, you can also use an xml variable, as shown in the following example.

DECLARE @doc xml 
SET @doc = '<?xml version="1.0" ?> 
 <SalesInvoice InvoiceID="1000" CustomerID="123"> 
  <Items> 
    <Item ProductCode="12" Quantity="2" UnitPrice="12.99"/> 
    <Item ProductCode="41" Quantity="1" UnitPrice="17.45"/> 
    <Item ProductCode="2" Quantity="1" UnitPrice="2.99"/> 
  </Items> 
</SalesInvoice>' 
 
DECLARE @docHandle int 
EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc 
 
SELECT * FROM 
OPENXML(@docHandle, 'SalesInvoice/Items/Item', 1) 
WITH 
(ProductCode int, 
 Quantity int, 
 UnitPrice smallmoney) 
 
EXEC sp_xml_removedocument @docHandle

Support for xml data type in the WITH clause

In SQL Server 2005, you can return xml data type columns in the WITH clause of the OPENXML function, as shown in the following example.

SELECT * FROM 
OPENXML(@docHandle, 'SalesInvoice', 1) 
WITH 
(InvoiceID int, 
 CustomerID int, 
 OrderDate datetime, 
 Items xml 'Items')

Batch-level scoping

In SQL Server 2000, the document handle returned by the sp_xml_preparedocument stored procedure is maintained for the duration of the session, even if the variable used to access it has gone out of scope. In SQL Server 2005, the document handle is destroyed at the end of the batch, reducing the impact on server resources.

Storing XML in the Database

Introduction

Support for XML in SQL Server 2000 makes it easy to translate relational data in a database into XML and vice versa. This enables the development of distributed applications that exchange data in XML format but store it in a relational store.

SQL Server 2005 continues to support this paradigm of translating between XML and relational format but also provides support for storing XML data natively in a database through the introduction of the xml data type.

Benefits of storing XML in the database

The ability to store XML natively in a relational database brings many advantages for application developers. The benefits of native XML storage include the following:

Both structured and semi-structured data are stored in a single location, making it easier to manage.
You can define variable content within a relational model.
You can choose the most suitable data model for your application's specific requirements while still taking advantage of a highly optimized data storage and querying environment.

Native XML functionality

The SQL Server 2005 xml data type stores the InfoSet of an XML document in an efficient internal format. The data can be treated as if it were the original XML document with the exception that insignificant white space, order of attributes, namespace prefixes, and the XML declaration is not retained. SQL Server 2005 provides the following functionality for the xml data type:

XML indexing. Columns defined as xml can be indexed using XML indexes and Full-Text indexes. This can significantly enhance the performance of queries that retrieve XML data.
XQuery-based data retrieval methods. The xml data type provides the query, value, and exist methods. These can be used to extract data from the XML data using an XQuery expression.
XQuery-based data modification. The xml data type provides the modify method, which uses an extension of the XQuery specification to perform updates to the XML data.

Typed vs. untyped XML

The xml data type supports both typed and untyped XML. Typed and untyped XML are compared in the following table:

XML format	Description
Typed	XML that is associated with an XML schema. The schema defines the elements and attributes that are valid in an XML document of this type and specifies a namespace for them. When the xml data type is used to store typed XML, SQL Server validates the XML against the schema and optimizes the internal storage of the data by assigning appropriate SQL Server data types to the data based on the XML data types defined in the schema.
Untyped	XML that has no schema. When the xml data type is used to store untyped XML, SQL Server does not validate the data. It does,however, ensure that the XML is a well-formed document or fragment.

Using Untyped XML

Introduction

You can use the xml data type to store untyped XML when you need to store XML data that you do not want to validate by using a schema. It is important to know how to assign an untyped XML value to the xml data type so that you can build applications that store untyped XML data.

Defining xml columns and variables

You define an xml column or variable for untyped XML in the same way that you define a column or variable for any other SQL Server data type. To define a column, specify the xml data type in the CREATE TABLE statement, as shown in the following example.

CREATE TABLE Sales.Invoices 
(InvoiceID int, 
 SalesDate datetime, 
 CustomerID int, 
 ItemList xml)

To define an untyped xml variable, specify the xml data type in the DECLARE statement as shown in the following example.

DECLARE @itemDoc xml

Implicitly casting string values

The simplest way to assign an untyped XML value to an xml variable or column is to assign any string (varchar, nvarchar, text, or ntext) value that contains a well-formed XML document or fragment. SQL Server will automatically cast the value to the xml data type.

The following example shows how to use implicit casting to assign an untyped XML value.

DECLARE @itemString nvarchar(2000) 
SET @itemString = '<Items> 
                     <Item ProductID="2" Quantity="3"/> 
                     <Item ProductID="4" Quantity="1"/> 
                   </Items>' 
 
DECLARE @itemDoc xml 
SET @itemDoc = @itemString 
 
INSERT INTO Sales.Invoices 
VALUES 
(1, GetDate(), 2, @itemDoc)

The same principle applies when assigning a constant string expression to an xml data type, as shown in the following example.

INSERT INTO Sales.Invoices 
VALUES 
(1, GetDate(), 2, '<Items> 
                           <Item ProductID="2" Quantity="3"/> 
                           <Item ProductID="4" Quantity="1"/> 
                           </Items>')

Explicitly casting a string to xml

You can explicitly cast a string value to xml by using the Transact-SQL CAST function, as shown in the following example.

DECLARE @castedDoc xml 
SET @castedDoc = CAST(@itemString AS xml)

Explicitly converting a string to xml

You can explicitly convert a string value to xml by using the Transact-SQL CONVERT function, as shown in the following example.

DECLARE @convertedDoc xml 
SET @convertedDoc = CONVERT(xml, @itemString)

Using well-formed XML

Regardless of the approach you use to assign a value to an xml column or variable, the value you assign must be well-formed XML, or an error will occur. The value you assign can be a well-formed XML document (with a single root element) or a well-formed XML fragment (containing multiple well-formed elements). The following example shows some examples of well-formed XML and a value that is not well formed.

DECLARE @itemXml xml 
-- Well-formed document. This will succeed. 
SET @itemXml = '<Items> 
                <Item ProductID="2" Quantity="3"/> 
                <Item ProductID="4" Quantity="1"/> 
                </Items>' 
-- Well-formed fragment. This will succeed. 
 SET @itemXml = '<Item ProductID="2" Quantity="3"/> 
                 <Item ProductID="4" Quantity="1"/>' 
-- Not well-formed. This will fail. 
 SET @itemXml = '<Items> 
                 <Item ProductID="2" Quantity="3"/> 
                 <Item ProductID="4" Quantity="1"/>'

Back to School main page

Other Views

Printer Friendly

Lesson 5 XML integration with SQL Server 2005

Introduction

Objectives

Contents

Enhancements to the FOR XML Clause

Introduction

ELEMENTS directive in RAW mode queries

Support for null elements

Inline XSD schemas

TYPE directive for xml data type

PATH mode

ROOT directive for well-formed results

Enhancements to the OPENXML Function

Introduction

Documents can be xml data type values

Support for xml data type in the WITH clause

Batch-level scoping

Storing XML in the Database

Introduction

Benefits of storing XML in the database

Native XML functionality

Typed vs. untyped XML

Using Untyped XML

Introduction

Defining xml columns and variables

Implicitly casting string values

Explicitly casting a string to xml

Explicitly converting a string to xml

Using well-formed XML

Other Views