1 / 23

XML Data in MS SQL Server Query and Modification

XML Data in MS SQL Server Query and Modification. Steven Blundy, Duc Duong, Abhishek Mukherji, Bartlett Shappee CS561. Outline. Introduction XML Data Type Structure and storage Schema, validation Methods Publishing FORXML Queries. Approaches for XML integration to DB. Mid-tier

takara
Download Presentation

XML Data in MS SQL Server Query and Modification

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. XML Data in MS SQL Server Query and Modification Steven Blundy, Duc Duong, Abhishek Mukherji, Bartlett Shappee CS561

  2. Outline • Introduction • XML Data Type • Structure and storage • Schema, validation • Methods • Publishing • FORXML Queries

  3. Approaches for XML integration to DB • Mid-tier • Bi-directional XML view • Query view using XPath • Schema-driven approach for shredding • Server-side • rowset-to-XML aggregator for XML Publishing - FOR XML • Query-driven shredding mechanism- Open XML

  4. SQL SERVER 2005 XML Architecture

  5. XML Storage - Native • Checks Well Formedness • Validation is optional • XML Documents or Fragments

  6. SQL BLOB Allows for utilization BLOB optimizations Streaming Parsing Compression Unicode (UTF-16) Strings - UNTYPED Requires Conversion XML Schema - TYPED Encode to match schema Much more efficient XML Storage - DB

  7. XML Storage - The Numbers • Advantages of Binary Storage • 20 to 30% Size Reduction • Faster • Limitations • 2gb of stored binary per instance • Hierarchy is limited to 128 Levels

  8. Storage - Schema • Storage Optimization • Size • Processing • Uses the XML Infoset • Defined in an XMLSchemaCollection

  9. Validation - Schema • XML Schema Collection • Stores 1+ XML Schemas • Identified by Name Space • Not the Same as Constraints (No Business Logic) • Metadata Entity • Certain Type are Format Constrained • i.e. Date must use ISO 8601 format • Uses the XML Infoset

  10. Validation - Schema Collection CREATE XML SCHEMA COLLECTION myCollection AS '<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://myBooks" elementFormDefault="qualified" targetNamespace="http://myBooks"> <xsd:element name="bookstore" type="bookstoreType" /> <xsd:complexType name="bookstoreType"> <xsd:sequence maxOccurs="unbounded"> <xsd:element name="book" type="bookType" /> </xsd:sequence> </xsd:complexType> <xsd:complexType name="authorName"> <xsd:sequence> <xsd:element name="first-name" type="xsd:string" /> <xsd:element name="last-name" type="xsd:string" /> </xsd:sequence> </xsd:complexType> </xsd:schema>'

  11. Validation - How? • At Data Entry • Validness checked when typed data is inserted • Checking Data • Applied using Cast • Updated Schema • Does not Require re-validation

  12. XML DT Methods • All evaluate an XQuery • Methods • query() - returns XML output • exists() - checks if expression results in nodes • nodes() - returns XML DT values • value() - returns value as SQL DT • modify() - modify XML data

  13. query() & nodes() methods • Both take XQuery string • query() returns list of untyped XML nodes • Can be converted to strings • For SELECT output • nodes() returns list of typed XML nodes • All XML DT methods available • count(*) works • No converting to strings

  14. exist() & value() methods • exist() • returns true if XQuery returns any nodes • value() • Takes 2 params: an XQuery & a SQL DT • Converts xml value returned by XQuery to specified SQL type

  15. Example: value() SELECT data.value(‘(/bibliograph/book/[1]/title)[1]’, ‘NVARCHAR(255)’) AS Title FROM Test

  16. Example: value() w/ nodes() SELECT book.value(‘(title)[1]’, ‘NVARCHAR(255)’) AS Title FROM Test CROSS APPLY data.nodes(‘/bibliograph/book’) AS R(book)

  17. Example: exist() SELECT book.value(‘(title)[1]’, ‘NVARCHAR(255)’) AS Title FROM Test CROSS APPLY data.nodes(‘/bibliograph/book’) AS R(book) WHERE data.exist(‘/bibliograph/book’) = 1

  18. modify() method • Uses extended XQuery • insert, delete, and replace keywords • Used in SQL UPDATEs

  19. Example: modify() UPDATE docs SET xCol.modify(‘ insert <section num="2"> <title>Background</title> </section> after (/doc//section[@num=1])[1]')

  20. Example 2: modify() UPDATE XmlCatalog SET Document.modify (' declare namespace bk = "http://myBooks"; replace value of (/bk:bookstore/bk:book [@ISBN="1-861003-11-0"]/bk:price)[1] with 49.99')

  21. XML Publishing select CustomerID as "@CustomerID", City as "address/city", PostalCode as "address/zip", ContactName as "contact/name", Phone as "contact/phone", from Customers for xml path('Customer'), root('Doc')

  22. Additional Papers Used • XML Support in Microsoft SQL Server 2005 • Shankar Pal, Mark Fussell, and Irwin Dolobowsk • http://msdn2.microsoft.com/en-us/library/ms345117.aspx • XML Best Practices for Microsoft SQL Server 2005 • Shankar Pal, Vishesh Parikh, Vasili Zolotov, Leo Giakoumakis, Michael Rys • http://msdn2.microsoft.com/en-us/library/ms345115(d=printer).aspx

  23. Questions

More Related