Reading Relational and XML Data | Solid Quality Learning, Microsoft Corporation Staff - Microsoft SQL Server 2005 Database Essentials Step by Step

Chapter 5, Designing a Database to Solve Business Needs, introduced the new xml datatype to store XML data natively in SQL Server 2005. Yet SQL Server 2005 offers several other technologies to manipulate relational data as XML data (or, conversely, to manipulate XML data as relational data) without having to store it. You might want to manipulate XML data as relational data because:

XML data is self describing. Applications can consume XML data without prior knowledge of its schema or structure.
XML is order dependent.
XML data is searchable by using special query languages, such as XQUERY and XPATH.

Relational formatting does not conform to these characteristics.

Viewing XML Results in SQL Server Management Studio

The easiest way to view XML results when using SQL Server Management Studio is to set the Results Pane view to Grid view instead of Text view.

Setting the Results Pane View

From the Query menu in SQL Server Management Studio, choose Results To Results To Grid.
After executing the query, the Results Pane will show a link instead of the result.
When you click the link, a new window will open in SQL Server Management Studio and show you the XML result. This new window is an XML Editor window.

Converting Relational Data to XML Format

The SELECT statement supports the FOR XML clause. This clause is used to transform the result of a query into XML format. You can start learning about XML in a simple way by using the first query that you learned in this chapter. The code in this section can be accessed from the sample files as \Ch07\Samples06.sql.

 SELECT OrderDate,SalesOrderNumber,CustomerID,Subtotal,TaxAmt,TotalDue FROM Sales.SalesOrderHeader WHERE ShipDate='20010708' FOR XML AUTO

The partial result of executing this query should look like the following:

 <Sales.SalesOrderHeader OrderDate="2001-07-01T00:00:00" SalesOrderNumber="SO43659" CustomerID="676" Subtotal="24643.9362" TaxAmt="1971.5149" TotalDue="27231.5495" /> <Sales.SalesOrderHeader OrderDate="2001-07-01T00:00:00" SalesOrderNumber="SO43660" CustomerID="117" Subtotal="1553.1035" TaxAmt="124.2483" TotalDue="1716.1794" /> ... <Sales.SalesOrderHeader OrderDate="2001-07-01T00:00:00" SalesOrderNumber="SO43701" CustomerID="11003" Subtotal="3399.9900" TaxAmt="271.9992" TotalDue="3756.9890" />

The resulting XML is not considered to be well formed because it does not contain a single root node. You can modify the query to create a well formed XML statement, as shown below.

 SELECT OrderDate,SalesOrderNumber,CustomerID,Subtotal,TaxAmt,TotalDue FROM Sales.SalesOrderHeader WHERE ShipDate='20010708' FOR XML AUTO, ROOT ('TotalSales')

The partial result of executing this query should look like the following:

 <TotalSales> <Sales.SalesOrderHeader OrderDate="2001-07-01T00:00:00" SalesOrderNumber="SO43659"  CustomerID="676" Subtotal="24643.9362" TaxAmt="1971.5149" TotalDue="27231.5495" /> <Sales.SalesOrderHeader OrderDate="2001-07-01T00:00:00" SalesOrderNumber="SO43660"  CustomerID="117" Subtotal="1553.1035" TaxAmt="124.2483" TotalDue="1716.1794" /> ... <Sales.SalesOrderHeader OrderDate="2001-07-01T00:00:00" SalesOrderNumber="SO43701"  CustomerID="11003" Subtotal="3399.9900" TaxAmt="271.9992" TotalDue="3756.9890" /> </TotalSales>

Notice that the table name (Sales.SalesOrderHeader) is used as the XML node name. By declaring a table alias, you can change the element name. Below, the query is modified again to use your own name for each XML node.

 SELECT OrderDate,SalesOrderNumber,CustomerID,Subtotal,TaxAmt,TotalDue FROM Sales.SalesOrderHeader MySales WHERE ShipDate='20010708' FOR XML AUTO, ROOT ('TotalSales')

The partial result of executing this query should look like the following:

 <TotalSales> <MySales OrderDate="2001-07-01T00:00:00" SalesOrderNumber="SO43659"  CustomerID="676" Subtotal="24643.9362" TaxAmt="1971.5149" TotalDue="27231.5495" /> <MySales OrderDate="2001-07-01T00:00:00" SalesOrderNumber="SO43660"  CustomerID="117" Subtotal="1553.1035" TaxAmt="124.2483" TotalDue="1716.1794" /> ... <MySales OrderDate="2001-07-01T00:00:00" SalesOrderNumber="SO43701"  CustomerID="11003" Subtotal="3399.9900" TaxAmt="271.9992" TotalDue="3756.9890" /> </TotalSales>

The same process is applied when you want to use a different name for each XML attribute instead of using the column name. Notice the variations in the following query and investigate the result by executing it.

 SELECT  OrderDate 'Date',         SalesOrderNumber 'OrderNumber',         CustomerID 'ID',         Subtotal,         TaxAmt 'Taxes',         TotalDue FROM Sales.SalesOrderHeader MySales WHERE ShipDate='20010708' FOR XML AUTO, ROOT('TotalSales')

In the previous examples, the resulting XML is built as attribute-based XML. In the following example, you will modify the query to receive element-based XML.

 SELECT OrderDate,SalesOrderNumber,CustomerID,Subtotal,TaxAmt,TotalDue FROM Sales.SalesOrderHeader MySales WHERE ShipDate='20010708' FOR XML AUTO, ROOT ('TotalSales'), ELEMENTS

The partial result of executing this query should look like the following:

 <TotalSales>   <MySales>     <OrderDate>2001-07-01T00:00:00</OrderDate>     <SalesOrderNumber>SO43659</SalesOrderNumber>     <CustomerID>676</CustomerID>     <Subtotal>24643.9362</Subtotal>     <TaxAmt>1971.5149</TaxAmt>     <TotalDue>27231.5495</TotalDue>   </MySales>   <MySales>     <OrderDate>2001-07-01T00:00:00</OrderDate>     <SalesOrderNumber>SO43660</SalesOrderNumber>     <CustomerID>117</CustomerID>     <Subtotal>1553.1035</Subtotal>     <TaxAmt>124.2483</TaxAmt>     <TotalDue>1716.1794</TotalDue>   </MySales> ... </TotalSales>

When using the FOR XML clause, queries can include any of the other clauses that have already been discussed.

You can look at a more complex example by retrieving results from multiple tables.

 SELECT  Vendor.VendorID,         Vendor.AccountNumber,         Vendor.[Name],         Product.ProductID,         Product.[Name] FROM Purchasing.Vendor Vendor     LEFT JOIN Purchasing.ProductVendor PV         INNER JOIN Production.Product Product         ON PV.ProductID=Product.ProductID     ON Vendor.VendorID=PV.VendorID ORDER BY Vendor.VendorID FOR XML AUTO, ROOT('Vendors')

The partial result of executing this query should look like the following:

 <Vendors>   <Vendor VendorID="1" AccountNumber="INTERNAT0001" Name="International">     <Product ProductID="462" Name="Lower Head Race" />   </Vendor>   <Vendor VendorID="2" AccountNumber="ELECTRON0002"    Name="Electronic Bike Repair &amp; Supplies">     <Product ProductID="511" Name="ML Road Rim" />     <Product ProductID="510" Name="LL Road Rim" />     <Product ProductID="512" Name="HL Road Rim" />   </Vendor>   <Vendor VendorID="3" AccountNumber="PREMIER0001" Name="Premier Sport, Inc.">     <Product ProductID="513" Name="Touring Rim" />   </Vendor>   <Vendor VendorID="4" AccountNumber="COMFORT0001" Name="Comfort Road Bicycles">     <Product ProductID="507" Name="LL Mountain Rim" />     <Product ProductID="508" Name="ML Mountain Rim" />   </Vendor> <Vendors>

When using the FOR XML AUTO clause, be aware that:

XML AUTO generates a new hierarchy level for each table in the SELECT query.
Table and column aliases can be used to rename the elements in the resulting XML.
All columns are formatted in the same way as either attributes or elements, but it is impossible to mix the different formatting modes.

If formatting modes cannot be mixed, how do you provide a different format (either attribute based or element based) for each column in the result? To solve this problem, SQL Server 2005 introduces the FOR XML PATH clause, which allows you to declare independent formatting options for each of the columns in a query.

Copy the following query to SQL Server Management Studio and compare the results with those from the previous query. The syntax is explained in Table 7-2.

 SELECT  Vendor.VendorID 'Vendor/@ID',         Vendor.AccountNumber 'Vendor/Account',         Vendor.[Name] 'Vendor/Name',          Product.ProductID 'Vendor/Product/@ID',         Product.[Name] 'Vendor/Product/Name' FROM Purchasing.Vendor Vendor     LEFT JOIN Purchasing.ProductVendor PV         INNER JOIN Production.Product Product         ON PV.ProductID=Product.ProductID     ON Vendor.VendorID=PV.VendorID ORDER BY Vendor.VendorID FOR XML PATH(''), ROOT('Vendors')

The partial result of executing this query should look like the following:

 <Vendors>   <Vendor ID="1">     <Account>INTERNAT0001</Account>     <Name>International</Name>     <Product ID="462">       <Name>Lower Head Race</Name>     </Product>   </Vendor>   <Vendor ID="2">     <Account>ELECTRON0002</Account>     <Name>Electronic Bike Repair &amp; Supplies</Name>     <Product ID="511">       <Name>ML Road Rim</Name>     </Product>   </Vendor> ... <Vendors>

By using the FOR XML PATH clause, the column aliases provide further formatting information to SQL Server 2005.

Table 7-2: Column Alias Syntax
Column Alias	Instructions to SQL Server 2005
element_name	Copy the value of this column into an element called element_name under the context element (the last processed node). Example: 'Vendor'
element_name1/ element_name2	Copy the value of this column into an element called element_name2 under an element called element_name1 . Example: 'Vendor/Account'
@attribute_name	Copy the value of this column into an attribute called attribute_name under the context element (the last processed node). Example: '@ID'
element_name/ @attribute_name	Copy the value of this column into an attribute called attribute_name under an element called element_name . Example: 'Vendor/@ID'

It is also important to be aware of the following information concerning the use of the FOR XML AUTO clause.

Developers have full control over the number of levels that the XML structure will contain.
XML attribute declarations must be declared before XML element declarations; therefore, column order does matter. Column order also indicates the context node with which to locate column values for which you do not specify the position in the XML structure.

Table aliases are ignored by the formatting mechanism in the XML PATH clause.

Note

The FOR XML clause supports four formatting modes: RAW, AUTO, EXPLICIT, and PATH. Only basic usage of the FOR XML AUTO and FOR XML PATH clauses have been covered in this chapter. For more information on how to use the other formatting modes, see the SQL Server Books Online topic, Constructing XML Using FOR XML.

Converting XML Data to Relational Format

You must now focus on the opposite scenario. Assume that you want to transform your XML data into a tabular (relational) format. SQL Server 2005 provides different technologies to accomplish this, but you are going to focus on the nodes() method of the new xml datatype.

Converting XML Data into a Relational Format Using the nodes() Method

Load the XML data into an xml datatype variable using the following T-SQL statements. The code in this section can be accessed from \Ch07\Samples07.sql in the sample files.

 DECLARE @MYDATA XML SET @MYDATA = '<Vendors>   <Vendor VendorID="1" AccountNumber="INTERNAT0001" Name="International">     <Product ProductID="462" Name="Lower Head Race" />   </Vendor>   <Vendor VendorID="2" AccountNumber="ELECTRON0002"    Name="Electronic Bike Repair Supplies">     <Product ProductID="511" Name="ML Road Rim" />     <Product ProductID="510" Name="LL Road Rim" />     <Product ProductID="512" Name="HL Road Rim" />   </Vendor>   <Vendor VendorID="3" AccountNumber="PREMIER0001" Name="Premier Sport, Inc.">     <Product ProductID="513" Name="Touring Rim" />   </Vendor>   <Vendor VendorID="4" AccountNumber="COMFORT0001" Name="Comfort Road Bicycles">     <Product ProductID="507" Name="LL Mountain Rim" />     <Product ProductID="508" Name="ML Mountain Rim" />   </Vendor>   <Vendor VendorID="5" AccountNumber="METROSP0001" Name="Metro Sport Equipment">     <Product ProductID="528" Name="Seat Lug" />   </Vendor>

 <Vendor VendorID="6" AccountNumber="GREENLA0001" Name="Green Lake Bike Company">       <Product />     </Vendor>   </Vendors>'

Construct a query using the value() method to extract specific values as well as the nodes() method to transform the XML into tabular format.

 SELECT XMLColumn.value('@VendorID','int') 'VendorID',        XMLColumn.value('@AccountNumber','nvarchar(100)') 'Account_Number',        XMLColumn.value('@Name','nvarchar(100)') 'Account_Name',        XMLColumn.value('(Product/@ProductID)[1]','int') 'FirstProduct' FROM @MYDATA.nodes('Vendors/Vendor') ResultTable(XMLColumn)

You can use this result set as you would any other relational table. When you execute this code, the result should look like the following figure.

The nodes() method returns a table with a single column (of type xml). The values inside this XML column are the result of the XPATH query sent as an input parameter to the nodes() method call. In the previous example, the XPATH query Vendors/Vendor indicates that you would like to select all of the nodes called Vendor that exist under a node called Vendors.

The value() method extracts a single value from the XML, transforms it into a SQL Server datatype, and returns a scalar value. The syntax in the above example is explained in Table 7-3.

Table 7-3: Input Parameter Syntax
Input Parameter	Instructions to SQL Server 2005
@attribute_name , int	Extract the value from the attribute_name attribute as an int datatype.
element_name , nvarchar(100)	Extract the value from the element_name element as an nvarchar(100) datatype.
element_name/@attribute_name , nvarchar(100)	Extract the value from the attribute_name attribute located under the element_name element as an nvarchar(100) datatype.
(element_name /@attribute_name)[1] , int	Extract the first value found from the attribute_name attribute under the element_name element as an int datatype.

Note	This chapter has barely scratched the surface of this topic. To learn more about using the XML data nodes() method, read the SQL Server Books Online topic, nodes() Method (xml Data Type).

Note	If you want to extract relational data from XML data without using the xml datatype, SQL Server 2005 offers another technique called OpenXML. For more information about how to use this other technology, read the SQL Server Books Online topic, Querying XML Using OPENXML.