Pro SQL Server XML

XML Puzzle Contest Winners

Michael Coles — Fri, 20 Jun 2008 23:18:00 GMT

In the last entry I promised to share the answer to the XML puzzle today. Here's a quick recap of the question - SQL 2005 BOL gives the following example of a full-text search XML thesaurus file: <XML ID="Microsoft Search Thesaurus">...(read more)

XML Puzzle

Michael Coles — Wed, 28 May 2008 01:23:00 GMT

How well do you know XML? Solve this XML puzzle and you can win a copy of the book Pro SQL Server 2008 XML!...(read more)

Grabbing Node Names and Values Pt. 2

Michael Coles — Sun, 09 Mar 2008 05:55:00 GMT

As a follow-up to my last post on grabbing node names and values , I was asked about grabbing URIs from more complex XML documents. The following query is the result of that discussion: DECLARE @x xml ; SET @x = N '<?xml version = "1.0"...(read more)

Grabbing Node Names and Values

Michael Coles — Fri, 07 Mar 2008 16:51:00 GMT

I got a question the other day about grabbing node names and values from an XML document. The person asking was using string parsing with the SUBSTRING function to split out names from content based on '>' characters in the XML string. Not...(read more)

Unmatched Nodes in XQuery

Michael Coles — Mon, 25 Feb 2008 05:53:00 GMT

In a previous entry I talked about simulating SQL inner joins with XQuery FLWOR expressions. In this post I'll take a look at finding unmatched nodes in two node sequences - a requirement for implementing outer join-style functionality in XQuery....(read more)

Retrieving Recursive Hierarchical Data

Michael Coles — Sat, 16 Feb 2008 02:19:00 GMT

Many people are familiar with the de facto standard hierarchical organizational chart, but the AdventureWorks sample database has some another hidden hierarchical gem. In this post I describe how to retrieve a recursive, nested, hierarchical AdventureWorks Bill of Materials as an XML document....(read more)

Inner Joins in XQuery

Michael Coles — Mon, 11 Feb 2008 05:54:00 GMT

In this post we consider how to perform classic "inner joins", as a special case of "cross joins", using XQuery FLWOR expressions....(read more)

Loading XML From the File System

Michael Coles — Mon, 04 Feb 2008 19:00:00 GMT

One from the bag o' tricks: the no fuss no muss way to quickly load XML documents from the file system into SQL Server for storage or processing....(read more)

Create a Dynamic Logging Trigger With XML

Michael Coles — Sun, 27 Jan 2008 01:47:00 GMT

Triggers are a useful tool for logging DML actions and auditing data changes to tables. But wouldn't it be great if you could write just one trigger and use it on any table? And if the table structure changed the trigger would automatically adjust with no work on your part? With FOR XML and catalog views we can do just that....(read more)

Xml Data Type: nodes() Method

Michael Coles — Wed, 23 Jan 2008 00:44:00 GMT

In the second-half of this kick-off double-feature, I'll tell you why SQL Server OPENXML shredding is on my TTMMU ("Things That Make Me Unhappy") shortlist. Here's why it should be on yours too, plus what you can do about it....(read more)

Welcome to Pro SQL Server XML

Michael Coles — Mon, 21 Jan 2008 22:38:00 GMT

Welcome to the Pro SQL Server XML (2005, 2008) blog. In this introductory blog, I provide a sample using the xml data type and the nodes() method to shred cached XML query plans on the server. This example demonstrates several new features in addition to the nodes() method, including CROSS APPLY, CTEs, dynamic management functions/views, and more....(read more)