How well do you know XML?  Try your hand at the following puzzle and email me your solution at michaelcoREPLACE_THIS_WITH_THE_AT_SIGNoptonline.com. I'll randomly select two correct solutions to receive a copy of Pro SQL Server 2008 XML. I'll post the correct answer to this blog at the same time. Be sure to include your name and complete "snail mail" mailing address with your answer.

And with no further ado, here's the puzzle:

While doing some research on SQL Server 2005 full-text search a while back I ran across a sample XML full-text thesaurus file in Books Online:

<XML ID="Microsoft Search Thesaurus">
  <thesaurus xmlns="x-schema:tsSchema.xml">
    <diacritics = false/>
    <expansion>
      _{Internet Explorer}
      _IE
      _IE5
    </expansion>
    <replacement>
      <pat>NT5</pat>
      <pat>W2K</pat>
      _{Windows 2000}
    </replacement>
    <expansion>
      _run
      _jog
    </expansion>
  </thesaurus>
</XML>

According to the XML 1.0 Recommendation there are two problems with this XML. Can you spot the two problems?

For reference you can view the original BOL entry with this XML in it here http://msdn.microsoft.com/en-us/library/ms345186.aspx. The latest version of the XML 1.0 Recommendation is available at http://www.w3.org/TR/REC-xml/.

I'll accept contest submissions until June 20.

Part of the promise of XML is that it makes modeling hierarchical data relatively painless. The hierarchical structure of XML automatically provides context to your data. Consider the following simple example:

<General name="Grant">
  <Colonel name="Sanders">
    <Major name="Payne">
      <Captain name="Calamity">
        <Sergeant name="Bilko">
          <Private name="Benjamin" />
        </Sergeant>
      </Captain>
    </Major>
  </Colonel>
</General>

As you can see, the hierarchical structure is implicit in the structure of the XML. The General is above the Colonel is above the Major, and so on. Organizational charts, military chains of command, and other similar structures are standard fare for hierarchical modeling. Needless to say, the AdventureWorks database has a standard organizational hierarchy modeled as relational data which you'll probably see used in a whole lot of samples all over the place.

But AdventureWorks also includes a more detailed, but often-overlooked, real-world example of hierarchy. The AdventureWorks Bill of Materials (BOM) hierarchy is modeled as an adjacency list hierarchy; one in which the child nodes maintain links to their parent nodes in the same table. You can query the Bill of Materials using the dbo.uspGetBillOfMaterials procedure like this:

EXEC dbo.uspGetBillOfMaterials 749, '2001-01-01'

The results are a "flat" relational listing of the raw materials used to build the "Road-150 Red, 62" bike.

The results do provide the parent and child component ID numbers, as well as the level of the component in the BOM. But they do not convey the hierarchical information without some sort of reconstruction on your part - probably performed in the UI for display purposes. All of the information required to reconstruct the hierarchical structure of the BOM is available in this result set, but it's not readily apparent. You can use FOR XML PATH and the xml data type to reconstruct the hierarchical structure of the BOM with a simple recursive function like this:

This function calls itself recursively to retrieve a BOM (up to 5 levels deep) in XML format. You can call the function like this:

SELECT dbo.GetRecursiveBom (749, 0);

And the resulting XML looks like the image below:

As you can see the item nodes are nested within each other, with each component acting as a container for its constitutent components. The code for this function is available as a download file here: GetRecursiveBom.sql.txt

In a future post we'll look at how to create a "recursive" XML schema to validate the XML produced by this function.

One of the more interesting features of XQuery is FLWOR expressions (FLWOR stands for "for, let, where, order by, return", the keywords in the FLWOR expression vocabulary). FLWOR expressions have several capabilities, many of which are well-documented, but some of which get little attention.

One of the lesser-publicized features I found interesting is the "inner join" capability. XQuery has the ability to generate the Cartesian product ("cross join") of two path expressions using a for clause format like this:

for $var1 in path1, $var2 in path2 

Technically speaking, this for clause "binds the tuple stream" of path1 to $var1, binds the tuple stream of path2 to $var2, and generates the Cartesian product of the two variables.  If path1 generated the tuple stream (tom, joe, larry), and path2 generated the tuple stream (johnson, washington), then the result of the cross join would be:

tom johnson
tom washington
joe johnson
joe washington
larry johnson
larry washington

Every possible combination of the two tuple streams is generated. Now I promised you an inner join, didn't I? That's actually pretty simple when you consider the inner join to be a special case of the cross join. For instance, consider the old-style SQL syntax (pre-SQL-92) for inner joins. It looked something like this:

SELECT bookName
FROM book, author
WHERE book.AuthorId = author.Id

This statement performs a cross join between the book and author tables, and then restricts the results with a WHERE clause. This is the essence of the inner join. We can do the same thing with the FLWOR expression in XQuery, as shown below:

DECLARE @xml xml;
-- Create sample XML document

SET @xml = N'<authors>
  <author id = "1">Fabio Claudio Ferracchiati</author>
  <author id = "2">Hugo Kornelis</author>
  <author id = "3">Rob Walters</author>
  <author id = "4">Lara Rubbelke</author>
  <author id = "5">Adam Machanic</author>
  <author id = "6">Michael Coles</author>
  <author id = "7">Robin Dewson</author>
  <author id = "8">Jan D. Narkiewicz</author>
  <author id = "9">Robert Rae</author>
</authors>
<books>
  <book title = "Pro T-SQL 2005 Programmer&apos;s Guide">
    <isbn>159059794X</isbn>
    <author>6</author>
  </book>
  <book title = "Expert SQL Server 2005 Development">
    <isbn>159059729X</isbn>
    <author>5</author>
    <author>4</author>
    <author>2</author>
  </book>
  <book title = "Linq for Visual C# 2005">
    <isbn>1590598261</isbn>
    <author>1</author>
  </book>
  <book title = "Accelerated SQL Server 2008">
    <isbn>1590599691</isbn>
    <author>3</author>
    <author>6</author>
    <author>7</author>
    <author>1</author>
    <author>8</author>
    <author>9</author>
  </book>
  <book title = "Pro SQL Server 2008 XML">
    <isbn>1590599837</isbn>
    <author>6</author>
  </book>
</books>';

-- Perform inner join
SELECT @xml.query('for $author in /authors/author, $book in /books/book
  where $author/@id = $book/author
  and $author eq "Fabio Claudio Ferracchiati"
  return <book> { $book/@title } </book>');

Here's how this FLWOR expression works, step-by-step:

1. The query binds the tuple stream from the /authors/author path to the variable $author.
2. Then it binds the tuple stream /books/book to the $book variable.
3. It generates the cross join of the two tuple streams.
4. The where clause limits the results to the ones where the author's id attribute matches one of the book's author elements.
5. Then the results are restricted again by the second half of the compound predicate: the final results will only include those where the author's name is "Fabio Claudio Ferracchiati".
6. Finally, node construction is used to return the title of the books this author has written as XML nodes.

The result of this XQuery FLWOR expression is shown below:

<book title="Linq for Visual C# 2005" />
<book title="Accelerated SQL Server 2008" />

As you can see FLWOR expressions make inner joins, as a subset of cross joins, possible in XQuery.