Introduction

The purpose of displaying data from the xml datatype in a relational form is to apply normal ADO.NET practices and other relational data management concepts easily.

• Formation of Views from this formed relational data.
• Retrieval of this data as dataset, recordset or simply raw XML again.
• This data can easily be used with multiple data providers for further processing.

Displaying XML in Relational form

Let us begin with the same Invoices XML used in part 1. Suppose we would like to display that XML in relational form as displayed below:

InvoiceId	Date	SalesPerson
1	10-Jan-2006	XYZ
2	11-Jan-2006	XYZ
3	11-Jan-2006	ABC

Then we simply execute the following code:

DECLARE @xmldata AS xml
SET @xmldata = '<?xml version="1.0" ?> 
 <Invoices>   
  <Invoice InvoiceId="1">
    <Invoicedate>10-Jan-2006</Invoicedate>
    <Salesperson  id="1">XYZ</Salesperson>
  </Invoice>   
  <Invoice InvoiceId="2">
    <Invoicedate>11-Jan-2006</Invoicedate>
    <Salesperson  id="1">XYZ</Salesperson> 
  </Invoice>     
  <Invoice InvoiceId="3">
    <Invoicedate>11-Jan-2006</Invoicedate>
    <Salesperson  id="2">ABC</Salesperson> 
  </Invoice>      
 </Invoices>'

SELECT 
       Colx.query('data(@InvoiceId) ') AS InvoiceId, 
       Colx.query('data(Invoicedate) ') AS Date, 
       Colx.query('data(Salesperson)') AS SalesPerson
FROM     
       @xmldata.nodes('Invoices/Invoice') AS Tabx(Colx)

We can easily break the operations of the SELECT query above into two parts:

Part 1 retrieves the nodes from the Table (Column) form:

@xmldata .nodes('Invoices/Invoice') AS Tabx(Colx)

Here we retrieve all nodes from Invoices by using @xmldata .nodes('Invoices/Invoice'). We use the AS keyword to set an alias name to @xmldata as a table (Tabx), and a column(Colx).

Part 2 queries the data in the relational column.

Colx.query('data(@InvoiceId) ') AS InvoiceId 

Here we query Colx with the query method passing in an XQuery expression as the parameter. We also specify the alias name for the column with AS InvoiceId.

The CROSS APPLY clause

A CROSS APPLY is an INNER JOIN between a table and a table-valued function. This is a new feature of SQL Server 2005. The results are normally used to retrieve and display running totals. In previous version of SQL Server, the same was achieved by using a temp table.

Displaying xml datatype data in relational form

Let's assume we have a table in the database for the Purchases made by a Purchaser from many other different Sellers. Each Seller would issue a different electronic Invoice which would be stored in a raw XML form in the database and each Seller would assign different types of 'InvoiceId's, different format of dates etc. . Here, we have a table Purchases of that type with the following data.

CompanyId	CompanyName	Invoices
1	IMI International Inc.	<Invoices> <Invoice InvoiceId="1"> <Invoicedate>10-01-2006</Invoicedate> <Salesperson id="1">XYZ</Salesperson> </Invoice> <Invoice InvoiceId="2"> <Invoicedate>11-01-2006</Invoicedate> <Salesperson id="1">XYZ</Salesperson> </Invoice> <Invoice InvoiceId="3"> <Invoicedate>11-01-2006</Invoicedate> <Salesperson id="2">ABC</Salesperson> </Invoice> </Invoices>
2	LADS International LLC.	<Invoices> <Invoice InvoiceId="55"> <Invoicedate>10-April-05</Invoicedate> <Salesperson id="3">PQR</Salesperson> </Invoice> <Invoice InvoiceId="56"> <Invoicedate>11-June-05</Invoicedate> <Salesperson id="2">ABC</Salesperson> </Invoice> <Invoice InvoiceId="57"> <Invoicedate>11-May-06</Invoicedate> <Salesperson id="2">ABC</Salesperson> </Invoice> </Invoices>
3	Mail & Parcel	<Invoices> <Invoice InvoiceId="BY-105"> <Invoicedate>10-April-2005</Invoicedate> <Salesperson id="3">PQR</Salesperson> </Invoice> <Invoice InvoiceId="AX-117"> <Invoicedate>11-June-2005</Invoicedate> <Salesperson id="2">ABC</Salesperson> </Invoice> <Invoice InvoiceId="AB-232"> <Invoicedate>11-May-2006</Invoicedate> <Salesperson id="2">ABC</Salesperson> </Invoice> </Invoices>

Hence our SELECT query for this table would be:

SELECT 
       CompanyName, 
       Colx.query('data(@InvoiceId) ') AS InvoiceId, 
       Colx.query('data(Invoicedate) ') AS Date
FROM  
       Purchases 
CROSS APPLY    
       @xmldata.nodes('Invoices/Invoice') AS Tabx(Colx)

Our result for the above query would be:

CompanyName	InvoiceId	Date
IMI International Inc.	1	10-01-2006
IMI International Inc.	2	11-01-2006
IMI International Inc.	3	11-01-2006
LADS International LLC.	55	10-April-05
LADS International LLC.	56	11-June-05
LADS International LLC.	57	11-May-06
Mail & Parcel	BY-105	10-April-2005
Mail & Parcel	AX-117	11-June-2005
Mail & Parcel	AB-232	11-May-2006

Summary

The display of XML data as relational tables becomes very important in cases where the front ends are classical in nature not supporting latest XML standards. This is also useful in the intermediate phases of an application upgrade for the easy compatibly of the phase-in components with phase-out components.

Top Articles in this category Using SQL Server Integration Services (SSIS) Control Flow in SQL Server 2005 SQL Server Integration Services (SSIS) in SQL Server 2005 has been redone, and is clearly superior over SQL Server 2000. With all of the new capabilities and features in SSIS, it is possible to create very complex transformation packages. This article will show you how to take advantage of the control flow features. Using SQL Server Integration Services (SSIS) Data Flow in SQL Server 2005 SQL Server Integration Services (SSIS) in SQL Server 2005 has been redone, and is clearly superior over SQL Server 2000. With all of the new capabilities and features in SSIS, it is possible to create very complex data transformation packages. This article will show you how. Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial Pinal Dave talks about encryption in SQL Server 2005. The SqlCacheDependency Class Luke Stratman shows how and why to use the SqlCacheDependency class. SQL Server Pre-Code Review Tips A list of tips for enforcing coding standards in SQL.

Top