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:

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)

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.

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:

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 This author has published 5 articles on DotNetSlackers. View other articles or the complete profile here.

---

Discussion

Subject	Author	Date
Very good article	Rajesh S	7/30/2008 6:06 AM
It was very useful imran. Thanks a lot. Great... Reply | Permanent link

---

Please login to rate or to leave a comment.