XML PATH Syntax

The XslGenerator and ExecuteMethod web method use SQL's FOR XML PATH syntax to return SQL recordsets in XML format. This topic outlines various case studies with FOR XML PATH syntax.

Always Return Root Node

When working with the XslGenerator, you may want to return an empty node if no results are returned. When working with an empty node, you can display a "Sorry, no results" message to a user (see Extensible_Stylesheet_Language#Displaying_a_Message_Saying_No_Results for more). To return an empty node, you must have an outer wrapper node. Otherwise SQL will return nothing, and your XSL will not get transformed.

For example, if this SQL returns no records for a given @cst_key parameter, you will have no XML returned to parse and your XSL will not work:

SELECT cst_sort_name_dn, 
[sum_inv_trx_date]=CONVERT(varchar, 
  sum_inv_trx_date, 101), 
  sum_sum_total, 
  sum_linebalance 
FROM vw_ac_invoice (nolock) 
JOIN co_customer (nolock) ON sum_cst_key = cst_key
WHERE 
  cst_key = @cst_key
FOR xml path('invoice'), root('root')

The SQL will return NO XML if there are no records. When run in SQL Server Management Studio, you will see this:

This means in your XSL template, you will never get a match on the main <root> node, and your "no results" section enclosed in the <xsl:if /> section will never get hit:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 
<xsl:template match="/root">
<h3>Search for an XYZ certified professional:</h3>
<table width="100%" border="1">
<tr>
<td>Name</td>
<td>Date</td>
<td>Total</td>
</tr>
 
<xsl:if test="count(invoice) = 0">
<tr>
<td colspan="3">No results</td>
</tr>
</xsl:if>
 
<xsl:apply-templates select="invoice" />
</table>
</xsl:template>
 
</xsl:stylesheet>

Enclose the inner SQL with an outer SELECT (along with a couple of other minor syntax changes) to make it always return an outer <root> node as shown here:

SELECT
(SELECT 
    cst_sort_name_dn, 
[sum_inv_trx_date]=CONVERT(varchar, sum_inv_trx_date, 101), 
    sum_sum_total, 
    sum_linebalance 
FROM vw_ac_invoice (nolock) 
JOIN co_customer (nolock) ON sum_cst_key = cst_key
WHERE 
    cst_key = @cst_key
FOR xml path('invoice'), type)
FOR xml path('root')

With this SQL, no results will return this, and the <root> template will be found in the XSL:

<root   />

Another example:

SELECT
(SELECT evt_key, evt_title
FROM ev_event (nolock)
WHERE evt_post_to_web_date > GETDATE()
FOR XML path('event'), type)
FOR xml path(''), root('events')

If an event meet this criteria, it returns the following:

<events>
<event>
<evt_key>ED670FE3-FAE1-4677-8C32-01AB0EB0E5D7</evt_key>
<evt_title>DLD Free Event 01</evt_title>
</event>
<event>
<evt_key>5969CA37-E9E9-4FFD-B921-044B56DDEB3D</evt_key>
<evt_title>2011 Back to School Bash</evt_title>
</event>
<event>
<evt_key>06C3FDB5-4C42-4C2F-A8A7-0663667B4477</evt_key>
<evt_title>2011 Discount Event</evt_title>
</event>
</events>

If no events meet the criteria, the following is returned:

<events></events>

NULL Columns Excluded from Response

Q. I have a column that gets left out of the response if the value is NULL. How can I ensure the column is always returned, even when the value is NULL?

A. By default, FOR XML SQL commands omit columns if the value is null. To force them to be included, you can add the "ELEMENTS XSINIL" line to the end of your SQL command:

FOR XML PATH ('Individual'), ROOT('Individuals'), ELEMENTS XSINIL

With the syntax above, a null value column will be returned like this:

<cst_eml_address_dn xsi:nil="true" />

Alternately, for each column, you can wrap it in an ISNULL function as shown for the middle name field:

SELECT 
 cust.cst_key AS WIdnum,
 cust.cst_key AS WLoginName,
 ind.Ind_First_name AS WFirstname,
 isnull(ind.Ind_mid_name, '') AS WMiddleName,