XQuery in Relational Database Systems

1
XQuery in Relational Database Systems
Speaker Please 1. fill in your session code,
and2. copy the appropriate Virtual Track
icon(s) onto the title slide .

• Michael Rys
• Program Manager SQL Server XML Technologies
• Microsoft Corporation
• mrys_at_microsoft.com

2
XML Characteristics

• Self-describing
• Complex data
• Trees, recursive, graph
• Structured Data highly regular, homogeneous
  structures
• Semi-structured Data heterogeneous, sparse data
• Markup Data documents/content markup
• Document ordering
• Schema/Type system
• Schema-less
• Optional Schema semi-structured, structured
• Extensible
• Annotations, multiple schemas (late binding)

3
XML Scenarios

• Document Management
• Office XML Documents
• Data/view separationDocuments, style sheets,
  transforms
• Data Exchange
• Business to business (B2B), business to consumer
  (B2C), application to application (A2A)
• XML is ubiquitous, extensible, platform
  independent transport format
• Messaging
• Simple Object Access Protocol (SOAP)
• Mid-Tier Collaboration

?Transport, Store, and Query XML data
4
RDBMS XML Scenarios

• relational ? XML mapping
• XML Publishing and shredding
• Relational fidelity
• Limited support for semi-structured data
• No full XML document storage
• Document order not preserved
• Recursive schemas not supported
• Limited XML query capabilities
• Native XML storage
• Documents are preserved
• XML Query to unlock information in XML

5
Storing XML natively in RDBMS

• In BLOB/CLOB
• Textual fidelity
• No parsing, no well-formedness check
• No integrated Querying, maybe fulltext search
• As SQL-2003 standard XML type
• Infoset fidelity
• XML document or fragment
• Will be based on XQuery data model in SQL-200n
• Will allow to be constrained by XML Schemas
• Different ways to physically store type

6
BLOB, CLOB, XML or Relational?
7
SQL Server 2005 XML Architecture
Relational
XML
XML Parser
XML
XML Schemata
Schema Collection
Validation
OpenXML/nodes()
PATH Index
Node Table
XML datatype (binary XML)
Rowsets
modify()
PROP Index
PRIMARYXML INDEX
FOR XML with TYPE directive
VALUE Index
query()
query()
8
XQuery in SQL Server 2005
9
XML StoreXML Data Type

• Native SQL type (SQL-2003 standard)
• Use for column, variable or parameter
• CREATE TABLE docs (id INT PRIMARY KEY,
  xDoc XML NOT NULL)
• Store un-typed or typed XML instances
• Well-formed and validation checks
• Methods on XML data type
• query(), value(), exist(), modify(), nodes()
• XML instances stored as LOB (2GB)
• Efficient binary representation

10
XML Schema Support

• XML Schema (W3C standard)
• Rich mechanism for type definitions and
  validation constraints
• Can be used to constrain XML documents
• Benefits of typed data
• Guarantees shape of data
• Allows storage and query optimizations
• XML type system
• Store XML schemas in system meta-data

11
Native XML StoreXML Index

• Create XML index on XML column
• CREATE PRIMARY XML INDEX idx_1 ON docs (xDoc)
• Creates secondary indexes on tags, values, paths
• Speeds up queries
• Results can be served directly from index
• Entire query is optimized
• Same award winning cost based optimizer
• Indexes are used as available

12
XML Query

• XQuery query XML documents and data
• Standards-based W3C working draft
• In document 123, return section heading of
  section 3 and later
• SELECT id, xDoc.query('
• for s in
• /doc_at_id 123//sec_at_num gt 3
• return lttopicgtdata(s/heading)lt/topicgt
• ') FROM docs

13
XQuery Features

• FLWOR FOR / LET / WHERE / ORDER BY/ RETURN
• Includes XPath 2.0 (/doc_at_id 123)
• Element constructors (lttopicgt)
• Order-preserving operators
• Input order FLWR
• Document order Path expressions
• Statically typed
• Early detection of errors
• Better performance
• Strong typing with schema, weak typing without
  schema

14
XML Type System

• Associate XML namespace with Schema collection
• CREATE XML SCHEMA COLLECTION S1 AS 'ltxsschema
  targetNameSpace"http//www.ms.com/xdb" /gt')
• Associate Schema Collection with XML type
• DECLARE _at_x XML(S1) or XML(CONTENT S1)
• Only Documents are allowed
• DECLARE _at_x XML(DOCUMENT S1)

15
XML Data Modification

• Insert, update, delete XQuery extensions
• XML sub-tree modification
• Add or delete XML sub-trees
• Update values
• Add a new section after section 1
• UPDATE docs SET xDoc.modify('insertltsection
  num''2''gt ltheadinggtBackground
  lt/headinggt lt/sectiongtafter
  (/doc/section_at_num1)1')

16
Outlook to SQL-200n Standard

• Adds Schema support
• Target namespace uri/location hint or SQL
  identifier based
• ISVALID predicate
• XML datatypes
• XML(DOCUMENT)
• XML(CONTENT)
• XML(SEQUENCE)
• XML(CONTENT(ANY)), XML(CONTENT(UNTYPED))
• XML(CONTENT(XMLSCHEMA ID CustomerSC ELEMENT
  "purchaseOrder"))
• Also on DOCUMENT
• XMLCAST
• Between XML
• Between XML and SQL types

17
Outlook to SQL-200n Standard

• Adds XQUERY
• select XMLQUERY ('declare namespace c
  "urnexample/customer" for c in
  /cdoc/ccustomer where c//csaleslead
  return ltcustomer id"c/_at_id"gt
  c/cname, c//csaleslead
  lt/customergt' PASSING BY REF doc RETURNING
  CONTENT BY VALUE)
• from XMLdoc

18
Outlook to SQL-200n Standard

• Adds XMLTABLE shredder (like SQL Servers
  OpenXML)
• select N1.customer, N1.CustomerName,
  N2.OrderID, N1.DocID
• from XMLdoc,
• XMLTABLE( 'declare namespace c
  "urnexample/customer"
  /cdoc/ccustomer'PASSING doc COLUMNS
  customer XML BY REF PATH '.',
  CustomerName nvarchar(20)
  PATH 'declare namespace c
  "urnexample/customer" cname1',
  DocID nvarchar(5) PATH '../_at_id') AS N1,
• XMLTABLE( 'declare namespace c
  "urnexample/customer" ./corder'
  PASSING N1.customer COLUMNS
  OrderID int PATH '_at_id') as N2

19
Top-Level XQuery

• Operate on collections instead on instances
• See relational data as XML
• Example (pseudo-syntax)XQUERY declare
  namespace c "urnexample/customer" for
  xc in sqlcollection("XMLdoc.doc")/cdoc/ccustom
  er for rc in sqltable("Customer") where
  xc//csaleslead and rc/id xc/_at_id return
  ltcustomer id"xc/_at_id"gt
  xc/cname, rc/address, xc//csaleslead
  lt/customergt

20
Additional resources

• SQL Server webpage (white papers etc)
  http//msdn.microsoft.com/sql
• Newsgroupnewsmicrosoft.public.sqlserver.xml
• My Email mrys_at_microsoft.com
• My Blog (demo scripts) http//www.sqljunkies.com/
  weblog/mrys
• Chapter XQuery and Relational Database Systems
  in XQuery From the Experts (ed. Howard Katz,
  published by Addison-Wesley)