Beyond Relational: XQuery Lab

This series of posts is part of the SQL Server XML Tutorials series that presents a set of XQuery, XSD, FOR XML and XML Bulk Load tutorials. This series of tutorials present several dozen representation/structure of an Order Information XML document and explains how to process them using XQuery and XML Bulk Load component. Then it explains how to create an XSD schema for the given XML document. It also explains how to generate such a document using FOR XML directive.

Each XQuery tutorial in this series will present 3 different versions of the XQuery example:

Reading information from an XML variable
Reading information from an XML Column
Reading information from an XML file

This tutorial demonstrates how to read information from an XML document using XQuery. We will use Sample XML Document 1 for the example in this post. Here is a list of all the different XML documents we will use throughout this series of tutorials.

Reading information from an XML variable

-- ------------------------------------------------------------------
-- Declare an XML variable and assign value
-- ------------------------------------------------------------------
DECLARE @x XML
SELECT @x = '
<Orders>
  <Order OrderNumber="20001" OrderDate="2008-01-01Z"
         DeliveryDate="2008-01-20T10:30:00-08:00"
         CustomerNumber="GREAL" CustomerName="Great Lakes Food Market"
         BillingCity="Eugene" BillingState="OR" BillingZip="97403"
         BillingAddress="2732 Baker Blvd."
         ShippingCity="Eugene" ShippingState="OR" ShippingZip="97403"
         ShippingAddress="2732 Baker Blvd"
         Terms="30 Days Credit"
         ContactName="Howard Snyder" ContactTitle="Purchase Manager"
         ContactEmail="hsnyder@greatlakes.com"
         ContactPhone="(503) 555-7555" ContactFax="(503) 555-2376"
         OrderNote="Delivery needed before 8 AM"
         InvoiceNote="Adjust the previous credit note with this invoice"
         DiscountAmount="300">
    <Items>
      <Item ItemNumber="FB001923" Quantity="12" Price="18.25" />
      <Item ItemNumber="SG060020" Quantity="80" Price="12.75" />
      <Item ItemNumber="FB019090" Quantity="24" Price="6.00" />
    </Items>
  </Order>
</Orders>'

-- ------------------------------------------------------------------
-- Read Order Header Information
-- ------------------------------------------------------------------
SELECT
    x.value('@OrderNumber','VARCHAR(20)') AS OrderNumber,
    x.value('xs:date(@OrderDate)','DATETIME') AS OrderDate,
    x.value('@DeliveryDate cast as xs:dateTime ?','DATETIME') 
        AS DeliveryDate,
    x.value('@CustomerNumber','VARCHAR(20)') AS CustomerNumber,
    x.value('@CustomerName','VARCHAR(40)') AS CustomerName,
    x.value('@BillingCity','VARCHAR(20)') AS BillingCity,
    x.value('@BillingState','VARCHAR(10)') AS BillingState,
    x.value('@BillingZip','VARCHAR(5)') AS BillingZip,
    x.value('@BillingAddress','VARCHAR(50)') AS BillingAddress,
    x.value('@ShippingCity','VARCHAR(20)') AS ShippingCity,
    x.value('@ShippingState','VARCHAR(10)') AS ShippingState,
    x.value('@ShippingZip','VARCHAR(5)') AS ShippingZip,
    x.value('@ShippingAddress','VARCHAR(50)') AS ShippingAddress,
    x.value('@Terms','VARCHAR(50)') AS Terms,
    x.value('@ContactName','VARCHAR(40)') AS ContactName,
    x.value('@ContactTitle','VARCHAR(40)') AS ContactTitle,
    x.value('@ContactEmail','VARCHAR(50)') AS ContactEmail,
    x.value('@ContactPhone','VARCHAR(20)') AS ContactPhone,
    x.value('@ContactFax','VARCHAR(20)') AS ContactFax,
    x.value('@OrderNote','VARCHAR(50)') AS OrderNote,
    x.value('@InvoiceNote','VARCHAR(50)') AS InvoiceNote,
    x.value('@DiscountAmount','MONEY') AS DiscountAmount
FROM @x.nodes('Orders/Order') ord(x)

-- ------------------------------------------------------------------
-- Read Item details
-- ------------------------------------------------------------------
SELECT 
    i.value('@ItemNumber','VARCHAR(50)') AS ItemNumber,
    i.value('@Quantity','INT') AS Quantity,
    i.value('@Price','MONEY') AS Price
FROM @x.nodes('Orders/Order/Items/Item') itm(i)

Reading information from an XML column

-- ------------------------------------------------------------------
-- Create a table and insert an XML value into an XML column
-- ------------------------------------------------------------------
DECLARE @t TABLE (OrderInfo XML)
INSERT INTO @t (OrderInfo) SELECT '
<Orders>
  <Order OrderNumber="20001" OrderDate="2008-01-01Z"
         DeliveryDate="2008-01-20T10:30:00-08:00"
         CustomerNumber="GREAL" CustomerName="Great Lakes Food Market"
         BillingCity="Eugene" BillingState="OR" BillingZip="97403"
         BillingAddress="2732 Baker Blvd."
         ShippingCity="Eugene" ShippingState="OR" ShippingZip="97403"
         ShippingAddress="2732 Baker Blvd"
         Terms="30 Days Credit"
         ContactName="Howard Snyder" ContactTitle="Purchase Manager"
         ContactEmail="hsnyder@greatlakes.com"
         ContactPhone="(503) 555-7555" ContactFax="(503) 555-2376"
         OrderNote="Delivery needed before 8 AM"
         InvoiceNote="Adjust the previous credit note with this invoice"
         DiscountAmount="300">
    <Items>
      <Item ItemNumber="FB001923" Quantity="12" Price="18.25" />
      <Item ItemNumber="SG060020" Quantity="80" Price="12.75" />
      <Item ItemNumber="FB019090" Quantity="24" Price="6.00" />
    </Items>
  </Order>
</Orders>'

-- ------------------------------------------------------------------
-- Read Order Header Information
-- ------------------------------------------------------------------
SELECT
    x.value('@OrderNumber','VARCHAR(20)') AS OrderNumber,
    x.value('xs:date(@OrderDate)','DATETIME') AS OrderDate,
    x.value('@DeliveryDate cast as xs:dateTime ?','DATETIME') 
        AS DeliveryDate,
    x.value('@CustomerNumber','VARCHAR(20)') AS CustomerNumber,
    x.value('@CustomerName','VARCHAR(40)') AS CustomerName,
    x.value('@BillingCity','VARCHAR(20)') AS BillingCity,
    x.value('@BillingState','VARCHAR(10)') AS BillingState,
    x.value('@BillingZip','VARCHAR(5)') AS BillingZip,
    x.value('@BillingAddress','VARCHAR(50)') AS BillingAddress,
    x.value('@ShippingCity','VARCHAR(20)') AS ShippingCity,
    x.value('@ShippingState','VARCHAR(10)') AS ShippingState,
    x.value('@ShippingZip','VARCHAR(5)') AS ShippingZip,
    x.value('@ShippingAddress','VARCHAR(50)') AS ShippingAddress,
    x.value('@Terms','VARCHAR(50)') AS Terms,
    x.value('@ContactName','VARCHAR(40)') AS ContactName,
    x.value('@ContactTitle','VARCHAR(40)') AS ContactTitle,
    x.value('@ContactEmail','VARCHAR(50)') AS ContactEmail,
    x.value('@ContactPhone','VARCHAR(20)') AS ContactPhone,
    x.value('@ContactFax','VARCHAR(20)') AS ContactFax,
    x.value('@OrderNote','VARCHAR(50)') AS OrderNote,
    x.value('@InvoiceNote','VARCHAR(50)') AS InvoiceNote,
    x.value('@DiscountAmount','MONEY') AS DiscountAmount
FROM @t
CROSS APPLY OrderInfo.nodes('Orders/Order') ord(x)

-- ------------------------------------------------------------------
-- Read Item details
-- ------------------------------------------------------------------
SELECT 
    i.value('@ItemNumber','VARCHAR(50)') AS ItemNumber,
    i.value('@Quantity','INT') AS Quantity,
    i.value('@Price','MONEY') AS Price
FROM @t
CROSS APPLY OrderInfo.nodes('Orders/Order/Items/Item') itm(i)

Reading information from an XML file

-- ------------------------------------------------------------------
-- Declare an XML variable and load information from an XML File
-- ------------------------------------------------------------------
DECLARE @x XML
SELECT @x = CONVERT(XML, bulkcolumn, 2) 
FROM OPENROWSET(BULK 'C:\temp\order1.xml', SINGLE_BLOB) AS x

-- ------------------------------------------------------------------
-- Read Order Header Information
-- ------------------------------------------------------------------
SELECT
    x.value('@OrderNumber','VARCHAR(20)') AS OrderNumber,
    x.value('xs:date(@OrderDate)','DATETIME') AS OrderDate,
    x.value('@DeliveryDate cast as xs:dateTime ?','DATETIME') 
        AS DeliveryDate,
    x.value('@CustomerNumber','VARCHAR(20)') AS CustomerNumber,
    x.value('@CustomerName','VARCHAR(40)') AS CustomerName,
    x.value('@BillingCity','VARCHAR(20)') AS BillingCity,
    x.value('@BillingState','VARCHAR(10)') AS BillingState,
    x.value('@BillingZip','VARCHAR(5)') AS BillingZip,
    x.value('@BillingAddress','VARCHAR(50)') AS BillingAddress,
    x.value('@ShippingCity','VARCHAR(20)') AS ShippingCity,
    x.value('@ShippingState','VARCHAR(10)') AS ShippingState,
    x.value('@ShippingZip','VARCHAR(5)') AS ShippingZip,
    x.value('@ShippingAddress','VARCHAR(50)') AS ShippingAddress,
    x.value('@Terms','VARCHAR(50)') AS Terms,
    x.value('@ContactName','VARCHAR(40)') AS ContactName,
    x.value('@ContactTitle','VARCHAR(40)') AS ContactTitle,
    x.value('@ContactEmail','VARCHAR(50)') AS ContactEmail,
    x.value('@ContactPhone','VARCHAR(20)') AS ContactPhone,
    x.value('@ContactFax','VARCHAR(20)') AS ContactFax,
    x.value('@OrderNote','VARCHAR(50)') AS OrderNote,
    x.value('@InvoiceNote','VARCHAR(50)') AS InvoiceNote,
    x.value('@DiscountAmount','MONEY') AS DiscountAmount
FROM @x.nodes('Orders/Order') ord(x)

-- ------------------------------------------------------------------
-- Read Item details
-- ------------------------------------------------------------------
SELECT 
    i.value('@ItemNumber','VARCHAR(50)') AS ItemNumber,
    i.value('@Quantity','INT') AS Quantity,
    i.value('@Price','MONEY') AS Price
FROM @x.nodes('Orders/Order/Items/Item') itm(i)

SQL Server XML Tutorials - XQuery Tutorials

This series of XQuery tutorials are different from the other XQuery tutorial series I wrote previously. The previous tutorial tries to explain different usages of all XQuery methods and functions. This series of tutorial is bit different from the previous one. In this tutorial we will focus on processing several dozen different representation of the same XML document. We will see how we need to change our XQuery expressions when the structure of the document changes.

All the sample documents we will use in this series are posted here. Please refer the sample document index and find a document having the structure most closely matches with the document you are trying to process. Then you can look at the XQuery example that demonstrates how to process such an XML document and can write a version of the query that matches your document structure.

Each XQuery tutorial in this series will present 3 different versions of the XQuery example:

Reading information from an XML variable
Reading information from an XML Column
Reading information from an XML file

If there is a case/requirement that I have not handled in my examples, please let me know. I will be glad to add that to the tutorials.

Here is a list of all the XQuery tutorials in this series

SEE ALSO

SQL Server XML Tutorials
SQL Server XML Tutorials - Sample XML Documents
SQL Server XML Tutorials - XSD Tutorials (coming soon)
SQL Server XML Tutorials - XML Bulk Load Tutorials (coming soon)
SQL Server XML Tutorials - FOR XML Tutorials (coming soon)

Technorati Tags: FOR XML Tutorials,SQL Server XML Tutorials,XSD Tutorials,XML Bulk Load Tutorials,SQLXML Tutorials,XQuery Tutorials,XML Namespace

Sunday, October 19, 2008

SQL Server XML Tutorials

I had been trying to help many people with SQL Server XML related questions in various forums and UG meetings and heard a lot of questions repeatedly coming up from different people. I have a few other tutorials in this web site that discuss various SQL Server XML related topics. I am trying to build one more set of tutorials that tries to answer some of the most common XML processing questions.

Based upon the questions I hear from people, I think there are 4 major XML processing requirements people usually have.

Read values using XQuery from an XML document having a specific structure
Create an XSD Schema for an XML document having a specific structure
Generate an XML document having a specific structure, taking data from one or more tables
Load data from an XML document having a specific structure, into one or more tables

Note that I have highlighted "specific structure". I talked to many people in the past who know how to perform one or more of the above operations with XML documents having a certain structure. But they find it difficult when the XML structure changes. Some people can read values from attributes, but they find it hard when the values are presented in elements. Or people can easily load data using XML Bulk Load when there are two levels of XML hierarchy, but find it hard when the structure become more complicated.

So the structure of the XML is very important. This set of tutorials aims at helping people who need help to handle XML documents with different structures. I hope this tutorial will help them to be able to process XML documents with ANY structure.

How does this Tutorial Work?

What I am trying to do in this tutorial is to present the same data in different XML structures. I have created an XML document that contains the information of a Sales Order. So far I have created 20 different representation of the same data, that results in 20 different XML documents having different structure. Then we will perform all the 4 action mentioned above, on those different XML documents. We will read information from all those documents using XQuery, Load the data to one or more Tables, create XSD schemas for those documents and see how to generate documents with different structures using FOR XML.

Tutorials in this series