Beyond Relational: SQL Server XML Tutorials

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 7 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-20T08:00: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">
    <Contact Name="Howard Snyder" Title="Purchase Manager"
             Email="hsnyder@greatlakes.com" 
             Phone="(503) 555-7555" Fax="(503) 555-2376"/>
    <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>
    <OrderNote>Delivery needed before 8 AM</OrderNote>
    <InvoiceNote>
      Adjust the previous credit note with this invoice
    </InvoiceNote>
    <DiscountAmount>300</DiscountAmount>
  </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('(Contact/@Name)[1]','VARCHAR(40)') AS ContactName,
    x.value('(Contact/@Title)[1]','VARCHAR(40)') AS ContactTitle,
    x.value('(Contact/@Email)[1]','VARCHAR(50)') AS ContactEmail,
    x.value('(Contact/@Phone)[1]','VARCHAR(20)') AS ContactPhone,
    x.value('(Contact/@Fax)[1]','VARCHAR(20)') AS ContactFax,
    x.value('OrderNote[1]','VARCHAR(50)') AS OrderNote,
    x.value('InvoiceNote[1]','VARCHAR(50)') AS InvoiceNote,
    x.value('DiscountAmount[1]','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-20T08:00: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">
    <Contact Name="Howard Snyder" Title="Purchase Manager"
             Email="hsnyder@greatlakes.com" 
             Phone="(503) 555-7555" Fax="(503) 555-2376"/>
    <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>
    <OrderNote>Delivery needed before 8 AM</OrderNote>
    <InvoiceNote>
      Adjust the previous credit note with this invoice
    </InvoiceNote>
    <DiscountAmount>300</DiscountAmount>
  </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('(Contact/@Name)[1]','VARCHAR(40)') AS ContactName,
    x.value('(Contact/@Title)[1]','VARCHAR(40)') AS ContactTitle,
    x.value('(Contact/@Email)[1]','VARCHAR(50)') AS ContactEmail,
    x.value('(Contact/@Phone)[1]','VARCHAR(20)') AS ContactPhone,
    x.value('(Contact/@Fax)[1]','VARCHAR(20)') AS ContactFax,
    x.value('OrderNote[1]','VARCHAR(50)') AS OrderNote,
    x.value('InvoiceNote[1]','VARCHAR(50)') AS InvoiceNote,
    x.value('DiscountAmount[1]','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\order7.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('(Contact/@Name)[1]','VARCHAR(40)') AS ContactName,
    x.value('(Contact/@Title)[1]','VARCHAR(40)') AS ContactTitle,
    x.value('(Contact/@Email)[1]','VARCHAR(50)') AS ContactEmail,
    x.value('(Contact/@Phone)[1]','VARCHAR(20)') AS ContactPhone,
    x.value('(Contact/@Fax)[1]','VARCHAR(20)') AS ContactFax,
    x.value('OrderNote[1]','VARCHAR(50)') AS OrderNote,
    x.value('InvoiceNote[1]','VARCHAR(50)') AS InvoiceNote,
    x.value('DiscountAmount[1]','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)

Beyond Relational

Tuesday, October 21, 2008

SQL Server XML Tutorials - XQuery Tutorial 7

0 comments:

Post a Comment