Quick Check

The query should return 25 rows, and the third column will contain product model instructions in the form of an XML document. You can click on the link for any of the rows returned and view the expanded XML document in a separate window. You will see that the instructions contain several nodes, such as step , material , and tool , which are children nodes for one or more locations.

You can optionally store XML schema information that is associated with a column, parameter, or variable. An XML column associated with a schema is said to be "typed." The schema information provides information about what values are acceptable for nodes and attributes. When a schema is provided, there is automatic validation of the XML document.

Once the schema is created, you need to add it to the SQL Server repository by executing the Transact-SQL command CREATE XML SCHEMA COLLECTION. Once this is done, you can reference the schema in SQL Server Management Studio. You can view a particular database schema by expanding the Programmability, Types, XML Schema Collections node in Object Explorer. (See Figure 2-6.) The Universal Resource Identifier (URI) associated with each of these schema definitions will be needed in the next section when we query an XML column.

Figure 2-6: XML schemas available with the AdventureWorks database

Querying XML Data with XQuery

XQuery is an XML query language adopted by the World Wide Web Consortium (W3C). You can learn more about this language specification by going to http://www.w3.org/TR/xquery/ . SQL Server 2005 provides a subset of this language in order to query the xml data type.

A query where you return the entire instructions column is fine if you want to return the entire XML document. However, if you want to just return a portion of the XML document, such as a particular node or attribute, then you will need to utilize one of the built-in methods for working with the xml data type.

The query method is used to query an xml data type using an XQuery expression. An XQuery expression is made up of the following:

• Prolog This is typically a namespace declaration that includes a prefix. For the AdventureWorks database, we will use the prefix AW, which stands for Adventure Works. This prefix will be referenced in the body. The namespace points to a URI. The URI refers to the location of an XML schema (.xsd file) that is used to define the XML column. For the AdventureWorks database, several schemas have already been imported and will be used in subsequent examples.

• Body The body contains one or more expressions that specify what data needs to be returned. This expression is similar to an XPath expression.

If you were to use the query method to return instructions for those products where the number of labor hours is greater than two and a half hours, the query would look like the following:

USE AdventureWorks GO SELECT p.Name, p.ProductNumber, pm.Instructions.query( 'declare namespace AW="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ ProductModelManuInstructions"; AW:root/AW:Location[@LaborHours>2.5]') As Locations FROM Production.Product p JOIN Production.ProductModel pm ON p.ProductModelID = pm.ProductModelID WHERE pm.Instructions is not NULL

This query should return 25 results, just as the first query did. The only difference is that the value in the third column will be different. In the second query, we are using the query method to limit the XML data returned in that column. The body portion of this query specifies that XML from the Location node should be returned for locations where the LaborHours attribute is greater than two and a half hours. For example, after running this query, if you were to click the XML link for the first product, you would see the following XML:

<AW:Location xmlns:AW="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ ProductModelManuInstructions" LaborHours="3" LotSize="1" SetupHours="0.25" LocationID="50"> Work Center 50 - SubAssembly. The following instructions pertain to Work Center 50. (Setup hours = .25, Labor Hours = 3, Machine Hours = 0, Lot Sizing = 1) <AW:step>Add Seat Assembly. </AW:step> <AW:step>Add Brake assembly. </AW:step> <AW:step>Add Wheel Assembly. </AW:step> <AW:step>Inspect Front Derailleur. </AW:step> <AW:step>Inspect Rear Derailleur. </AW:step> </AW:Location> <AW:Location xmlns:AW="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ ProductModelManuInstructions" LaborHours="4" LotSize="1" LocationID="60"> Work Center 60 - Final Assembly. The following instructions pertain to Work Center 60. (Setup hours = 0, Labor Hours = 4, Machine Hours = 0, Lot Sizing = 1) <AW:step> Perform final inspection per engineering specification <AW:specs>AWC- 915</AW:

specs

>. </AW:step> <AW:step>Complete all required certification forms.</AW:step> <AW:step>Move to shipping.</AW:step> </AW:Location>

Notice that the XML returned includes the prefix AW, which was specified in the prolog section of the query method. Also note that the XML includes all nodes beneath the Locations node, such as step and specs. For those products where the number of labor hours per location was less than or equal to two and a half hours, an empty string is returned.

An alternative to declaring a namespace within the XQuery expression is to use an XML namespace. This is done using the XML NAMESPACES Transact-SQL statement. For example, you could replace the previous query with the following:

USE AdventureWorks GO WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ ProductModelManuInstructions' As AW) SELECT p.Name, p.ProductNumber, pm.Instructions.query( 'AW:root/AW:Location[@LaborHours>2.5]') As Locations FROM Production.Product p JOIN Production.ProductModel pm ON p.ProductModelID = pm.ProductModelID WHERE pm.Instructions is not NULL

Other methods available for the xml data type include the following:

• value(XQuery expression, SQL type) Similar to the query method, this also performs an XQuery, but it returns the XML as a SQL type other than the xml data type.

• exist(XQuery expression) Used to determine whether a query will return a nonempty result. If the method returns a value of 1, then there is at least one node to be returned. If the method returns a value of 0, then an empty result would be returned. If the method returns a value of NULL, then no XML document was found in that column.

  The exist method can be really useful in combining the results of an XQuery expression with a Transact-SQL query. For example, the second query in this section returned all records where the instructions were NOT NULL. Twenty-five records were returned, but not all of them contained XML due to the XQuery restriction. If you wanted to return records for those products that had locations in which the number of labor hours was greater than two and a half hours, you could use the following query instead and only return 22 results:

  USE AdventureWorks GO WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ ProductModelManuInstructions' As AW) SELECT p.Name, p.ProductNumber, pm.Instructions.query( 'AW:root/AW:Location[@LaborHours>2.5]') As Locations FROM Production.Product p JOIN Production.ProductModel pm ON p.ProductModelID = pm.ProductModelID --WHERE pm.instructions is not null WHERE pm.Instructions.exist('/AW:root/AW:Location[@LaborHours>2.5]') = 1 AND pm.instructions is not NULL
  

• modify(XML DML) Used in conjunction with an UPDATE statement, this method can be used to insert, delete, or replace the XML using a Data Manipulation Language (DML) statement.

• nodes(XQuery expression) Used when you need to transform XML data into a relational format. The method returns the data as a column within a table. With this method, you can return multiple rows by using the value method with the nodes method. This can be useful for retrieving a rowset view of the XML data.

Using Parameters

You need to query XML columns using parameters and not hard-coded values. The sql:variable function enables you to expose a variable to an XQuery statement. For example, you can use this function together with the query method to return XML that contains locations with the LocationID attribute set to a value of 50:

USE AdventureWorks GO DECLARE @LocID int; SET @LocID = 50; WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure -works/ProductModelManuInstructions' As AW) SELECT p.Name, p.ProductNumber, pm.Instructions.query( 'AW:root/AW:Location[@LocationID=(sql:variable("@LocID"))]') As Location FROM Production.Product p JOIN Production.ProductModel pm ON p.ProductModelID = pm.ProductModelID WHERE pm.Instructions is not NULL

This query will return 25 records. If you were to click the link in the Location column for the first record, you should see the following:

<AW:Location xmlns:AW="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ ProductModelManuInstructions" LaborHours="3" LotSize="1" SetupHours="0.25" LocationID="50"> Work Center 50 - SubAssembly. The following instructions pertain to Work Center 50. (Setup hours = .25, Labor Hours = 3, Machine Hours = 0, Lot Sizing = 1) <AW:step>Add Seat Assembly. </AW:step> <AW:step>Add Brake assembly. </AW:step> <AW:step>Add Wheel Assembly. </AW:step> <AW:step>Inspect Front Derailleur. </AW:step> <AW:step>Inspect Rear Derailleur. </AW:step> </AW:Location>

Alternatively, you can use the value method to return a portion of the XML. The value that is returned can then be compared to a Transact-SQL variable in the WHERE clause. For example, the following query can also be used to return XML for a location with a location ID equal to 50:

USE AdventureWorks GO DECLARE @LocID int; SET @LocID = 50; WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/ adventure-works/ProductModelManuInstructions' As AW) SELECT p.Name, p.ProductNumber, pm.Instructions.query( 'AW:root/AW:Location') As Location FROM Production.Product p JOIN Production.ProductModel pm ON p.ProductModelID = pm.ProductModelID WHERE pm.instructions.value('(AW:root/AW:Location/@LocationID [1]', 'int') = @LocID AND pm.Instructions is not NULL

Note that this query still uses the value method to return the XML for the Location column. The WHERE clause uses the value method to specify that a singleton value should be returned as an integer. On the surface, this looks like the same result is being accomplished as in the previous query. However, the drawback to using this method is that you need to return a singleton value, which in this case is the first node. This means that the query will only return data where the Location ID is 50 in the first node. Due to this restriction, the query will only return five records instead of 25.

Returning Data as XML

There might be times when you need to return data from a relational database as XML. The FOR XML clause can be used with a SELECT query to accomplish this. To use the FOR XML clause, you need to specify one of the following four modes:

• RAW The simplest form; this is used to return a single element for each row. For the XML to be hierarchical, you would have to utilize nested FOR XML queries.

• AUTO This mode will return the data in a hierarchical format, but you have no control over how it is shaped. The shape is controlled by how the SELECT statement is written.

• EXPLICIT This mode enables you to control the shape of the XML by specifying a format. These types of queries can be difficult to write, and typically you can accomplish the same result using nested FOR XML queries.

• PATH This mode is also an alternative to using the EXPLICIT mode. It enables you to specify attributes and elements as XPath expressions.

  The following query is an example of using the RAW mode and can be used to return XML for the Product ID with a value of 1:

  Select Name, ProductNumber, ListPrice FROM Production.Product WHERE ProductID = 1 FOR XML RAW The previous query should return the following XML string: <row Name="Adjustable Race" ProductNumber="AR-5381" ListPrice="0.0000" />
  

If you preferred that the product columns were returned as elements and not attributes, you could alternatively use the following query:

Select Name, ProductNumber, ListPrice FROM Production.Product WHERE ProductID = 1 FOR XML RAW, ELEMENTS

The ELEMENTS keyword is an enhancement that forces the RAW mode to be element-centric and not attribute-centric . The results from the second query would be as follows :

<row> <Name>Adjustable Race</Name> <ProductNumber>AR-5381</ProductNumber> <ListPrice>0.0000</ListPrice> </row>

If you were executing a complex SQL statement that included levels such as the detail associated with an order, then using one of the other modes would be more appropriate. For example, if you wanted to return information about purchase orders that included all detail for orders with a status of 2, you could use the following query:

USE AdventureWorks GO Select [Order].PurchaseOrderID, [Order].Status, Vendor.Name as VendorName, ShipMethod.Name as ShipMethod, [Order].OrderDate, [Order].Shipdate, [Order].TotalDue, Detail.OrderQty, Detail.UnitPrice, Detail.LineTotal, Product.Name as ProductName FROM Purchasing.PurchaseOrderHeader [Order] JOIN Purchasing.Vendor Vendor ON [Order].VendorID = Vendor.VendorID JOIN Purchasing.ShipMethod ShipMethod ON [Order].ShipMethodID = ShipMethod.ShipMethodID JOIN Purchasing.PurchaseOrderDetail Detail ON [Order].PurchaseOrderID = Detail.PurchaseOrderID JOIN Production.Product Product ON Detail.ProductID = Product.ProductID WHERE [Order].Status = 2 FOR XML AUTO

By including the FOR XML AUTO clause, you will return XML in levels. However, you will have little control over how the XML is formatted. A portion of the XML returned from the previous query is as follows:

<Order PurchaseOrderID="4001" Status="2" OrderDate="2004-03-14T00:00:00" Shipdate="2004-04- 08T00:00:00" TotalDue="5036.1465"> <Vendor VendorName="G &amp; K Bicycle Corp."> <ShipMethod ShipMethod="OVERSEAS - DELUXE"> <Detail OrderQty="25" UnitPrice="13.0863" LineTotal="327.1575"> <Product ProductName="Sport-100 Helmet, Red" /> <Product ProductName="Sport-100 Helmet, Black" /> </Detail> <Detail OrderQty="300" UnitPrice="13.0800" LineTotal="3924.0000"> <Product ProductName="Sport-100 Helmet, Blue" /> </Detail> </ShipMethod> </Vendor> </Order> <Order PurchaseOrderID="4002" Status="2" OrderDate="2004-10-23T00:00:00" Shipdate="2004-11- 17T00:00:00" TotalDue="1122.0000"> <Vendor VendorName="Jeff's Sporting Goods"> <ShipMethod ShipMethod="OVERSEAS - DELUXE"> <Detail OrderQty="150" UnitPrice="3.4000" LineTotal="510.0000"> <Product ProductName="Mountain Bike Socks, M" /> <Product ProductName="Mountain Bike Socks, L" /> </Detail> </ShipMethod> </Vendor> </Order>

The element and attribute names derive from the alias names used in the SELECT query. If the format created by the AUTO method is acceptable, then this is the easiest way to return formatted XML data.

However, if you want more control over whether columns are assigned as attributes or elements, you can use the PATH method. For example, assume you wanted to return the following XML results:

<PurchaseOrders> <Order PurchaseOrderID="4001" Status="2" VendorName="G &amp; K Bicycle Corp." ShipMethodName="OVERSEAS - DELUXE" OrderDate="2004-03-14T00:00:00" ShipDate="2004-04- 08T00:00:00" TotalDue="5036.1465" /> <Order PurchaseOrderID="4002" Status="2" VendorName="Jeff's Sporting Goods" ShipMethodName="OVERSEAS - DELUXE" OrderDate="2004-10-23T00:00:00" ShipDate="2004-11- 17T00:00:00" TotalDue="1122.0000" /> <Order PurchaseOrderID="4003" Status="2" VendorName="Integrated Sport Products" ShipMethodName="OVERSEAS - DELUXE" OrderDate="2004-06-14T00:00:00" ShipDate="2004-07- 09T00:00:00" TotalDue="30198.9600" /> <Order PurchaseOrderID="4004" Status="2" VendorName="Integrated Sport Products" ShipMethodName="OVERSEAS - DELUXE" OrderDate="2003-12-05T00:00:00" ShipDate="2003-12- 30T00:00:00" TotalDue="51021.3000" /> <Order PurchaseOrderID="4005" Status="2" VendorName="Integrated Sport Products" ShipMethodName="OVERSEAS - DELUXE" OrderDate="2004-02-28T00:00:00" ShipDate="2004-03- 24T00:00:00" TotalDue="8167.5000" /> <Order PurchaseOrderID="4006" Status="2" VendorName="International Trek Center" ShipMethodName="OVERSEAS - DELUXE" OrderDate="2004-02-19T00:00:00" ShipDate="2004-03- 15T00:00:00" TotalDue="24070.7500" /> <Order PurchaseOrderID="4007" Status="2" VendorName="Fitness Association" ShipMethodName="OVERSEAS - DELUXE" OrderDate="2004-04-01T00:00:00" ShipDate="2004-04- 26T00:00:00" TotalDue="609422.0000" /> <Order PurchaseOrderID="4008" Status="2" VendorName="Team Athletic Co." ShipMethodName="OVERSEAS - DELUXE" OrderDate="2004-05-23T00:00:00" ShipDate="2004-06- 17T00:00:00" TotalDue="436401.9000" /> <Order PurchaseOrderID="4009" Status="2" VendorName="Green Lake Bike Company" ShipMethodName="OVERSEAS - DELUXE" OrderDate="2003-12-11T00:00:00" ShipDate="2004-01- 05T00:00:00" TotalDue="16406.5000" /> <Order PurchaseOrderID="4010" Status="2" VendorName="Jeff's Sporting Goods" ShipMethodName="OVERSEAS - DELUXE" OrderDate="2003-12-11T00:00:00" ShipDate="2004-01- 05T00:00:00" TotalDue="41536.0000" /> <Order PurchaseOrderID="4011" Status="2" VendorName="Green Lake Bike Company" ShipMethodName="OVERSEAS - DELUXE" OrderDate="2004-07-25T00:00:00" ShipDate="2004-08- 19T00:00:00" TotalDue="59941.7500" /> <Order PurchaseOrderID="4012" Status="2" VendorName="Integrated Sport Products" ShipMethodName="OVERSEAS - DELUXE" OrderDate="2004-07-25T00:00:00" ShipDate="2004-08- 19T00:00:00" TotalDue="1097448.0000" /> </PurchaseOrders> You could use the following query to return the previous results: USE AdventureWorks GO Select [Order].PurchaseOrderID "@PurchaseOrderID", [Order].Status "@Status", Vendor.Name "@VendorName", ShipMethod.Name "@ShipMethodName", [Order].OrderDate "@OrderDate", [Order].Shipdate "@ShipDate", [Order].TotalDue "@TotalDue" FROM Purchasing.PurchaseOrderHeader [Order] JOIN Purchasing.Vendor Vendor ON [Order].VendorID = Vendor.VendorID JOIN Purchasing.ShipMethod ShipMethod ON [Order].ShipMethodID = ShipMethod.ShipMethodID WHERE [Order].Status = 2 FOR XML PATH('Order'), ROOT('PurchaseOrders')

This query uses the PATH mode and passes in the name of the element to be used for each row returned. The query also uses the ROOT directive to pass in the name of the root element. Attributes are specified using the at (@) prefix. You can also specify element names using the name followed by the forward slash (/).

Updating Data Using XML

It might be necessary for you to update relational data based on some value in an XML document. Because SQL Server 2005 includes the xml data type, this can be accomplished by creating a stored procedure or user -defined function that accepts an XML document as an input parameter.

The body of the stored procedure or user-defined function would then contain Transact-SQL code that retrieved values from the XML document. This could be done using the methods shown in the preceding section, "Querying XML Data with XQuery."

For example, assume you had an XML document that contained updated contact information. The document might look similar to the following:

<UpdatedContactInfo ContactID="1"> <Name> <FirstName>Gustavo</FirstName> <MiddleName>G.</MiddleName> <LastName>Achong</LastName> </Name> </UpdatedContactInfo>

If you were to query the Person.Contact table for the AdventureWorks database, you would see that the middle name for the first contact is empty. The following stored procedure could be used to update the name columns in the Person.Contact table with the values from the XML document:

CREATE PROCEDURE spUpdateContactName(@xml xml) AS BEGIN --Get the ID since we will use this in more than one DECLARE @ID int SET @ID = (SELECT @xml.value('/UpdatedContactInfo[1]/@ContactID', 'int')) --Update the Names with the values in the XML Document UPDATE Person.Contact SET FirstName = (SELECT @xml.value('(/UpdatedContactInfo/Name/FirstName) [1]', 'nvarchar(50)')), LastName = (SELECT @xml.value('(/UpdatedContactInfo/Name/LastName) [1]', 'nvarchar(50)')), MiddleName = (SELECT @xml.value('(/UpdatedContactInfo/Name/MiddleName) [1]', 'nvarchar(50)')) WHERE ContactID = @ID END

You would then execute the stored procedure and pass in the XML document using the following Transact-SQL code:

DECLARE @xml xml Set @xml = '<UpdatedContactInfo ContactID="1"><Name><FirstName>Gustavo</ FirstName><MiddleName>G.</MiddleName> <LastName>Achong</LastName></Name></UpdatedContactInfo>'

EXEC

spUpdateContactName @xml

The result of executing the previous Transact-SQL statements should be that one record was updated. The middle name for the first contact would then be set with a value of "G."

Lab: Working with XML Data

In this lab, you will experiment with working with XML data. In the first exercise, you will write a query against an XML column using an XQuery expression. In the second exercise, you will return data from a relational database as XML.

The completed lab is available in the \Labs\Chapter 02\Lab3 folder on the companion CD.

Exercise 1: Write an XQuery Expression

In this exercise, you will create a query against an XML column in the Person.Contact table. The query returns data for all contacts with postal address information in the XML column AdditionalContactInfo.

Not all contacts have this information embedded in the XML column, so the query uses the exist method to test for this condition. If the postal address exists, then the value method is used to retrieve the street address, city, state, ZIP Code, and country for each contact.

Unlike the instructions column in the Production.ProductModel table, AdditionalContactInfo utilizes multiple schemas. For this reason, we use the WITH XMLNAMESPACES clause and declare namespace declarations to reference these schemas.

1. Open SQL Server Management Studio.

2. Connect to the instance of SQL Server 2005 that contains the AdventureWorks database.

3. Select New Query.

4. Add the following code to the query window:

   USE AdventureWorks GO WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ ContactInfo' As AW) SELECT c.FirstName + ' ' + c.LastName as "Contact Name", ContactInfo.ref.value( 'declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/a dventure-works/ContactTypes"; (act:homePostalAddress/act:Street)[1]', 'nvarchar(50)') As "Postal Street", ContactInfo.ref.value( 'declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure- works/ContactTypes"; (act:homePostalAddress/act:City)[1]', 'nvarchar(50)') As "Postal City", ContactInfo.ref.value( 'declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure- works/ContactTypes"; (act:homePostalAddress/act:StateProvince)[1]', 'nvarchar(50)') As "Postal State", ContactInfo.ref.value( 'declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure- works/ContactTypes"; (act:homePostalAddress/act:PostalCode)[1]', 'nvarchar(50)') As "Zip Code", ContactInfo.ref.value( 'declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure- works/ContactTypes"; (act:homePostalAddress/act:CountryRegion)[1]', 'nvarchar(50)') As "Postal Country" FROM Person.Contact c OUTER APPLY c.AdditionalContactInfo.nodes( '/AW:AdditionalContactInfo') AS ContactInfo(ref) WHERE ContactInfo.ref.exist( 'declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/ adventure-works/ContactTypes"; act:homePostalAddress') = 1 AND c.AdditionalContactInfo is not NULL
   

5. Click the Execute button. The results window should display three records, which are the only records that contain the postal address information. The results from this query are listed in Table 2-7.

Table 2-7: Query Results When Using the Query in Exercise 1
Open table as spreadsheet

Contact Name	Postal Street	Postal City	Postal State	Zip Code	Country
Gustavo Achong	123 Oak	Seattle	WA	98001	USA
Catherine Abel	P.O. Box 5	Edmonds	WA	98431	USA
Kim Abercrombie	990 5th Avenue	Redmond	WA	98052	USA