XQuery Tips and Tricks

1
XQuery Tips and Tricks

• Using SQL Server 2005

Richmond Code Camp April 2007
2
Your Speaker

• Hal Hayes, Principal Consultant
• Microsoft MCP
• ACRITECH Corp
• www.acritech.com
• Software Development Consulting
• DoD, Healthcare, Commercial
• DotNetNuke, SharePoint, SQL Server, .NET
  Development

Gratuitous aircraft picture
3
Agenda

• XQuery (Brief Overview)
• Some XML Column Methods
• Tips with XQuery
• Working with Results
• Performance

4
What you should know

• XML
• XML Schemas
• XPath and XSLT
• Working with XML in .NET
• Working with XML in SQL Server

5
What is XQuery???

• XML-based functional, declarative query language
• XQuery is SQL-like
• Fine-grained querying against native XML
  (hierarchical vs. flat table/record format for
  SQL)
• Syntax is straight-forward making it easy (??) to
  query your XML data
• Standards Based. Recently promoted to a W3C
  Recommendation
• W3 standardized language (http//www.w3.org/XML/Q
  uery)

6
XQuery in SQL Server 2005

• A subset of full language
• Namespace aware
• Can work with or without a Schema (strong vs.
  weak-typed)
• One major differenceno let statement!
• (Whats let??)

7
XML Data Type Methods in SQL 2005

• Column.query()
• Column.value()
• Column.modify()
• Column.nodes()
• Column.exists()

8
XQuery Basics (Demo)

• Operations on XML data type using the .query()
  method
• XPath
• FLOWR

9
FLWOR
ltbibgt for b in doc("bib.xml")/bib/book
where b/publisher Microsft" and b/_at_year
gt 1991 return ltbook year" b/_at_year "gt
b/title lt/bookgt lt/bibgt

• Main construct of XQuery is the FLOWR expression
• For-Let-Where-Order-Return
• equivalence to SELECT-FROM-HAVING-WHERE in SQL
• Use the Return construct to shape your results

10
FLWOR (contd)

• for clause provides a definition of a variable
  and binding of iterations across a range of
  sequence values (SQL-SELECT)
• let clause allows association of a variable to a
  further ordered list of tuples (SQL-SET)
• where clause is a filter of the current sequence
  or tuples from for/let (SQL-WHERE)
• order clause sorts current results based on a
  given criteria (SQL-ORDER)
• return clause is used to create output that can
  be XML or not XML (SQL-RETURN

11
More Demos FLWOR Queries
12
Erorors

• Kinds of errors you may encounter
• XQuery syntax errors
• Malformed XPath with Schema
• Malformed XPath without Schema (empty entities)
• value() requires a singleton (or empty sequence)
• Wrong data type in comparison

13
Implementation Issues

• Empty Entities
• Wrong data type in comparison
• Understanding the WHERE
• Inserting XML into XML data type

14
XQuery and ADO.Net

• Getting back your results
• String data type
• SqlXML data type

15
How about XQuery with SQLCLR?

• Demo

16
Performance

• Comparison of search
• XQuery (with Schema)
• XQuery (without Schema)
• XML Dom/XPath on Client
• Text Search
• Shredded Document/Relational

17
Tips

• Were practical, use Schemas
• Performance (maybe)
• Provides a clue to invalid XPath statements
• XQuery implementation
• Use .exists to reduce likelihood of returning
  empty entities
• Insert XML fragments using .NET (Client-side or
  SQLCLR) Because it is either too hard or
  impossible via XML column methods
• Manage XQuery statements in production through
  Stored Procs or Functions (do not use it as an Ad
  Hoc XML query tool) but with limitations

18
XQuery Other Solutions

• Commercial
• Altova (XMLSpy) AltovaXML, a free download that
  works with .NET (free)
• DataDirect XQuery
• Sonic XML Server
• Saxonica Saxon
• Oracle Xquery
• Open Source
• Numerous, like XQEngine

J A V A
19
Questions?
20
Conclusions

• XQuery provides a powerful tool to sift large
  sets of hierarchical data
• SQL Server 2005 has placed many limitations on
  its XQuery implementation
• XQuery is not a replacement for SQL, at least
  within relational databases

21
References

• Articles
• Five Practical XQuery Applications, DevX
• http//www.devx.com/xml/Article/15618/0/page/1
• The Case for XQuery XML adoption is growing
  steady, XML Journal
• http//xml.sys-con.com/read/155663.htm
• Books
• Pro ADO.NET 2.0, Sahil Malik, Apress 2005
• Pro SQL Server 2005, by Thomas Rizzo, et al.,
  Apress 2005
• XQuery Kick Start, James McGovern, et al., SAMS
  2003
• Pro SQL Server 2005, by Thomas Rizzo, et al.,
  Apress 2005
• XQuery from the Experts A Guide to the W3C XML
  Query Language, Howard Katz, et al.,
  Addison-Wesley 2003
• XQuery The XML Query Language, Michael Brundage,
  Addison-Wesley 2004
• A Developer's Guide to SQL Server 2005, Bob
  Beauchemin and Dan Sullivan, Addison-Wesley 2006

22
Blogs

• Hals Blog Morning Compilation
• http//www.halhayes.com/blog
• Michael Rys, Program Manager for SQL Server's XML
  Technologies and member of W3 XML Query Working
  Group
• http//sqljunkies.com/WebLog/mrys
• Kent Tegels, DevelopMentor, MVP
• http//sqljunkies.com/WebLog/ktegels
• Mike Champion, Program Manager for XML Standards
  XML WebData team at Microsoft
• http//blogs.msdn.com/mikechampion
• Shankar Pal, XML SQL Program Manager, Microsoft
  Corporation
• http//blogs.msdn.com/spal
• Microsoft Corporation XML Team Weblog
• http//blogs.msdn.com/xmlteam/default.aspx