XML Data in MS SQL Server Query and

XML Data in MS SQL Server Query and Modification Steven Blundy, Duc Duong, Abhishek Mukherji, Bartlett Shappee CS 561

Outline Introduction n XML Data Type n ¨ Structure and storage ¨ Schema, validation ¨ Methods n Publishing ¨ FORXML Queries

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

SQL SERVER 2005 XML Architecture

XML Storage - Native n Checks Well Formedness ¨ Validation n is optional XML Documents or Fragments

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

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

Storage - �Schema n Storage Optimization ¨ Size ¨ Processing Uses the XML Infoset n Defined in an XMLSchema. Collection n

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

Validation - �Schema Collection CREATE XML SCHEMA COLLECTION my. Collection AS '<xsd: schema xmlns: xsd="http: //www. w 3. org/2001/XMLSchema" xmlns="http: //my. Books" element. Form. Default="qualified" target. Namespace="http: //my. Books"> <xsd: element name="bookstore" type="bookstore. Type" /> <xsd: complex. Type name="bookstore. Type"> <xsd: sequence max. Occurs="unbounded"> <xsd: element name="book" type="book. Type" /> </xsd: sequence> </xsd: complex. Type> <xsd: complex. Type name="author. Name"> <xsd: sequence> <xsd: element name="first-name" type="xsd: string" /> <xsd: element name="last-name" type="xsd: string" /> </xsd: sequence> </xsd: complex. Type> </xsd: schema>'

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

XML DT Methods All evaluate an XQuery n Methods n ¨ 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

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

exist() & value() methods n exist() ¨ returns n 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

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

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

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 Title Design Patterns All about XML …

modify() method n Uses extended XQuery ¨ insert, n delete, and replace keywords Used in SQL UPDATEs

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

Example 2: modify() UPDATE Xml. Catalog SET Document. modify (' declare namespace bk = "http: //my. Books"; replace value of (/bk: bookstore/bk: book [@ISBN="1 -861003 -110"]/bk: price)[1] with 49. 99')

XML Publishing select Customer. ID as "@Customer. ID", City as "address/city", Postal. Code as "address/zip", Contact. Name as "contact/name", Phone as "contact/phone", from Customers for xml path('Customer'), root('Doc')

Additional Papers Used n XML Support in Microsoft SQL Server 2005 Shankar Pal, Mark Fussell, and Irwin Dolobowsk ¨ http: //msdn 2. microsoft. com/en-us/library/ms 345117. aspx ¨ n XML Best Practices for Microsoft SQL Server 2005 Shankar Pal, Vishesh Parikh, Vasili Zolotov, Leo Giakoumakis, Michael Rys ¨ http: //msdn 2. microsoft. com/enus/library/ms 345115(d=printer). aspx ¨

Questions