XML%20Data%20in%20MS%20SQL%20Server%20Query%20and%20Modification

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
XML Storage - DB

• 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

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
• 'ltxsdschema xmlnsxsd"http//www.w3.org/2001/XML
  Schema"
• xmlns"http//myBooks"
• elementFormDefault"qualified"
• targetNamespace"http//myBooks"gt
• ltxsdelement name"bookstore"
  type"bookstoreType" /gt
• ltxsdcomplexType name"bookstoreType"gt
• ltxsdsequence maxOccurs"unbounded"gt
• ltxsdelement name"book" type"bookType" /gt
• lt/xsdsequencegt
• lt/xsdcomplexTypegt
• ltxsdcomplexType name"authorName"gt
• ltxsdsequencegt
• ltxsdelement name"first-name"
  type"xsdstring" /gt
• ltxsdelement name"last-name"
  type"xsdstring" /gt
• lt/xsdsequencegt
• lt/xsdcomplexTypegt
• lt/xsdschemagt'

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
Title
Design Patterns
16
Example value() w/ nodes()
SELECT book.value((title)1, NVARCHAR(255))
AS Title FROM Test CROSS APPLY data.nodes(/bibl
iograph/book) AS R(book)
Title
Design Patterns
All about XML

17
Example exist()
SELECT book.value((title)1, NVARCHAR(255))
AS Title FROM Test CROSS APPLY data.nodes(/bibl
iograph/book) AS R(book) WHERE
data.exist(/bibliograph/book) 1
Title
Design Patterns
All about XML

18
modify() method

• Uses extended XQuery
• insert, delete, and replace keywords
• Used in SQL UPDATEs

19
Example modify()

• UPDATE docs SET xCol.modify(
• insert
• ltsection num"2"gt
• lttitlegtBackgroundlt/titlegt
• lt/sectiongt
• after (/doc//section_at_num1)1')

20
Example 2 modify()

• UPDATE XmlCatalog
• SET Document.modify ('
• declare namespace bk "http//myBooks"
• replace value of (/bkbookstore/bkbook
  _at_ISBN"1-861003-11-0"/bkprice)1 with 49.99')

21
XML Publishing

• select CustomerID as "_at_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(
  dprinter).aspx

23
Questions