XQuery

1
XQuery

• Your gateway to manipulating XML in SQL Server
  2005

2
About the Speaker

• Hal Hayes
• Hal.hayes_at_tmaresources.com
• Sr. Director, Software Engineering
• TMA Resources, Leader in Member-centric Software
• Microsoft MCP (just shy of my MCAD)
• Founder, CAPAREA (formerly CAVBUG)
• Learning Tree Instructor (SQL Server)

gratuitous aircraft picture
3
Agenda

• XQuery Defined
• Working with XML in SQL Server 2005
• Advanced Usage
• Topic Maps
• Recommendations
• Resources

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. Currently a W3C Candidate
  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!

7
XQuery Basics

• FLOWR
• XPath
• Comments

8
FLOWR
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

9
FLOWR (continued)

• 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)

10
Two Examples

• Standard Recommendation XQuery
• (using XMLSpy)
• SQL Server 2005 Version

11
Additional XML Data Type Methods In SQL 2005

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

12
XQuery DML

• Column.query(statement)
• Use XQuery statement, or
• XPath
• Declaration of a namespace
• Can return XML
• Column.value(statement, type)
• Returns a value
• Use SQL types (i.e. varchar(255))

13
XQuery DML

• Column.nodes()
• Useful for shredding an XML document into its
  constituent parts.
• If used in the WHERE clause, can be used in
  conjunction with .query(), .exist(), .value(),
  and .nodes() (but not .modify())
• Column.exist()
• Returns a 1 (representing True) if the XQuery
  expression in a query returns a nonempty result
  (meaning it returns at least one XML node).
• Returns a 0 (representing False) if the XQuery
  returns and empty result.

14
XQuery DML

• Column.modify()
• Used for XML DML operations (non-query)
• update, delete, replace value of
• Executed as part of the DML Update statement

UPDATE customerData SET customerDocs.modify(' ins
ert lthistorygtno history availablelt/historygt as
first into (//customer)1')
15
Demos

• With some basic stuff
• Maybe some advanced stuff, too!

16
Quick Introduction to Topic Maps

• Constructs for creating a meta-model of data and
  information
• Topic Maps are XML based
• Current (pending) standard is XTM 2.0 (ISO)

17
Topic Map Fundamentals
An Introduction to Topic Maps, Kal Ahmed and
Graham Moore, The Architectural Journal,
http//www.architecturejournal.net/2005/issue5/Jou
r5Intro/
18
Topic Map Demo
19
Recommendations

• For getting at detail in your XML documents
  stored in the database, XQuery is a great tool.
• Dont rely on the XQuery Where clauseyou could
  retrieve empty records!
• Strong-typed (mapped to Schema) XML Data Columns
  will have better performance than weak-typed.
• DML is limited. You have better choices for
  manipulation (i.e. .NET XML, XLinq)
• Dynamic Parameterized XQueries are problematic,
  but can be done. Best bet is to encase them in
  StoredProcs.

20
Resources

• W3C XQuery Working Group
• http//www.w3.org/XML/Query
• Topic Map ISO
• http//www.isotopicmaps.org/sam/sam-xtm/
• Topic Map Consortium
• http//www.topicmaps.org

21
Books

• A Developer's Guide to SQL Server 2005 ,
  Beauchemin, Berglund, Sullivan Addison-Wesley
• XQuery Kick Start, James McGovern, et al. SAMS
• XQuery, The XML Query Language, Michael Brundage,
  Addison-Wesley
• XQuery from the Experts, Don Chamberlin, et al.,
  Addison-Wesley

22
Blogs of Note

• 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
• Bob Beauchemin's Blog, Author of A First Look at
  SQL Server 2005 for Developers
• http//staff.develop.com/bobb/weblog/default.aspx