Generating Relational data from xml datatype Part 2

Published: 20 Nov 2006
By: Imran Nathani

In Part 1 of this article, we saw the basics of the xml datatype and retrieval of data from it. In this part we will see the use of the xml datatype in relational data tables and the retrieval of data from an xml datatype in relational form.

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.

About Imran Nathani

Sorry, no bio is available

View complete profile

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
 
 
 

Discussion


Subject Author Date
placeholder Very good article Rajesh S 7/30/2008 6:06 AM

Please login to rate or to leave a comment.

Product Spotlight