Posts
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 21 for the example in this post. Here is a list of all the different XML documents we will use throughout this series of tutorials.
This example is slightly different from the other examples we saw previously. The major difference is that, all the sample documents we saw previously had information of only one order. This example shows how to process the data if we have more than one order information in the XML document. One significant change added to the code is an extra CROSS APPLY that joins the details part with the header node, so that we can retrieve the order number along with item details. For example:
SELECT o.value('@Number','VARCHAR(10)') AS OrderNumber, i.value('@ItemNumber','VARCHAR(10)') AS ItemNumber, i.value('@Quantity','INT') AS Quantity, i.value('@Price','MONEY') AS Price FROM @x.nodes('/Orders/Order') ord(o) CROSS APPLY o.nodes('Items/Item') itm(i) /* OrderNumber ItemNumber Quantity Price ----------- ---------- ----------- --------------------- 20001 FB001923 12 18.25 20001 SG060020 80 12.75 20001 FB019090 24 6.00 20002 FB001923 5 18.25 20002 SG060020 25 12.75 20002 FB019090 15 6.00 */
Note that the above code retrieves order number from the order header node and order details from the detail node. The nodes are linked using a CROSS apply. Let us now see the complete source code listing.
Reading information from an XML variable
-- ------------------------------------------------------------------ -- Declare an XML variable and assign value -- ------------------------------------------------------------------ DECLARE @x XML SELECT @x = ' <Orders> <Order Number="20001" Date="2008-01-01Z"> <DeliveryDate>2008-01-20T08:00:00-08:00</DeliveryDate> <Customer Number="GREAL"> <Name>Great Lakes Food Market</Name> <Billing City="Eugene" State="OR" Zip="97403"> <Address>2732 Baker Blvd.</Address> </Billing> <Shipping City="Eugene" State="OR" Zip="97403"> <Address>2732 Baker Blvd.</Address> </Shipping> <Terms>30 Days Credit</Terms> <Contact Name="Howard Snyder" Title="Purchase Manager"> <Email>hsnyder@greatlakes.com</Email> <Phone>(503) 555-7555</Phone> <Fax>(503) 555-2376</Fax> </Contact> </Customer> <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> <Order Number="20002" Date="2008-01-02Z"> <DeliveryDate>2008-01-21T08:00:00-08:00</DeliveryDate> <Customer Number="GREAL"> <Name>Great Lakes Food Market</Name> <Billing City="Eugene" State="OR" Zip="97403"> <Address>2732 Baker Blvd.</Address> </Billing> <Shipping City="Eugene" State="OR" Zip="97403"> <Address>2732 Baker Blvd.</Address> </Shipping> <Terms>30 Days Credit</Terms> <Contact Name="Howard Snyder" Title="Purchase Manager"> <Email>hsnyder@greatlakes.com</Email> <Phone>(503) 555-7555</Phone> <Fax>(503) 555-2376</Fax> </Contact> </Customer> <Items> <Item ItemNumber="FB001923" Quantity="5" Price="18.25" /> <Item ItemNumber="SG060020" Quantity="25" Price="12.75" /> <Item ItemNumber="FB019090" Quantity="15" 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('@Number[1]','VARCHAR(20)') AS OrderNumber, x.value('xs:date(@Date[1])','DATETIME') AS OrderDate, x.value('DeliveryDate[1] cast as xs:dateTime ?','DATETIME') AS DeliveryDate, x.value('(Customer/@Number)[1]','VARCHAR(20)') AS CustomerNumber, x.value('(Customer/Name)[1]','VARCHAR(40)') AS CustomerName, x.value('(Customer/Billing/@City)[1]','VARCHAR(20)') AS BillingCity, x.value('(Customer/Billing/@State)[1]','VARCHAR(10)') AS BillingState, x.value('(Customer/Billing/@Zip)[1]','VARCHAR(5)') AS BillingZip, x.value('(Customer/Billing/Address)[1]','VARCHAR(50)') AS BillingAddress, x.value('(Customer/Shipping/@City)[1]','VARCHAR(20)') AS ShippingCity, x.value('(Customer/Shipping/@State)[1]','VARCHAR(10)') AS ShippingState, x.value('(Customer/Shipping/@Zip)[1]','VARCHAR(5)') AS ShippingZip, x.value('(Customer/Shipping/Address)[1]','VARCHAR(50)') AS ShippingAddress, x.value('(Customer/Terms)[1]','VARCHAR(50)') AS Terms, x.value('(Customer/Contact/@Name)[1]','VARCHAR(40)') AS ContactName, x.value('(Customer/Contact/@Title)[1]','VARCHAR(40)') AS ContactTitle, x.value('(Customer/Contact/Email)[1]','VARCHAR(50)') AS ContactEmail, x.value('(Customer/Contact/Phone)[1]','VARCHAR(20)') AS ContactPhone, x.value('(Customer/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 o.value('@Number','VARCHAR(10)') AS OrderNumber, i.value('@ItemNumber','VARCHAR(10)') AS ItemNumber, i.value('@Quantity','INT') AS Quantity, i.value('@Price','MONEY') AS Price FROM @x.nodes('/Orders/Order') ord(o) CROSS APPLY o.nodes('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 Number="20001" Date="2008-01-01Z"> <DeliveryDate>2008-01-20T08:00:00-08:00</DeliveryDate> <Customer Number="GREAL"> <Name>Great Lakes Food Market</Name> <Billing City="Eugene" State="OR" Zip="97403"> <Address>2732 Baker Blvd.</Address> </Billing> <Shipping City="Eugene" State="OR" Zip="97403"> <Address>2732 Baker Blvd.</Address> </Shipping> <Terms>30 Days Credit</Terms> <Contact Name="Howard Snyder" Title="Purchase Manager"> <Email>hsnyder@greatlakes.com</Email> <Phone>(503) 555-7555</Phone> <Fax>(503) 555-2376</Fax> </Contact> </Customer> <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> <Order Number="20002" Date="2008-01-02Z"> <DeliveryDate>2008-01-21T08:00:00-08:00</DeliveryDate> <Customer Number="GREAL"> <Name>Great Lakes Food Market</Name> <Billing City="Eugene" State="OR" Zip="97403"> <Address>2732 Baker Blvd.</Address> </Billing> <Shipping City="Eugene" State="OR" Zip="97403"> <Address>2732 Baker Blvd.</Address> </Shipping> <Terms>30 Days Credit</Terms> <Contact Name="Howard Snyder" Title="Purchase Manager"> <Email>hsnyder@greatlakes.com</Email> <Phone>(503) 555-7555</Phone> <Fax>(503) 555-2376</Fax> </Contact> </Customer> <Items> <Item ItemNumber="FB001923" Quantity="5" Price="18.25" /> <Item ItemNumber="SG060020" Quantity="25" Price="12.75" /> <Item ItemNumber="FB019090" Quantity="15" 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('@Number[1]','VARCHAR(20)') AS OrderNumber, x.value('xs:date(@Date[1])','DATETIME') AS OrderDate, x.value('DeliveryDate[1] cast as xs:dateTime ?','DATETIME') AS DeliveryDate, x.value('(Customer/@Number)[1]','VARCHAR(20)') AS CustomerNumber, x.value('(Customer/Name)[1]','VARCHAR(40)') AS CustomerName, x.value('(Customer/Billing/@City)[1]','VARCHAR(20)') AS BillingCity, x.value('(Customer/Billing/@State)[1]','VARCHAR(10)') AS BillingState, x.value('(Customer/Billing/@Zip)[1]','VARCHAR(5)') AS BillingZip, x.value('(Customer/Billing/Address)[1]','VARCHAR(50)') AS BillingAddress, x.value('(Customer/Shipping/@City)[1]','VARCHAR(20)') AS ShippingCity, x.value('(Customer/Shipping/@State)[1]','VARCHAR(10)') AS ShippingState, x.value('(Customer/Shipping/@Zip)[1]','VARCHAR(5)') AS ShippingZip, x.value('(Customer/Shipping/Address)[1]','VARCHAR(50)') AS ShippingAddress, x.value('(Customer/Terms)[1]','VARCHAR(50)') AS Terms, x.value('(Customer/Contact/@Name)[1]','VARCHAR(40)') AS ContactName, x.value('(Customer/Contact/@Title)[1]','VARCHAR(40)') AS ContactTitle, x.value('(Customer/Contact/Email)[1]','VARCHAR(50)') AS ContactEmail, x.value('(Customer/Contact/Phone)[1]','VARCHAR(20)') AS ContactPhone, x.value('(Customer/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 o.value('@Number','VARCHAR(10)') AS OrderNumber, 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') ord(o) CROSS APPLY o.nodes('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\order21.xml', SINGLE_BLOB) AS x -- ------------------------------------------------------------------ -- Read Order Header Information -- ------------------------------------------------------------------ SELECT x.value('@Number[1]','VARCHAR(20)') AS OrderNumber, x.value('xs:date(@Date[1])','DATETIME') AS OrderDate, x.value('DeliveryDate[1] cast as xs:dateTime ?','DATETIME') AS DeliveryDate, x.value('(Customer/@Number)[1]','VARCHAR(20)') AS CustomerNumber, x.value('(Customer/Name)[1]','VARCHAR(40)') AS CustomerName, x.value('(Customer/Billing/@City)[1]','VARCHAR(20)') AS BillingCity, x.value('(Customer/Billing/@State)[1]','VARCHAR(10)') AS BillingState, x.value('(Customer/Billing/@Zip)[1]','VARCHAR(5)') AS BillingZip, x.value('(Customer/Billing/Address)[1]','VARCHAR(50)') AS BillingAddress, x.value('(Customer/Shipping/@City)[1]','VARCHAR(20)') AS ShippingCity, x.value('(Customer/Shipping/@State)[1]','VARCHAR(10)') AS ShippingState, x.value('(Customer/Shipping/@Zip)[1]','VARCHAR(5)') AS ShippingZip, x.value('(Customer/Shipping/Address)[1]','VARCHAR(50)') AS ShippingAddress, x.value('(Customer/Terms)[1]','VARCHAR(50)') AS Terms, x.value('(Customer/Contact/@Name)[1]','VARCHAR(40)') AS ContactName, x.value('(Customer/Contact/@Title)[1]','VARCHAR(40)') AS ContactTitle, x.value('(Customer/Contact/Email)[1]','VARCHAR(50)') AS ContactEmail, x.value('(Customer/Contact/Phone)[1]','VARCHAR(20)') AS ContactPhone, x.value('(Customer/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 o.value('@Number','VARCHAR(10)') AS OrderNumber, i.value('@ItemNumber','VARCHAR(50)') AS ItemNumber, i.value('@Quantity','INT') AS Quantity, i.value('@Price','MONEY') AS Price FROM @x.nodes('/Orders/Order') ord(o) CROSS APPLY o.nodes('Items/Item') itm(i)
SEE ALSO
- Previous XQuery Tutorial
- Next XQuery Tutorial
- All XQuery Tutorials in this series
- SQL Server XML Tutorials
0 comments:
Post a Comment