Mastering XQuery for XML Manipulation in SQL Server 2005

1. XQuery Your gateway to manipulating XML in SQL Server 2005

2. About the Speaker… Hal Hayes Hal.hayes@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/Query)

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 difference…no “let” statement!

7. XQuery Basics • FLOWR • XPath • Comments

8. FLOWR <bib> { for $b in doc("bib.xml")/bib/book where $b/publisher = “Microsft" and $b/@year gt 1991 return <book year="{ $b/@year }"> { $b/title } </book> } </bib> 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) • forclause 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(' insert <history>no history available</history> 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/Jour5Intro/

18. Topic Map Demo

19. Recommendations • For getting at detail in your XML documents stored in the database, XQuery is a great tool. • Don’t rely on the XQuery Where clause…you 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