1 / 46

Module 5: Using XML

Module 5: Using XML. Module 5: Using XML. Using the XML Data Type Retrieving XML by Using FOR XML Shredding XML by Using OPENXML Introducing XQuery Creating XML Indexes Implementing XML Schemas. Lesson 1: Using the XML Data Type. What Is XML? What Is the XML Data Type?

nira
Télécharger la présentation

Module 5: 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 5:Using XML

  2. Module 5: Using XML • Using the XML Data Type • Retrieving XML by Using FOR XML • Shredding XML by Using OPENXML • Introducing XQuery • Creating XML Indexes • Implementing XML Schemas

  3. Lesson1: Using the XML Data Type • What Is XML? • What Is the XML Data Type? • The Query, Value, and Exist Methods • The Modify Method • The Nodes Method

  4. What Is XML? • XML is a plain-text, Unicode-based meta-language • Represents both structured and semi-structured data • Not tied to any programming language, OS, or vendor A sample XML document for a music store order: <?xml version="1.0" encoding="iso-8859-1" ?> <?xml-stylesheethref="orders.xsl"?> <order id="ord123456"> <customer id="cust0921"> <first-name>Dare</first-name> <last-name>Obasanjo</last-name> <address> <street>One Microsoft Way</street> <city>Redmond</city> <state>WA</state> <zip>98052</zip> </address> </customer> </order>

  5. What Is the XML Data Type? • Native data type for XML • Lets you store XML documents and fragments • Used 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 ...

  6. The Query, Value, and Exist Methods • Use query to return untyped XML • Use value to return a scalar value SELECT xmlCol.query( '<InvoiceNumbers> { for $i in /InvoiceList/Invoice return <InvoiceNo> {number($i/@InvoiceNo)} </InvoiceNo> } </InvoiceNumbers>') • Use exist to check existence of 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>')

  7. The Modify Method • Insert adds child nodes to an XML document • Replace value of updates node in an XML doc • Delete removes 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]')

  8. 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)

  9. Demonstration: Using the XML Data Type In this demonstration, you will see how to: • 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

  10. Lesson2: 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

  11. 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

  12. 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.CustomerIDCustID, 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>

  13. What Are AUTO Mode Queries? XML representation of data entities Execute this query: SELECT Cust.CustomerID, OrderHeader.CustomerID, OrderHeader.SalesOrderID, OrderHeader.Status, Cust.CustomerType FROM Sales.CustomerCust, Sales.SalesOrderHeaderOrderHeader WHERE Cust.CustomerID = OrderHeader.CustomerID ORDER BY Cust.CustomerID FOR XML AUTO This is the partial result: <CustCustomerID="1" CustomerType="S"> <OrderHeaderCustomerID="1" SalesOrderID="43860" Status="5" /> <OrderHeaderCustomerID="1" SalesOrderID="44501" Status="5" /> <OrderHeaderCustomerID="1" SalesOrderID="45283" Status="5" /> <OrderHeaderCustomerID="1" SalesOrderID="46042" Status="5" /> </Cust> ...

  14. 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

  15. What Are PATH Mode Queries? • Use XML Path Language (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> ...

  16. 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>...

  17. Demonstration: Using FOR XML In this demonstration, you will see how to: • Retrieve XML in RAW mode • Retrieve XML in AUTO mode • Retrieve XML in EXPLICIT mode • Retrieve XML in PATH mode

  18. Lesson3: 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

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

  20. 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

  21. OPENXML Syntax ColPattern identifies SalesOrderID, Status, and OrderDate <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> Uses attributes as default rowpattern identifies node level 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

  22. 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') ...

  23. Demonstration: Using OPENXML to Shred XML In this demonstration, you will see how to: • Use the OPENXML function • Shred XML by using elements only • Shred XML by using attributes or elements • Shred XML by using a ColPattern parameter

  24. Lab: Using XML • Exercise 1: Mapping Relational Data and XML • Exercise 2: Storing XML Natively in the Database Logon information Estimated time: 30 minutes

  25. Lab Scenario Adventure Works wants to improve its database systems be expanding its use of XML data storage. It has decided to focus on its Sales and Human Resources departments. After discussing it with the department heads, the IT department has decided to expand the users of XML data for sales order tracking and job candidate historical information.

  26. Lab Review • In Exercise 1, what does the FOR XML clause do? • In Exercise 2, what is the purpose of the XML Schema? • In Exercise 2, what does the XML Schema Collection do? • In Exercise 2, why does the top INSERT code section of TestXMLSchema.sql fail?

  27. Lesson4: Introducing XQuery • What Is XQuery? • XQuery Basics • XQuery Expressions

  28. What Is XQuery? • Query language to identify nodes in XML • Can query structured or semi-structured XML /InvoiceList/Invoice[@InvoiceNo=1000] • FLWOR statements

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

  30. 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> <abcattrAbc="1"></abc> <abcattrAbc="2"></abc> </root>' SELECT @x.query('/root/abc[attrAbc]')

  31. Demonstration: Using XQuery Expressions In this demonstration, you will see how to: • Declare namespace and retrieve XML data

  32. Lesson5: Creating XML Indexes • What Are XML Indexes? • What Are the Benefits of XML Indexes? • Types of XML Indexes

  33. What Are XML Indexes? • XML indexes can be created on xml data type columns • They index all tags, values and paths over the XML instances in the column and benefit query performance XML indexes fall into the following categories: • Primary XML index • Secondary XML index

  34. What Are the Benefits of XML Indexes? Your application may benefit from an XML index when: • Queries on XML columns are common in your workload • Your XML values are relatively large and the retrieved parts are relatively small

  35. Types of XML Indexes XML indexes fall into the following categories: • Primary XML index • Index scan or seek on the primary XML index • Secondary XML index • Index scan or seek on node table's PATH index • Index scan or seek on node table's PROPERTY index • Index scan or seek on node table's VALUE index

  36. Demonstration: Creating XML Indexes In this demonstration, you will see how to: • Record the performance statistics for an XML query • Create a primary XML index • Create a secondary XML index • Investigate the performance statistics for an XML query

  37. Lesson6: Implementing XML Schemas • What Are XML Schemas? • XML Schema Validation • What Is an XML Schema Collection? • What Is Typed and Untyped XML

  38. What Are XML Schemas? • XML Schema is an XML-based alternative to Document Type Definition (DTD) • An XML schema describes the structure of XML document • The XML Schema language is also called XML Schema Definition (XSD) An XML schema provides the following: • Validation constraints • SQL Server validates the instance • Data type information • Schemas provide info about types of attributes and elements

  39. XML Schema Validation New XML Schema Validation Support in SQL 2008:

  40. What Is an XML Schema Collection? • SQL Server provides native storage of XML data with the xml data type • You can optionally associate XSD schemas with an xml type through an XML schema collection The XML schema collection does the following: • Stores the imported XML schemas • Validates XML instances • Types the XML data as it is stored in the database

  41. What Is Typed and Untyped XML? • Use untyped XML data type in the following situations: • You do not have a schema for your XML data • You have schemas, but don’t want server to validate data • Use typed XML data type in the following situations: • You have schemas and want server to validate XML data • You want to take advantage of storage and query optimizations based on type information • You want to take advantage of type information during compilation of your queries

  42. Demonstration: Using Typed XML In this demonstration, you will see how to: • Create an XML schema collection • Create a typed XML column • Insert valid and invalid data into a typed XML column

  43. Lab: Using XML • Exercise 3: Using XQuery with XML Methods • Exercise 4: Creating XML Indexes Logon information Estimated time: 30 minutes

  44. Lab Scenario The IT department has found that their initial expansion of XML document storage for the Human Resources department has successful. They want to expand their testing of this new system, and see if it can be used to manipulate individual elements of a resume stored as an XML document. Because of the success with the Sales and Human Resources department, the IT department has decided to expand their use of XML data infrastructure to the Production department.

  45. Lab Review • In Exercise 3, what does the query() modify method do? • In Exercise 4, what will the XML Indexes do? • In Exercise 4, what does the fill factor do?

  46. Module Review and Takeaways • Review Questions • Best Practices • Tools

More Related