Subscribe To

Subscribe to SQL Server and XML by Email
Labels

Tuesday, October 21, 2008

SQL Server XML Tutorials - XQuery Tutorial 7

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:

  1. Reading information from an XML variable
  2. Reading information from an XML Column
  3. 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)
 

SEE ALSO

 

0 comments:

Post a Comment

About Me
Jacob Sebastian
Tutorials
* XQuery Tutorials
My Links
SQL Server White Papers
SQL Server 2008
My Articles
XML Workshop RSS Feed
Contact me
Readers
Free Hit Counter
Web Site Hit Counters
SQL Server Bloggers
Blog Directories
blogarama - the blog directory Programming Blogs - BlogCatalog Blog Directory
 
Copyright Jacob Sebastian