1 / 27

Module 3: Using XML

Module 3: Using XML. Overview. Retrieving XML by Using FOR XML Shredding XML by Using OPENXML Introducing XQuery Using the xml Data Type. Lesson 1: Retrieving XML by Using FOR XML. Introduction to the FOR XML Clause What Are RAW Mode Queries? What Are AUTO Mode Queries?

andra
Download Presentation

Module 3: Using XML

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Module 3:Using XML

  2. Overview • Retrieving XML by Using FOR XML • Shredding XML by Using OPENXML • Introducing XQuery • Using the xml Data Type

  3. Lesson 1: Retrieving XML by Using FOR XML • Introduction to the FOR XML Clause • What Are RAW Mode Queries? • What Are AUTO Mode Queries? • What Are EXPLICIT Mode Queries? • What Are PATH Mode Queries? • Syntax for Retrieving Nested XML • Practice: Using FOR XML

  4. Introduction to the FOR XML Clause • Extends SELECT syntax • Returns XML instead of rows and columns • Configurable to return attributes, elements, and schema • Benefits client applications that work with XML Converted to XML Database Server Client Application

  5. What Are RAW Mode Queries? • XML representation of a rowset • Contains either elements or attributes • Optional root element and row element name SELECT Cust.CustomerID CustID, CustomerType, SalesOrderID FROM Customer Cust JOIN SalesOrderHeader [Order] ON Cust.CustomerID = [Order].CustomerID ORDER BY Cust.CustomerID FOR XML RAW SELECT Cust.CustomerID CustID, CustomerType, SalesOrderID FROM Customer Cust JOIN SalesOrderHeader [Order] ON Cust.CustomerID = [Order].CustomerID ORDER BY Cust.CustomerID FOR XML RAW, ELEMENTS SELECT Cust.CustomerID CustID, CustomerType, SalesOrderID FROM Customer Cust JOIN SalesOrderHeader [Order] ON Cust.CustomerID = [Order].CustomerID ORDER BY Cust.CustomerID FOR XML RAW('Order'), ROOT('Orders') <row CustID="1" CustomerType="S" SalesOrderID="43860"/> <row CustID="1" CustomerType="S" SalesOrderID="44501"/> ... <row> <CustID>1</CustID> <CustomerType>S</CustomerType> <SalesOrderID>43860</SalesOrderID> </row>... <Orders> <Order><CustID>1</CustID><CustomerType>S</... </Order> ... </Orders>

  6. What Are AUTO Mode Queries? • XML representation of data entities • Nest data based on join precedence • Can use options such as ELEMENTS and ROOT SELECT Cust.CustomerID CustID, CustomerType, SalesOrderID FROM Customer Cust JOIN SalesOrderHeader [Order] ON Cust.CustomerID = [Order].CustomerID ORDER BY Cust.CustomerID FOR XML AUTO <Cust CustID="1" CustomerType="S"> <Order SalesOrderID="43860" /> <Order SalesOrderID="44501" /> ... </Cust> <Cust CustID="2" CustomerType="S"> ...

  7. What Are EXPLICIT Mode Queries? • Tabular representations of XML documents • Allow complete control of XML format SELECT 1 AS Tag, NULL AS Parent, SalesOrderID AS [Invoice!1!InvoiceNo], OrderDate AS [Invoice!1!Date!Element] FROM SalesOrderHeader FOR XML EXPLICIT SELECT 1 AS Tag, NULL AS Parent, SalesOrderID AS [Invoice!1!InvoiceNo], OrderDate AS [Invoice!1!Date!Element] FROM SalesOrderHeader FOR XML EXPLICIT Attribute <Invoice InvoiceNo="43659"> <Date>2001-07-01T00:00:00</Date> </Invoice> <Invoice InvoiceNo="43660">... <Invoice InvoiceNo="43659"> <Date>2001-07-01T00:00:00</Date> </Invoice> <Invoice InvoiceNo="43660">... Element

  8. What Are PATH Mode Queries? • Use XPath to specify XML format • Allow creation of nested data • Easier to use than EXPLICIT mode SELECT EmployeeID "@EmpID", FirstName "EmpName/First", LastName "EmpName/Last" FROM Person.Contact INNER JOIN Employee ON Person.Contact.ContactID = Employee.ContactID FOR XML PATH <row EmpID="1"> <EmpName> <First>Guy</First> <Last>Gilbert</Last> </EmpName> </row> ...

  9. Syntax for Retrieving Nested XML • AUTO mode produces only attributes or elements • Use inner FOR XML with TYPE clause to return xml data type • Combine EXPLICIT mode with UNION ALL SELECT Cust.CustomerID, CustomerType, SalesOrderID, Status FROM Customer Cust JOIN SalesOrderHeader [Order] ON Cust.CustomerID = [Order].CustomerID ORDER BY Cust.CustomerID FOR XML AUTO SELECT Cust.CustomerID, CustomerType, SalesOrderID, Status FROM Customer Cust JOIN SalesOrderHeader [Order] ON Cust.CustomerID = [Order].CustomerID ORDER BY Cust.CustomerID FOR XML AUTO, ELEMENTS SELECT Name CategoryName, (SELECT Name SubCategoryName FROM ProductSubCategory SubCategory WHERE SubCategory.ProductCategoryID = Category.ProductCategoryID FOR XML AUTO, TYPE, ELEMENTS) FROM ProductCategory Category FOR XML AUTO SELECT 1 AS Tag, NULL AS Parent, ... FROM SalesOrderHeader UNION ALL SELECT 2 AS Tag, 1 AS Parent, ... FROM SalesOrderDetail OD JOIN ... FOR XML EXPLICIT <Cust> <CustomerID>1</CustomerID> <CustomerType>S</CustomerType> <Order> <SalesOrderID>43860</SalesOrderID> <Status>5</Status>... <Cust CustomerID="1" CustomerType="S"> <Order SalesOrderID="43860" Status="5"/> <Order SalesOrderID="44501" Status="5"/> ... </Cust> <Invoice InvoiceNo="43659"> <Date>2001-07-01T00:00:00</Date> <LineItem ProductID="709">Bike Socks, M</LineItem> <LineItem ProductID="711">Helmet, Blue</LineItem> </Invoice>... <Category CategoryName="Accessories"> <SubCategory> <SubCategoryName>Bike Racks</SubCategoryName> </SubCategory>...

  10. Practice: Using FOR XML In this practice, you will: • Retrieve XML in RAW mode • Retrieve XML in AUTO mode • Retrieve XML in EXPLICIT mode • Retrieve XML in PATH mode

  11. Lesson 2: Shredding XML by Using OPENXML • Overview of Shredding XML Data • Stored Procedures for Managing In-Memory Node Trees • OPENXML Syntax • Syntax for Working With XML Namespaces • Practice: Using OPENXML to Shred XML

  12. Overview of Shredding XML Data Create internal tree representation by using sp_xml_preparedocument 2 Use sp_xml_removedocumentto clean up memory tree 5 Use OPENXML to retrieve rowset 3 Process (or shred) the data into tables 4 XML documentreceived from client 1

  13. Stored Procedures for Managing In-Memory Node Trees • Create tree by using sp_xml_preparedocument • Free memory by using sp_xml_removedocument CREATE PROC ProcessOrder @doc xml -- xml data AS -- Declare document handle DECLARE @hdoc integer -- Create memory tree EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc -- Process Document -- Remove memory tree EXEC sp_xml_removedocument @hdoc

  14. OPENXML Syntax <Customer CustomerID="1" CustomerType="S"> <Order SalesOrderID="43860" Status="5" OrderDate="2001-08-01T00:00:00"> <OrderDetail ProductID="761" Quantity="2"/> <OrderDetail ProductID="770" Quantity="1"/> </Order> </Customer> rowpattern identifies node level Uses attributes as default SELECT * FROM OPENXML (@idoc, '/Customer/Order/OrderDetail', 1) WITH (CustomerID int '../../@CustomerID', OrderID int '../@SalesOrderID', OrderDate datetime '../@OrderDate', ProdID int '@ProductID', Quantity int) From Customer element From Order element Defaults to Quantity attribute From OrderDetail element

  15. Syntax for Working With XML Namespaces • sp_xml_preparedocument accepts namespaces • Use namespace prefix in all XPath expressions <Customer xmlns="urn:AW_NS" xmlns:o="urn:AW_OrderNS" CustomerID="1" CustomerType="S"> <o:Order SalesOrderID="43860" Status="5" OrderDate="2001-08-01T00:00:00"> <o:OrderDetail ProductID="761" Quantity="2"/> <o:OrderDetail ProductID="770" Quantity="1"/> </o:Order> </Customer> EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, <ROOT xmlns:rootNS="urn:AW_NS" xmlns:orderNS="urn:AW_OrderNS"/>' SELECT * FROM OPENXML (@idoc, '/rootNS:Customer/orderNS:Order/orderNS:OrderDetail') WITH...

  16. Practice: Using OPENXML to Shred XML In this practice, you will: • Use the OPENXML function • Shred XML by using elements only • Shred XML by using attributes or elements • Shred XML by using a colpattern parameter

  17. Lesson 3: Introducing XQuery • What Is XQuery? • XQuery Basics • XQuery Expressions

  18. What Is XQuery? • Query language to identify nodes in XML /InvoiceList/Invoice[@InvoiceNo=1000] • FLWOR statements

  19. XQuery Basics Comments (: Comment text :) Sequences and QNames • Result of an XQuery expression is a sequence • All identifiers are QNames Operators if-then-else • Arithmetic comparison • General comparison • Value comparison • Node comparison • Node order comparison • Logical if ( $A eq $B ) then <result>A</result> else <result>B</result>

  20. XQuery Expressions Primary expressions Path expressions • Relative • Literals • Variable references • Function calls child::Address/child::Country • Absolute /Address/Country Sequence expressions • Construct, filter and combine sequences declare @x xml set @x = '<root> <abc></abc> <abc attrAbc="1"></abc> <abc attrAbc="2"></abc> </root>' SELECT @x.query('/root/abc[attrAbc]')

  21. Lesson 4: Using the xml Data Type • What Is the xml Data Type? • The query, value, and exist Methods • The modify Method • The nodes Method • Practice: Using the xml Data Type

  22. What Is the xml Data Type? • Native data type for XML • Internal storage structure for XML InfoSet • Use for tables, variables, or parameters • Exposes methods to query and modify XML -- usage within table definition CREATE TABLE NewTable ( Col1 int primary key, Col2 xml ) -- usage as local variable declare @data xml -- usage as parameter to stored procedure CREATE PROCEDURE SaveData(@doc xml) AS ...

  23. The query, value, and exist Methods • Usequery to return untyped XML • Usevalue to return a scalar value SELECT xmlCol.query( '<InvoiceNumbers> { for $i in /InvoiceList/Invoice return <InvoiceNo> {number($i/@InvoiceNo)} </InvoiceNo> } </InvoiceNumbers>') • Useexist to check for the existence of a specified value • Bind relational columns and variables SELECT xmlCol.value( '(/InvoiceList/Invoice/@InvoiceNo)[1]', 'int') SELECT xmlCol.exist( '/InvoiceList/Invoice[@InvoiceNo=1000]' ) SELECT Invoices.query( '<Store> {sql:column("StoreName")} </Store>')

  24. The modify Method • insertadds child nodes or siblings to an XML document • replacevalue of updates a node in the XML document • deleteremoves a node from the XML document SET @xmlDoc.modify( 'insert element salesperson {"Bill"} as first into (/InvoiceList/Invoice)[1]') SET xmlCol.modify( replace value of (/InvoiceList/Invoice/SalesPerson/text())[1] with "Ted"') SET @xmlDoc.modify( 'delete (/InvoiceList/Invoice/SalesPerson)[1]')

  25. The nodes Method • Shreds xml variables into relational data • Requires the APPLY operator with xml columns SELECT nCol.value('@ProductID', 'int') Product, nCol.value('@Quantity', 'int') Qty FROM @xmlOrder.nodes('/Order/LineItem') AS nTable(nCol) SELECT nCol.value('../@OrderID[1]', 'int') ID, nCol.value('@ProductID[1]', 'int') Prod FROM Orders CROSS APPLY OrderDoc.nodes('/Order/LineItem') AS nTable(nCol)

  26. Practice: Using the xml Data Type In this practice, you will: • Use the xml data type • Use the query, value, and exist methods • Bind relational columns • Use the modify method to insert, update, and delete XML • Use the nodes method

  27. Lab: Working With XML • Exercise 1: Mapping Relational Data and XML • Exercise 2: Storing XML Natively in the Database • Exercise 3: Using XQuery With xml Methods

More Related