The Gurus Guide to SQL Server Stored Procedures, XML, and HTML [Electronic resources] نسخه متنی

EXPLICIT Mode

If you need more control over the XML that FOR XML produces, EXPLICIT mode is more flexible (and therefore more complicated to use) than either RAW mode or AUTO mode. EXPLICIT mode queries define XML documents in terms of a "universal table"a mechanism for returning a result set from SQL Server that describes what you want the document to look like, rather than composing the document itself. A universal table is just a SQL Server result set with special column headings that tell SQL Server how to produce an XML document from your data. Think of it as a set-oriented method of making an API call and passing parameters to it. You use the facilities available in Transact-SQL to make the call and pass it parameters.

A universal table consists of one column for each table column that you want to return in the XML fragment, plus two additional columns: Tag and Parent. Tag is a positive integer that uniquely identifies each tag that is to be returned by the document; Parent establishes parent-child relationships between tags.

The other columns in a universal tablethe ones that correspond to the data you want to include in the XML fragmenthave special names that actually consist of multiple segments delimited by exclamation points. These special column names pass muster with SQL Server's parser and provide specific instructions regarding the XML fragment to produce. They have the format of

Element!Tag!Attribute!Directive

We'll see some examples of these shortly.

The first thing you need to do to build an EXPLICIT mode query is to determine the layout of the XML document with which you want to end up. Once you know this, you can work backward from there to build a universal table that will produce the desired format. For example, let's say we want a simple customer list based on the Northwind Customers table that returns the customer ID as an attribute and the company name as an element. The XML fragment we're after might look like this:

<Customers CustomerId="ALFKI">Alfreds Futterkiste</Customers>

Here's a Transact-SQL query that returns a universal table that specifies this layout:

SELECT 1 AS Tag,
NULL AS Parent,
CustomerId AS [Customers!1!CustomerId],
CompanyName AS [Customers!1]
FROM Customers

(Results abridged)

Tag         Parent      Customers!1!CustomerId Customers!1
----------- ----------- ---------------------- --------------
1     NULL        ALFKI                 Alfreds Futterkiste
1     NULL        ANATR                 Ana Trujillo Emparedados y
1     NULL        ANTON                 Antonio Moreno Taquería

The first two columns are the extra columns I mentioned earlier. Tag specifies an identifier for the tag we want to produce. Because we only want to produce one element per row, we hard code this to 1. The same is true of Parent. There's only one element, and a top-level element doesn't have a parent, so we return NULL for Parent in every row.

Because we want to return the customer ID as an attribute, we specify an attribute name in the heading of column 3 (in bold type). And because we want to return CompanyName as an element rather than an attribute, we omit the attribute name in column 4.

By itself, this table accomplishes nothing. We have to add FOR XML EXPLICIT to the end of it in order for the odd column names to have any special meaning. Add FOR XML EXPLICIT to this query and run it from Query Analyzer. Here's what you should see:

SELECT 1 AS Tag,
NULL AS Parent,
CustomerId AS [Customers!1!CustomerId],
CompanyName AS [Customers!1]
FROM Customers
FOR XML EXPLICIT

(Results abridged and formatted)

XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------------
<Customers CustomerId="ALFKI">Alfreds Futterkiste</Customers>
<Customers CustomerId="ANATR">Ana 
Trujillo Emparedados y helados</Customers>
<Customers CustomerId="WHITC">White Clover Markets</Customers>
<Customers CustomerId="WILMK">Wilman Kala</Customers>
<Customers CustomerId="WOLZA">Wolski Zajazd</Customers>

As you can see, each CustomerId value is returned as an attribute, and each CompanyName is returned as the element data for the Customers element, just as we specified.

Directives

The fourth part of the multivalue column headings supported by EXPLICIT mode queries is the directive segment. You use it to further control how data is represented in the resulting XML fragment. The directive segment supports eight values (Table 14-1).

Of these, element is the most frequently used. It causes data to be rendered as a subelement rather than an attribute. For example, let's say that, in addition to CustomerId and CompanyName, we wanted to return ContactName in our XML fragment and we wanted it to be a subelement rather than an attribute. Here's what the query would look like:

SELECT 1 AS Tag,
NULL AS Parent,
CustomerId AS [Customers!1!CustomerId],
CompanyName AS [Customers!1],
ContactName AS [Customers!1!ContactName!element]
FROM Customers
FOR XML EXPLICIT

(Results abridged and formatted)

XML_F52E2B61-18A1-11d1-B105-00805F49916B
-------------------------------------------
<Customers CustomerId="ALFKI">Alfreds Futterkiste
<ContactName>Maria Anders</ContactName>
</Customers>
<Customers CustomerId="ANATR">Ana Trujillo Emparedados y
<ContactName>Ana Trujillo</ContactName>
</Customers>
<Customers CustomerId="ANTON">Antonio Moreno Taquería
<ContactName>Antonio Moreno</ContactName>
</Customers>
<Customers CustomerId="AROUT">Around the Horn
<ContactName>Thomas Hardy</ContactName>
</Customers>
<Customers CustomerId="BERGS">Berglunds snabbköp
<ContactName>Christina Berglund</ContactName>
</Customers>
<Customers CustomerId="WILMK">Wilman Kala
<ContactName>Matti Karttunen</ContactName>
</Customers>
<Customers CustomerId="WOLZA">Wolski Zajazd
<ContactName>Zbyszek Piestrzeniewicz</ContactName>
</Customers>

As you can see, ContactName is nested within each Customers element as a subelement. The elements directive encodes the data it returns. We can retrieve the same data using the xml directive without encoding like this:

SELECT 1 AS Tag,
NULL AS Parent,
CustomerId AS [Customers!1!CustomerId],
CompanyName AS [Customers!1],
ContactName AS [Customers!1!ContactName!xml]
FROM Customers
FOR XML EXPLICIT

The xml directive (in bold type) causes the column to be returned without encoding any special characters it contains.

Establishing Data Relationships

Thus far, we've been listing the data from a single table, so our EXPLICIT queries haven't been terribly complex. That would still be true even if we queried multiple tables, as long as we didn't mind repeating the data from each table in each top-level element in the XML fragment. Just as the column values from joined tables are often repeated in the result sets of Transact-SQL queries, we could create an XML fragment that contained data from multiple tables repeated in each element. However, this wouldn't be the most efficient way to represent the data in XML. Remember: XML supports hierarchical relationships between elements. You can establish these hierarchies using EXPLICIT mode queries and T-SQL UNIONs. Here's an example:

SELECT 1 AS Tag,
NULL AS Parent,
CustomerId AS [Customers!1!CustomerId],
CompanyName AS [Customers!1],
NULL AS [Orders!2!OrderId],
NULL AS [Orders!2!OrderDate!element]
FROM Customers
UNION
SELECT 2 AS Tag,
1 AS Parent,
CustomerId,
NULL,
OrderId,
OrderDate
FROM Orders
ORDER BY [Customers!1!CustomerId], [Orders!2!OrderDate!element]
FOR XML EXPLICIT

This query does several interesting things. First, it links the Customers and Orders tables using the CustomerId column that they share. Notice the third column in each SELECT statement. It returns the CustomerId column from each table. The Tag and Parent columns establish the details of the relationship between the two tables. The Tag and Parent values in the second query link it to the first. They establish that ORDER records are children of CUSTOMER records. Lastly, note the ORDER BY clause. It arranges the elements in the table in a sensible fashionfirst by CustomerId and secondly by the OrderDate of each Order. Here's the result set:

(Results abridged and formatted)

XML_F52E2B61-18A1-11d1-B105-00805F49916B
---------------------------------------------
<Customers CustomerId="ALFKI">Alfreds Futterkiste
<Orders OrderId="10643">
<OrderDate>1997-08-25T00:00:00</OrderDate>
</Orders>
<Orders OrderId="10692">
<OrderDate>1997-10-03T00:00:00</OrderDate>
</Orders>
<Orders OrderId="10702">
<OrderDate>1997-10-13T00:00:00</OrderDate>
</Orders>
<Orders OrderId="10835">
<OrderDate>1998-01-15T00:00:00</OrderDate>
</Orders>
<Orders OrderId="10952">
<OrderDate>1998-03-16T00:00:00</OrderDate>
</Orders>
<Orders OrderId="11011">
<OrderDate>1998-04-09T00:00:00</OrderDate>
</Orders>
</Customers>
<Customers CustomerId="ANATR">Ana Trujillo Emparedados y helados
<Orders OrderId="10308">
<OrderDate>1996-09-18T00:00:00</OrderDate>
</Orders>
<Orders OrderId="10625">
<OrderDate>1997-08-08T00:00:00</OrderDate>
</Orders>
<Orders OrderId="10759">
<OrderDate>1997-11-28T00:00:00</OrderDate>
</Orders>
<Orders OrderId="10926">
<OrderDate>1998-03-04T00:00:00</OrderDate>
</Orders>
</Customers>

As you can see, each customer's orders are nested within its element.

The hide Directive

You use the hide directive to omit a column you've included in the universal table from the resulting XML document. One use of this functionality is to order the result by a column that you don't want to include in the XML fragment. When you aren't using UNION to merge tables, this isn't a problem because you can order by any column you choose. However, the presence of UNION in a query requires order by columns to exist in the result set. The hide directive gives you a way of satisfying this requirement without being forced to return data you don't want to. Here's an example:

SELECT 1 AS Tag,
NULL AS Parent,
CustomerId AS [Customers!1!CustomerId],
CompanyName AS [Customers!1],
PostalCode AS [Customers!1!PostalCode!hide],
NULL AS [Orders!2!OrderId],
NULL AS [Orders!2!OrderDate!element]
FROM Customers
UNION
SELECT 2 AS Tag,
1 AS Parent,
CustomerId,
NULL,
NULL,
OrderId,
OrderDate
FROM Orders
ORDER BY [Customers!1!CustomerId], [Orders!2!OrderDate!element], 
[Customers!1!PostalCode!hide]
FOR XML EXPLICIT

Notice the hide directive (in bold type) that's included in the column 5 heading. It allows the column to be specified in the ORDER BY clause without actually appearing in the resulting XML fragment.

The cdata Directive

It's not unusual for XML documents to need to include unparsed data. CDATA (as opposed to PCDATA, where "P" stands for "Parsed") is unparsed character data. A CDATA section is output by an XML parser in the same condition it was received. There's no encoding or other translation. CDATA sections allow you to include XML sections that might otherwise confuse the parser. To render a CDATA section from an EXPLICIT mode query, include the cdata directive. Here's an example:

SELECT 1 AS Tag,
NULL AS Parent,
CustomerId AS [Customers!1!CustomerId],
CompanyName AS [Customers!1],
Fax AS [Customers!1!!cdata]
FROM Customers
FOR XML EXPLICIT

(Results abridged and formatted)

XML_F52E2B61-18A1-11d1-B105-00805F49916B
-------------------------------------------
<Customers CustomerId="ALFKI">Alfreds Futterkiste
<![CDATA[030-0076545]]>
</Customers>
<Customers CustomerId="ANATR">Ana Trujillo Emparedados y helados
<![CDATA[(5) 555-3745]]>
</Customers>
<Customers CustomerId="ANTON">Antonio Moreno Taquería
</Customers>
<Customers CustomerId="AROUT">Around the Horn
<![CDATA[(171) 555-6750]]>
</Customers>
<Customers CustomerId="BERGS">Berglunds snabbköp
<![CDATA[0921-12 34 67]]>
</Customers>

As you can see, each value in the Fax column is returned as a CDATA section in the XML fragment. Note the omission of the attribute name in the cdata column heading (in bold type). This is because attribute names aren't allowed for CDATA sections. Again, they represent unparsed sections in a document, so the XML parser can't process any attribute or element names they may contain.

The id, idref, and idrefs Directives

The id, idref, and idfrefs directives can be used to represent relational data in an XML document. Set up in a DTD or XML-Data schema, they establish relationships between elements. They're handy in situations when you need to exchange complex data and want to minimize the amount of data duplication in the document.

EXPLICIT mode queries can use the id, idref, and idrefs directives to specify relational fields in an XML document. Naturally, this approach is only workable if a schema is used to define the document and identify the columns used to establish links between entities. FOR XML's XMLDATA option provides a means of generating an inline schema for its XML fragment. In conjunction with the id directives, it can identify relational fields in the XML fragment. Here's an example:

SELECT 1 AS Tag,
NULL AS Parent,
CustomerId AS [Customers!1!CustomerId!id],
CompanyName AS [Customers!1!CompanyName],
NULL AS [Orders!2!OrderID],
NULL AS [Orders!2!CustomerId!idref]
FROM Customers
UNION
SELECT 2,
NULL,
NULL,
NULL,
OrderID,
CustomerId
FROM Orders
ORDER BY [Orders!2!OrderID]
FOR XML EXPLICIT, XMLDATA

(Results abridged and formatted)

XML_F52E2B61-18A1-11d1-B105-00805F49916B
--------------------------------------------
<Schema name="Schema2" xmlns="urn:schemas-
microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="Customers" content="mixed" model="open">
<AttributeType name="CustomerId" dt:type="id"/>
<AttributeType name="CompanyName" dt:type="string"/>
<attribute type="CustomerId"/>
<attribute type="CompanyName"/>
</ElementType>
<ElementType name="Orders" content="mixed" model="open">
<AttributeType name="OrderID" dt:type="i4"/>
<AttributeType name="CustomerId" dt:type="idref"/>
<attribute type="OrderID"/>
<attribute type="CustomerId"/>
</ElementType>
</Schema>
<Customers xmlns="x-schema:#Schema2" CustomerId="ALFKI"
CompanyName="Alfreds Futterkiste"/>
<Customers xmlns="x-schema:#Schema2" CustomerId="ANATR"
CompanyName="Ana Trujillo Emparedados y helados"/>
<Customers xmlns="x-schema:#Schema2" CustomerId="ANTON"
CompanyName="Antonio Moreno Taquería"/>
<Customers xmlns="x-schema:#Schema2" CustomerId="AROUT"
CompanyName="Around the Horn"/>
<Orders xmlns="x-schema:#Schema2" OrderID="10248" CustomerId="VINET"/>
<Orders xmlns="x-schema:#Schema2" OrderID="10249" CustomerId="TOMSP"/>
<Orders xmlns="x-schema:#Schema2" OrderID="10250" CustomerId="HANAR"/>
<Orders xmlns="x-schema:#Schema2" OrderID="10251" CustomerId="VICTE"/>
<Orders xmlns="x-schema:#Schema2" OrderID="10252" CustomerId="SUPRD"/>
<Orders xmlns="x-schema:#Schema2" OrderID="10253" CustomerId="HANAR"/>
<Orders xmlns="x-schema:#Schema2" OrderID="10254" CustomerId="CHOPS"/>
<Orders xmlns="x-schema:#Schema2" OrderID="10255" CustomerId="RICSU"/>

Note the use of the id and idref directives to in the CustomerId columns of the Customers and Orders tables (in bold type). These directives link the two tables using the CustomerId column that they share.

If you examine the XML fragment returned by the query, you'll see that it starts off with the XML-Data schema that the xmldata directive created. This schema is then referenced in the XML fragment that follows.