Problem

You want to store an XML document in your database.

Solution

Store the document in a column with the xml data type.

CREATE TABLE dbo.Book
        (
         BookID INT IDENTITY PRIMARY KEY,
         ISBNNBR CHAR(13) NOT NULL,
         BookNM VARCHAR(250) NOT NULL,
         AuthorID INT NOT NULL,
         ChapterDesc XML NULL
        );
GO

How It Works

Native xml data types can be used as a data type for columns in a table. Data stored in the xml data type can contain an XML document or XML fragments. An XML fragment is an XML instance without a single top-level element for the contents to nest in. Creating an XML data type column is as easy as just using it in the table definition, as shown earlier.

The xml data type can also be used as a parameter to a procedure.

CREATE PROCEDURE dbo.usp_INS_Book
       @ISBNNBR CHAR(13),
       @BookNM VARCHAR(250),
       @AuthorID INT,
       @ChapterDesc XML
AS
INSERT dbo.Book
        (ISBNNBR,
         BookNM,
         AuthorID,
         ChapterDesc)
VALUES (@ISBNNBR,
         @BookNM,
         @AuthorID,
         @ChapterDesc);
GO

And it can be used as a variable in a batch.

DECLARE @Book XML;
SET @Book =
'
<Book name = "SQL Server 2012 T-SQL Recipes">
<Chapters>
<Chapter id = "1" > Getting Started with SELECT</Chapter>
<Chapter id = "2" > Elementary Programming</Chapter>
<Chapter id = "3" > Nulls and Other Pitfalls</Chapter>
<Chapter id = "4" > Combining Data from Multiple Tables</Chapter>
</Chapters>
</Book>
';

In the previous example, the variable is declared and then populated with XML data. The next recipe will show you how to use the XML data in the variable.

Problem

You want to insert XML data into an XML column in a table.

Solution

Utilize the INSERT statement to insert XML data into a column of the xml data type.

INSERT dbo.Book
        (ISBNNBR,
         BookNM,
         AuthorID,
         ChapterDesc)
VALUES ('9781430242000',
         'SOL Server 2012 T-S0L Recipes',
         55,
' < Book name = "SQL Server 2012 T-SQL Recipes">
<Chapters>
<Chapter id = "1" > Getting Started with SELECT</Chapter>
<Chapter id = "2" > Elementary Programming</Chapter>
<Chapter id = "3" > Nulls and Other Pitfalls</Chapter>
<Chapter id = "4" > Combining Data from Multiple Tables</Chapter>
</Chapters>
</Book > '),

How It Works

In this example, data is inserted directly into the table with the INSERT statement. The XML data is passed as a string, which is implicitly converted to the xml data type.

XML data can also be saved into a variable, and the variable can then be used in the INSERT statement.

DECLARE @Book XML;
SET @Book =
CAST(' < Book name = "S0L Server 2012 Fast Answers">
<Chapters>
<Chapter id = "1" > Installation, Upgrades. . . </Chapter>
<Chapter id = "2" > Configuring SQL Server </Chapter>
<Chapter id = "3" > Creating and Configuring Databases </Chapter>
<Chapter id = "4" > SQL Server Agent and SQL Logs </Chapter>
</Chapters>
</Book > ' as XML);
INSERT dbo.Book
        (ISBNNBR,
         BookNM,
         AuthorID,
         ChapterDesc)
VALUES ('1590591615',
         'SOL Server 2012 Fast Answers',
         55,
         @Book);

In this example, the XML data is first explicitly converted to the xml data type with the CAST function and stored in a variable of the xml data type. The variable is then used in the SELECT statement to insert the data into the table.

In either example, when the string XML data is being converted to the xml data type (in the first example when being inserted into the column and in the second when being converted with the CAST function), the XML data is checked to ensure that it is well formed. means that it follows the general rules of an XML document. For example, the following code is not well formed (it is missing the closing </Book > tag):

DECLARE @Book XML;
SET @Book =
CAST(' < Book name = "S0L Server 2000 Fast Answers">
<Chapters>
<Chapter id = "l" > Installation, Upgrades. . . </Chapter>
<Chapter id = "2" > Configuring SQL Server </Chapter>
<Chapter id = "3" > Creating and Configuring Databases </Chapter>
<Chapter id = "4" > SQL Server Agent and SQL Logs </Chapter>
</Chapters>
' as XML);

When executing this code, the following error is generated:

Msg 9400, Level 16, State 1, Line 2
XML parsing: line 8, character 0, unexpected end of input

The XML column in this example is untyped. When an XML column is untyped, it means that the contents inserted into the column are not validated against an XML schema. An XML schema is used to define the allowed elements and attributes for an XML document and is discussed in the next recipe.

Problem

You want to ensure that all the elements and attributes of XML data are verified to be in accordance with an agreed upon standard.

Solution

Utilize an XML schema collection to validate that the elements, attributes, data types, and allowed values are followed in an XML document.

CREATE XML SCHEMA COLLECTION BookStoreCollection
AS
N' < xsd:schema targetNamespace = "http://PROD/BookStore"
         xmlns:xsd = "http://www.w3.org/2001/XMLSchema"
         xmlns:sqltypes = "http://schemas.microsoft.com/sqlserver/2004/sqltypes"
         elementFormDefault = "qualified">
    <xsd:import namespace = "http://schemas.microsoft.com/sqlserver/2004/sqltypes"/>
    <xsd:element name = "Book">
        <xsd:complexType>
           <xsd:sequence>
             <xsd:element name = "BookName" minOccurs = "0">
               <xsd:simpleType>
                 <xsd:restriction base = "sqltypes:varchar">
                   <xsd:maxLength value = "50" />
                 </xsd:restriction>
               </xsd:simpleType>
             </xsd:element>
             <xsd:element name = "ChapterID" type = "sqltypes:int" minOccurs = "0" />
             <xsd:element name = "ChapterNM" minOccurs = "0">
             <xsd:simpleType>
               <xsd:restriction base = "sqltypes:varchar">
                 <xsd:maxLength value = "50" />
                   </xsd:restriction>
                 </xsd:simpleType>
               </xsd:element>
             </xsd:sequence>
           </xsd:complexType>
      </xsd:element>
</xsd:schema > ';
GO

How It Works

This example builds an XML schema (which is also referred to as an XML schema definition, or XSD). An XML schema defines the elements, attributes, data types, and allowed values for the XML document.

The syntax for the CREATE XML SCHEMA COLLECTION statement is as follows:

CREATE XML SCHEMA COLLECTION [ <relational_schema>. ]sql_identifier AS Expression

Table 24-1 describes the arguments.

You can now create a variable that requires that the XML document adheres to this definition.

DECLARE @Book XML (DOCUMENT BookStoreCollection);
SET @Book =
CAST('
<Book xmlns = "http://PROD/BookStore">
    <BookName > "SQL Server 2012 Fast Answers"</BookName>
    <ChapterID > 1</ChapterID>
    <ChapterNM > Installation, Upgrades. . .</ChapterNM>
</Book > ' as XML);
GO

Note that the < Book > tag specifies the xmlns for the default namespace of the XML schema collection. Using the keyword DOCUMENT or CONTENT with the schema collection reference lets you determine whether the allowed XML will allow only a full XML document (DOCUMENT) or XML fragments (CONTENT).

If you attempt to set this variable to XML data that does not adhere to the XML schema, an error is generated.

DECLARE @Book XML (DOCUMENT BookStoreCollection);
SET @Book =
CAST('
<Book xmlns = "http://PROD/BookStore">
    <BookName > "S0L Server 2012 Fast Answers"</BookName>
    <ChapterID > 1</ChapterID>
    <ChapterNM > Installation, Upgrades. . .</ChapterNM>
    <ChapterID > 2</ChapterID>
    <ChapterNM > Configuring SQL Server</ChapterNM>
</Book > ' as XML);
GO

This XML data has extra ChapterID and ChapterNM tags. Executing this code generates the following error:

Msg 6923, Level 16, State 1, Line 2
XML Validation: Unexpected element(s): {http://PROD/BookStore}ChapterID. Location:
/*:Book[1]/*:ChapterID[2]

You can also build a table with a column of the xml data type that is required to adhere to this XML schema.

CREATE TABLE dbo.BookInfoExport
       (
        BookID INT IDENTITY PRIMARY KEY,
        ISBNNBR CHAR(10) NOT NULL,
        BookNM VARCHAR(250) NOT NULL,
        AuthorID INT NOT NULL,
        ChapterDesc XML(BookStoreCollection) NULL
       );

To add additional XML schemas to an existing XML schema collection, you can use the ALTER XML SCHEMA COLLECTION statement. The syntax is as follows:

ALTER XML SCHEMA COLLECTION[ relational_schema. ]sql_identifier ADD 'Schema Component'

To remove the entire XML schema collection from the database, use the DROP XML SCHEMA COLLECTION statement. The syntax is as follows:

DROP XML SCHEMA COLLECTION [ relational_schema. ]sql_identifier

The only argument for dropping an existing XML schema collection is the name of the collection. Prior to dropping an XML schema collection, it cannot be used in any table definitions.

Problem

You need to determine what XML schema collections exist on a database.

Solution

Use the system catalog views XML_schema_collections and XML_schema_namespaces to retrieve information about existing XML schema collections.

SELECT name
FROM sys.XML_schema_collections
ORDER BY create_date;

This query returns the following result set:

name
-------------------
sys
BookStoreCollection

How It Works

The system catalog views XML_schema_collections and XML_schema_namespaces contain information about existing XML schema collections, and they can be queried to return this information. In the previous example, all of the XML schema collections for this database are returned by querying the XML_schema_collections system catalog view. The namespaces used by XML schema collections can be returned with the following query:

SELECT n.name
FROM sys.XML_schema_namespaces n
        INNER JOIN sys.XML_schema_collections c
         ON c.XML_collection_id = n.XML_collection_id
WHERE c.name = 'BookStoreCollection';

This query returns the following result set:

name
---------------------
http://PROD/BookStore

Problem

You need to extract data from the XML document.

Solution

To extract data from an XML document, you would utilize one of the various XQuery methods.

CREATE TABLE dbo.BookInvoice
       (
        BookInvoiceID INT IDENTITY PRIMARY KEY,
        BookInvoiceXML XML NOT NULL
       )
GO
INSERT dbo.BookInvoice (BookInvoiceXML)
VALUES
(' < BookInvoice invoicenumber = "1" customerid = "22" orderdate = "2008-07-01Z">
<OrderItems>
<Item id = "22" qty = "1" name = "SQL Fun in the Sun"/>
<Item id = "24" qty = "1" name = "T-SQL Crossword Puzzles"/>
</OrderItems>
</BookInvoice > '),
(' < BookInvoice invoicenumber = "1" customerid = "40" orderdate = "2008-07-11Z">
<OrderItems>
<Item id = "11" qty = "1" name = "MCITP Cliff Notes"/>
</OrderItems>
</BookInvoice > '),
(' < BookInvoice invoicenumber = "1" customerid = "9" orderdate = "2008-07-22Z">
<OrderItems>
<Item id = "11" qty = "1" name = " MCITP Cliff Notes"/>
<Item id = "24" qty = "1" name = "T-SQL Crossword Puzzles"/>
</OrderItems>
</BookInvoice > '),
SELECT BookInvoiceID
FROM dbo.BookInvoice
WHERE BookInvoiceXML.exist('/BookInvoice/OrderItems/Item[@id = 11]') = 1;

This query returns the following result set:

BookInvoiceID

-------------

2

3

How It Works

The xml data type column can be queried and the data can be modified using XQuery methods. XQuery is a query language that is used to search XML documents. These XQuery methods described in Table 24-2 are integrated into SQL Server and can be used in regular Transact-SQL queries. (Data modifications using XQuery are demonstrated in the next recipe.)

The XQuery methods are implemented as a method of the xml column. Thus, they are called in the format (XML Column).(XQuery method). Additionally, as pointed out at the beginning of the chapter, these methods are case sensitive and must be used in lowercase, regardless of the case sensitivity of your SQL Server instance.

In the previous example, the exist method is used to find all rows from the table for purchases of the item with an ID of 11. The next example demonstrates the nodes method, which shreds a document into a relational rowset. A local variable is used to populate a single XML document from the BookInvoice table, which is then referenced using the nodes method. This query retrieves a document and lists the ID element of each BookInvoice/OrderItems/Item node.

DECLARE @BookInvoiceXML XML;
SELECT @BookInvoiceXML = BookInvoiceXML
FROM dbo.BookInvoice
WHERE BookInvoiceID = 2;
SELECT BookID.value('@id', 'integer') BookID
FROM @BookInvoiceXML.nodes('/BookInvoice/OrderItems/Item') AS BookTable (BookID);

This query returns the following result set:

BookID
-----------
11

The next example demonstrates the query method, which is used to return the two-item elements from a specific XML document:

DECLARE @BookInvoiceXML XML;
SELECT @BookInvoiceXML = BookInvoiceXML
FROM dbo.BookInvoice
WHERE BookInvoiceID = 3;
SELECT @BookInvoiceXML.query('/BookInvoice/OrderItems'),

This query returns the following result set:

<OrderItems > <Item id = "11" qty = "1" name = "MCITP Cliff Notes" /> < Item id = "24" qty = "1"
name = "T-SQL Crossword Puzzles" /></OrderItems>

The previous example of this recipe demonstrates the value method, which is used to find the distinct book names from the first and second items within the BookInvoiceXML xml column.

SELECT DISTINCT
        BookInvoiceXML.value('(/BookInvoice/OrderItems/Item/@name)[1]',
         'varchar(30)') AS BookTitles
FROM dbo.BookInvoice
UNION
SELECT DISTINCT
        BookInvoiceXML.value('(/BookInvoice/OrderItems/Item/@name)[2]',
         'varchar(30)')
FROM dbo.BookInvoice;

This query returns the following result set:

BookTitles
-----------------------
NULL
MCDBA Cliff Notes
SQL Fun in the Sun
T-SQL Crossword Puzzles

Problem

You want to modify data stored in a column with the xml data type.

Solution

Utilize the XQuery modify method to update xml data.

SELECT BookInvoiceXML
FROM dbo.BookInvoice
WHERE BookInvoiceID = 2;
UPDATE dbo.BookInvoice
SET BookInvoiceXML.modify
('insert < Item id = "920" qty = "l" name = "SQL Server 2012 Transact-SOL Recipes"/>
into (/BookInvoice/OrderItems)[1]')
WHERE BookInvoiceID = 2;
SELECT BookInvoiceXML
FROM dbo.BookInvoice
WHERE BookInvoiceID = 2;

These queries return the following result sets:

BookInvoiceXML
-------------------------------------------------------------------------------------------
< BookInvoice invoicenumber = "1" customerid = "40" orderdate = "2008-07-11Z" > <OrderItems > <Item
id = "11" qty = "1" name = "MCDBA Cliff Notes" /></OrderItems > </BookInvoice>
BookInvoiceXML
-------------------------------------------------------------------------------------------
< BookInvoice invoicenumber = "1" customerid = "40" orderdate = "2008-07-11Z" > <OrderItems > <Item
id = "11" qty = "1" name = "MCDBA Cliff Notes" /> < Item id = "920" qty = "l" name = "SQL Server 2012
Transact-SOL Recipes" /></OrderItems > </BookInvoice>

How It Works

xml data type columns can be modified using the modify method in conjunction with the UPDATE statement, allowing you to insert, update, or delete an XML node in the xml data type column. In this example, the XQuery modify function is used to call an insert command to add a new item element into the existing XML document. The insert command inside the XQuery modify method is known as the XML DML operator; other XML DML operators are delete (which removes a node from the XML) and replace (which updates XML data).

Problem

You want to improve the performance of queries that are selecting data from xml data-typed columns.

Solution

Add an XML index on the xml column.

CREATE PRIMARY XML INDEX idx_XML_Primary_Book_ChapterDESC
ON dbo.Book(ChapterDesc);

How It Works

XML columns can store up to 2 GB per column, per row. Because of this potentially large amount of data, querying against the XML column can cause poor query performance. You can improve the performance of queries against XML data type columns by using XML indexes. When you create the primary XML index, the XML data is persisted to a special internal table in tabular form, which allows for more efficient querying. To create an XML index, the table must first already have a clustered index defined on the primary key of the table.

XML columns can have only primary XML index defined and then up to secondary indexes (of different types described in a bit). The CREATE INDEX command is used to define XML indexes. The abridged syntax is as follows:

CREATE [ PRIMARY ] XML INDEX index_name ON < object > ( xml_column_name ) [ USING XML INDEX xml_index_name
[ FOR { VALUE | PATH | PROPERTY } ] ] [ WITH ( <xml_index_option > [ ,. . . n ] ) ][ ; ]

Creating an index for an XML column uses several of the same arguments as a regular table index (see Chapter 17 for more information). Table 24-3 describes the XML-specific arguments of this command.

In the first example shown earlier, a primary XML index is created on an xml data type column. Now that a primary XML index has been created, secondary XML indexes can be created. The following example creates a secondary value XML index:

CREATE XML INDEX idx_XML_Value_Book_ChapterDESC ON dbo.Book(ChapterDESC)
USING XML INDEX idx_XML_Primary_Book_ChapterDESC FOR VALUE;

XML indexes may look a little odd at first because you are adding secondary indexes to the same xml data type column. Adding the different types of secondary indexes helps benefit performance, based on the different types of XQuery queries you plan to execute. All in all, you can have up to four indexes on a single xml data type column: one primary and three secondary. A primary XML index must be created prior to being able to create secondary indexes. A secondary PATH index is used to enhance performance for queries that specify a path and value from the xml column using XQuery. A secondary PROPERTY index is used to enhance the performance of queries that retrieve specific node values by specifying a path using XQuery. The secondary VALUE index is used to enhance the performance of queries that retrieve data using an imprecise path (for example, for an XPath expression that employs //, which can be used to find nodes in a document no matter where they exist).

Problem

You need to convert relational data stored in your database as an XML document.

Solution

Utilize the FOR XML clause of a SELECT statement to return an XML document from the tables and columns being selected.

SELECT ShiftID,
        Name
FROM AdventureWorks2012.HumanResources.[Shift]
FOR XML RAW('Shift'),
         ROOT('Shifts'),
         TYPE;

This query returns the following result set:

<Shifts>
    <Shift ShiftID = "1" Name = "Day" />
    <Shift ShiftID = "2" Name = "Evening" />
    <Shift ShiftID = "3" Name = "Night" />
</Shifts>

How It Works

The FOR XML clause is included at the end of a SELECT query in order to return data in an XML format. FOR XML extends a SELECT statement by returning the relational query results in an XML format. FOR XML operates in four different modes: RAW, AUTO, EXPLICIT, and PATH. The AUTO and RAW modes allow for a quick and semi-automated formatting of the results, whereas EXPLICIT and PATH provide more control over the hierarchy of data and the assignment of elements versus attributes. FOR XML PATH, on the other hand, is an easier alternative to EXPLICIT mode for those developers who are more familiar with the XPath language.

In RAW mode, a single-row element is generated for each row in the result set, with each column in the result converted to an attribute within the element.

In this example, FOR XML RAW is used to return the results of the HumanResources.Shift table in an XML format. The TYPE option is used to return the results in the XML data type, and ROOT is used to define a top-level element where the results will be nested. The FOR XML AUTO mode creates XML elements in the results of a SELECT statement and also automatically nests the data, based on the columns in the SELECT clause. AUTO shares the same options as RAW.

In this example, Employee, Shift, and Department information is queried from the AdventureWorks2012 database, with XML AUTO automatically arranging the hierarchy of the results.

SELECT TOP 3
        BusinessEntityID,
        Shift.Name,
        Department.Name
FROM AdventureWorks2012.HumanResources.EmployeeDepartmentHistory Employee
        INNER JOIN AdventureWorks2012.HumanResources.Shift Shift
         ON Employee.ShiftID = Shift.ShiftID
        INNER JOIN AdventureWorks2012.HumanResources.Department Department
         ON Employee.DepartmentID = Department.DepartmentID
ORDER BY BusinessEntityID
FOR XML AUTO,
         TYPE;

This query returns the following result set:

<Employee BusinessEntityID = "1">
    <Shift Name = "Day">
      <Department Name = "Executive" />
    </Shift>
</Employee>
<Employee BusinessEntityID = "2">
    <Shift Name = "Day">
      <Department Name = "Engineering" />
    </Shift>
</Employee>
<Employee BusinessEntityID = "3">
    <Shift Name = "Day">
      <Department Name = "Engineering" />
    </Shift>
</Employee>

Notice that the second INNER JOIN caused the values from the Department table to be children of the Shift table’s values. The Shift element was then included as a child of the Employee element. Rearranging the order of the columns in the SELECT clause, however, impacts how the hierarchy is returned. Here’s an example:

SELECT TOP 3
        Shift.Name,
        Department.Name,
        BusinessEntityID
FROM AdventureWorks2012.HumanResources.EmployeeDepartmentHistory Employee
        INNER JOIN AdventureWorks2012.HumanResources.Shift Shift
         ON Employee.ShiftID = Shift.ShiftID
        INNER JOIN AdventureWorks2012.HumanResources.Department Department
         ON Employee.DepartmentID = Department.DepartmentID
ORDER BY Shift.Name,
        Department.Name,
        BusinessEntityID
FOR XML AUTO,
         TYPE;

This query returns the following result set:

<Shift Name = "Day">
    <Department Name = "Document Control">
        <Employee BusinessEntityID = "217" />
        <Employee BusinessEntityID = "219" />
        <Employee BusinessEntityID = "220" />
    </Department>
</Shift>

This time, the top of the hierarchy is Shift, with a child element of Department and where Employees are child elements of the Department elements.

The FOR XML EXPLICIT mode allows you more control over the XML results, letting you define whether columns are assigned to elements or attributes. The EXPLICIT parameters have the same use and meaning as for RAW and AUTO; however, EXPLICIT also makes use of , which are used to define the resulting elements and attributes. For example, the following query displays the VendorID and CreditRating columns as attributes and displays the VendorName column as an element. The column is defined after the column alias using an element name, tag number, attribute, and directive.

SELECT TOP 3
        1 AS Tag,
        NULL AS Parent,
        BusinessEntityID AS [Vendor!1!VendorID],
        Name AS [Vendor!1!VendorName!ELEMENT],
        CreditRating AS [Vendor!1!CreditRating]
FROM AdventureWorks2012.Purchasing.Vendor
ORDER BY CreditRating
FOR XML EXPLICIT,
         TYPE;

This query returns the following result set:

<Vendor VendorID ="1496" CreditRating ="1">
    <VendorName >Advanced Bicycles</VendorName>
</Vendor>
<Vendor VendorID ="1492" CreditRating ="1">
    <VendorName >Australia Bike Retailer</VendorName>
</Vendor>
<Vendor VendorID ="1500" CreditRating ="1">
    <VendorName >Morgan Bike Accessories</VendorName>
</Vendor>

The Tag column in the SELECT clause is required in EXPLICIT mode in order to produce the XML document output. Each tag number represents a constructed element. The Parent column alias is also required, providing the hierarchical information about any parent elements. The Parent column references the tag of the parent element. If the Parent column is NULL, this indicates that the element has no parent and is top-level.

The TYPE directive in the FOR XML clause of the previous query was used to return the results as a true SQL Server native xml data type, allowing you to store the results in XML or query it using XQuery.

Next, the FOR XML PATH option defines column names and aliases as XPath expressions. XPath is a language used for searching data within an XML document.

FOR XML PATH uses some of the same arguments and keywords as other FOR XML variations. Where it differs, however, is in the SELECT clause, where XPath syntax is used to define elements, subelements, attributes, and data values. Here’s an example:

SELECT Name AS "@Territory",
        CountryRegionCode AS "@Region",
        SalesYTD
FROM AdventureWorks2012.Sales.SalesTerritory
WHERE SalesYTD > 6000000
ORDER BY SalesYTD DESC
FOR XML PATH('TerritorySales'),
         ROOT('CompanySales'),
         TYPE;

This query returns the following result set:

<CompanySales>
    <TerritorySales Territory ="Southwest" Region ="US">
        <SalesYTD >10510853.8739</SalesYTD>
    </TerritorySales>
    <TerritorySales Territory ="Northwest" Region ="US">
        <SalesYTD >7887186.7882</SalesYTD>
    </TerritorySales>
    <TerritorySales Territory ="Canada" Region ="CA">
        <SalesYTD >6771829.1376</SalesYTD>
    </TerritorySales>
</CompanySales>

This query returned results with a root element of CompanySales and a subelement of TerritorySales. The TerritorySales element was then attributed based on the territory and region code (both prefaced with @ in the SELECT clause). The SalesYTD, which was unmarked with XPath directives, became a subelement to TerritorySales. Using a column alias starting with @ and not containing a / is an example of an XPath-like name.

In this next example, the query explicitly specifies the hierarchy of the elements:

SELECT Name AS "Territory",
        CountryRegionCode AS "Territory/Region",
        SalesYTD AS "Territory/Region/YTDSales"
FROM AdventureWorks2012.Sales.SalesTerritory
WHERE SalesYTD > 6000000
ORDER BY SalesYTD DESC
FOR XML PATH('TerritorySales'),
         ROOT('CompanySales'),
         TYPE;

This query returns the following result set:

<CompanySales>
  <TerritorySales>
    <Territory >Southwest
      <Region >US
        <YTDSales >10510853.8739</YTDSales>
      </Region>
    </Territory>
  </TerritorySales>
  <TerritorySales>
    <Territory >Northwest
      <Region >US
        <YTDSales >7887186.7882</YTDSales>
      </Region>
      </Territory>
    </TerritorySales>
    <TerritorySales>
      <Territory >Canada
        <Region >CA
            <YTDSales >6771829.1376</YTDSales>
          </Region>
      </Territory>
    </TerritorySales>
</CompanySales>

The query specifies the CountryRegionCode to have an element name of Region as a subelement to the Territory element and specifies the SalesYTD to have an element name of YTDSales as a subelement to the Region element.

Problem

You need to return parts of an XML document as relational data.

Solution

Utilize the OPENXML function to parse a document and return the selected parts as a rowset.

DECLARE @XMLdoc XML,
        @iDoc INTEGER;
SET @XMLdoc =
' < Book name = "SQL Server 2000 Fast Answers">
    <Chapters>
        <Chapter id = "1" name = "Installation, Upgrades"/>
        <Chapter id = "2" name = "Configuring SQL Server"/>
        <Chapter id = "3" name = "Creating and Configuring Databases"/>
        <Chapter id = "4" name = "SQL Server Agent and SQL Logs"/>
    </Chapters>
</Book > ';
EXECUTE sp_XML_preparedocument @iDoc OUTPUT, @XMLdoc;
SELECT Chapter, ChapterNm
FROM OPENXML(@iDoc, '/Book/Chapters/Chapter', 0)
WITH (Chapter INT '@id', ChapterNm VARCHAR(50) '@name'),
EXECUTE sp_xml_removedocument @idoc;

This query returns the following result set:

Chapter    ChapterNm
-------    ---------------------------------
1          Installation, Upgrades
2          Configuring SQL Server
3          Creating and Configuring Databases
4          SQL Server Agent and SQL Logs

How It Works

OPENXML converts XML format to a relational form. To perform this conversion, the sp_XML_preparedocument system stored procedure is used to create an internal pointer to the XML document, which is then used with OPENXML in order to return the rowset data.

The syntax for the sp_XML_preparedocument system stored procedure is as follows:

sp_xml_preparedocument
hdoc
OUTPUT
[ , xmltext ]
[ , xpath_namespaces ]

Table 24-4 describes the arguments for this command.

The syntax for the OPENXML command is as follows:

OPENXML(idoc ,rowpattern, flags)
[WITH (SchemaDeclaration | TableName)]

Table 24-5 shows the arguments for this command.

In this example, the XML document is stored in an XML data typed variable. The document is then passed to the sp_XML_preparedocument system stored procedure, and the document handle is returned.

Next, a SELECT statement calls the OPENXML function, passing the XML document handle returned from the sp_XML_preparedocument system stored procedure for the first parameter and the XPath expression of the node to be queried in the second parameter. For the flags parameter, a 0 is passed in, specifying to use attribute-centric mappings. The WITH clause defines the actual result output. Two columns are defined: the chapter and the chapter name (Chapter and ChapterNm). For the column definitions, the column name, the data type, and the attribute from the XML document that will be used for this column are specified.

Finally, the sp_xml_removedocument system stored procedure is called, which removes the internal representation of the XML document specified by the document handle and invalidates the document handle.