Home > Articles > Databases > SQL Server

SQL Server Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

SQL Server I/O: Using XML Control Mechanisms

Last updated Mar 28, 2003.

I'm pretty impressed with things that are extensible. I like Swiss Army knives, I like multiple-use tools, and I like basic things that connect to other basic things to make new things.

I explained how to use the FOR XML select predicate in a previous article. I mentioned that there are a few limitations with the qualifier AUTO at the end of that predicate. In this week's article, I explore a couple of those weaknesses and show you a more powerful (if not more verbose) qualifier.

As you recall, the FOR XML AUTO predicate creates an XML output by turning a rowset based data set into a hierarchy. It does that by using the order of the SELECT statement and the column names to create the element or attribute names. One limitation with this predicate is that it is difficult to specify the element or attribute names directly. Another limitation is that it is difficult to make multiple levels in the XML document, and depending on the join, difficult to place these levels "after the fact." That basically means that to get the proper hierarchy, you have to keep the columns in order.

The answer to these issues is the FOR XML EXPLICIT predicate. It's longer to type, and takes a bit longer to understand, but once you do you'll find yourself using this statement as your primary way to get XML data into the format you want.

I've been using the authors table in the pubs database quite a bit throughout this series, but I use the titles table here to demonstrate a hierarchy. The titles table shows the names of books in the database. I also include the sales table, which shows the sales of each book. I stop at two levels of the hierarchy so that you have a chance to understand the concept, but you can apply it to as many levels as you need.

One of the best ways to explain this predicate is to define what I'm looking to accomplish, show the statement, and then break each component of the statement down with an example.

In this example, I'm looking for an attribute-normal form representation of the identifiers and titles of the books in the database. I need to create some T-SQL scripts that run daily to parse these out to a file location that another firm uses in their sales forecast application.

Now for the statement. I use a simple SELECT statement to retrieve the data, and then use the FOR XML EXPLICIT predicate to format it properly. The statement takes the following format:

SELECT column AS [Element Name!Tag Number!Attribute Name!Directive]
FROM
Table

That's the simplest form. In a moment, I'll ask for more columns than just one and also add a join condition, but let's keep it simple for now. The command is typed just as I have it here, with the [] and the ! marks. Here are the component parts of the SELECT statement:

  • Element Name: the name of the element that I place the column in.
  • Tag Number: shows the Level of the hierarchy, with 1 the highest.
  • Attribute Name: I want this in attribute-normal form, so the name of the attribute goes here. Even if I want to change that to element-normal form, the value still goes here.
  • Directives: Directives are optional commands that tell the processor to treat the data a little differently. They are:
  • cdata: This wraps the column in a CDATA element, which is similar to the <PRE> tag in HTML. It basically treats everything within it as plain text, and preserves indentation and so forth. If you use this directive, your font falls back to the default text font in your display.
  • element: Shows the data in element normal form.
  • hide: Hides the column.
  • ID, IDREF, and IDREFS: Define the keys and referencing attributes when using XMLDATA. I'll explain more about this concept in a future article.
  • Parent: The tag under which this element shows up.

Putting this into practical use, we have:

USE pubs
GO

SELECT 1 AS Tag 
  , NULL AS Parent
  , title_id AS [Book!1!ID]
  , title AS [Book!1!Title]
  , type AS [Book!1!Type]
FROM titles
FOR XML EXPLICIT

I selected a NULL value to hold the place of the parent node, since this is the root of the XML document. Here is a partial result of that query:

<Book ID="MC2222" Title="Silicon Valley Gastronomic Treats" Type="mod_cook "/>
<Book ID="MC3021" Title="The Gourmet Microwave" Type="mod_cook "/>
<Book ID="MC3026" Title="The Psychology of Computer Cooking" Type="UNDECIDED "/>
<Book ID="PC1035" Title="But Is It User Friendly?" Type="popular_comp"/>
<Book ID="PC8888" Title="Secrets of Silicon Valley" Type="popular_comp"/>
<Book ID="PC9999" Title="Net Etiquette" Type="popular_comp"/>
<Book ID="PS1372" Title="Computer Phobic AND Non-Phobic Individuals: Behavior Variations" Type="psychology "/>
<Book ID="PS2091" Title="Is Anger the Enemy?" Type="psychology "/>
<Book ID="PS2106" Title="Life Without Fear" Type="psychology "/>

As I mentioned in previous articles, in many cases I reserve attributes for meta-data. Assuming that I'll use the "type" tag of the book as meta-data, I rearrange the column order and modify two lines of the SELECT statement to include a directive to make certain fields return as elements:

SELECT 1 AS Tag 
  , NULL AS Parent
  , type AS [Book!1!Type]
  , title_id AS [Book!1!ID!element]
  , title AS [Book!1!Title!element]
FROM titles
FOR XML EXPLICIT

And here's a partial result of that query:

<Book Type="business ">
 <ID>BU1032</ID>
 <Title>The Busy Executive's Database Guide</Title>
</Book>

<Book Type="business ">
 <ID>BU1111</ID>
 <Title>Cooking with Computers: Surreptitious Balance Sheets</Title>
</Book>

<Book Type="business ">
 <ID>BU2075</ID>
 <Title>You Can Combat Computer Stress!</Title>
</Book>

Using FOR XML AUTO certainly simplifies the syntax of a SELECT, but doesn't offer this kind of flexibility. Using FOR XML EXPLICIT does require a bit more thought, and it allows you to create just the right document layout for your situation.

Next week, we'll continue this thread. I'll show you how to create hierarchies with the FOR XML EXPLICIT predicate.

Online Resources

If you've got a little time, Ronald Bourret has one of the best breakdowns of using XML as a database that I've seen. Check it out here: http://www.rpbourret.com/xml/XMLAndDatabases.htm#intro

InformIT Tutorials and Sample Chapters

Nicholas Chase covers SQL Server's XML capabilities as well. You can read his article here: http://www.informit.com/guides/content.aspx?g=xml&seqNum=115

Save To Your Account