Using XQuery to Query and Manipulate XML Data in SQL Server 2005

1
Using XQuery to Query and Manipulate XML Data in
SQL Server 2005

• Stephen Forte
• CTO, Corzen Inc
• Microsoft Regional Director NY/NJ (USA)

2
Speaker.Bio.ToString()

• CTO and co-Founder of Corzen, Inc
• Microsoft Regional Director for New York NJ
• Blog http//www.stephenforte.net/owdasblog/
• Wrote a few books (Jet, VB, SQL Server, Access)
• Writing SQL Server Core Developers Guide (MS
  Press)
• International Conference Speaker for 8 Years
• Co-moderator founder of NYC .NET Developers
  Group
• http//www.nycdotnetdev.com
• Former CTO of Zagat Survey
• Hobbies include high altitude trekking, scuba
  diving, hiking, rock climbing and triathlon

3
Session Disclaimer Notes

• All code against SQL Server 2005 June 2005 CTP
  beta
• You can download for free at
• www.microsoft.com/sqlserver
• Content is subject to change in final release but
  if so will update this session on my blog

4
SQL Server 2005 Platform
5
Agenda

• XML in the Database?
• What is XQuery?
• Building XQuery Expressions
• XML DML

6
XML in a relational database??

• Applications may need to work with XML data, if
  so how do store it, you can
• stored XML as text
• loses much of value of XML representation
• decompose XML into multiple relational tables
• allows use of relational technologies
• Store XML as an xml data type
• allows use of XML technologies

7
XML Data Type

• New SQL scalar type
• Usable as columns and variables in table, view,
  function, stored procedure, etc.
• Constraints by XML schema
• Generated by relational FOR XML queries
• Rowset access using OpenXML
• Data Manipulation - XQuery modification
• Support SQL triggers, replication, BCP, DBCC,
  FTS, etc.
• Not comparable PK/FK, UNIQUE unsupported

8
Features Of XML Data Type

• Implicit conversion from character types
• Can contain documents and fragments (element
  content on top level)
• Can be statically constrained by XML Schema
• Method based interface
• Query and DML on XML
• Explicit extraction of scalar values
• Returned to user as XML text or binary
• TDS enhanced for XML data type
• XML data storage provides efficient processing

9
XML column

• XML column can store well-formed XML
• documents or fragments

10
XML Schema Support

• XML Schema
• Rich mechanism for schema definitions
• Optional XML document, W3C standard
• Benefits of typed data
• Guarantees shape of data
• Allows storage and query optimizations
• XML type system
• Store XML schemas in system meta-data

11
XML indexes

• You can create XML INDEXes on an XML column
• optimizes XML Queries on the column
• table or view must have clustered primary key
• composite XML index not allowed
• primary XML index must be created first
• three specialized index types also available
• VALUE, PATH, PROPERTY

CREATE TABLE xml_tab ( id integer primary key,
doc xml) GO CREATE PRIMARY XML INDEX xml_idx
on xml_tab (doc) GO
12
Agenda

• XML in the Database?
• What is XQuery?
• Building XQuery Expressions
• XML DML

13
What is XQuery

• XQuery is a language used to query and process
  XML data
• W3C Standard
• http//www.w3.org/TR/xquery/
• XQuery is a superset of XPath 2.0
• XQuery uses expressions to query data-similar to
  SQL

14
FLWOR Expressions

• XML Queries are FLWOR expressions
• made up of five types of clause
• FOR
• LET (not supported by SQL Server 2005 or
  System.Xml)
• WHERE
• ORDER BY
• RETURN

15
XQuery Expressions

• XQuery is a superset of XPath
• can use XPath or FLWOR expressions
• Can also use both in one expression
• XPath used to select values in FLWOR expressions

for p in /students/student where p/age gt
65 order by p/age1 return p/name
16
FLWOR Expression
for p in doc(students.xml")/students/student
let a p/age
where a gt 65
return p/name/givenName/text()
Richard Campbell
17
Before you XQuery

• Your table needs a primary key
• The XML Column needs an XML Schema
• Apply an XML Index (primary and values)

18
Now you are ready to XQuery!
19
Agenda

• XML in the Database?
• What is XQuery?
• Building XQuery Expressions
• XML DML

20
SQL Server XQuery Functionality

• June 2005 CTP Version of SQL Server
• SQL Server exposes a subset of XQuery
• November 2003 spec alignment (spec changed from
  beta1)
• XPath predicate supported only at end of
  expression
• Can use XQuery as a constraint on a field
• only a subset of functions and operators
  supported
• old-style comments
• -- comment -- instead of ( comment )

21
SQL Server 2005 Support of XQuery

• XQuery is supported through methods on the XML
  type
• xml.exist
• xml.value
• xml.query
• xml.modify
• xml.nodes
• These methods can return
• columns in rowsets - when used with SQL SELECT
• Variables
• SQL Server only DML functions

22
xml.exist

• xml.exist uses XML type and XQuery expression
• returns false if query returns NULL sequence
• returns true otherwise
• Useable in XML type check constraints

23
xml.exist
24
xml.value

• xml.value return a SQL Server scalar type
• SQL type, xml data type instance, XQuery as input
• returns scalar type or NULL
• cannot return XML type instance
• Useable within SQL Server
• in predicates
• as result values

25
xml.value
26
xml.query

• xml.query returns an XML data type instance
• XML type instance and XQuery are inputs
• can return scalar type as XML fragment
• can use constructors to compose new data

27
xml.query
28
Using Relational Data in XQuery

• SQL Server XQuery can combine relational and XML
• sqlvariable - uses TSQL variables
• sqlcolumn - uses column value
• same row as XML type column

29
sqlvariable, sqlcolumn
30
XML DML

• xml.modify can mutate the XML instance in place
• XQuery has no standard DML
• uses proposed DML extensions
• insert, delete, replace operators within XQuery
  expressions
• insert can insert before or after an element
• insert can insert first or last sibling
• insert can insert subelements or attribute
• must be a single node
• replace can replace elements or attributes
• only simple types
• uses "value-of" clause to update value

31
xml.modify
32
Session Summary

• If you need to store XML data use the native type
• SQL Server implements the W3C XQuery Spec
• SQL Server extends the spec with XML DML

33
Questions?
34
Thanks!

• Please fill out your evaluation form!
• stevef_at_orcsweb.com
• Please put (PDC Karachi in the subject)