Transact-SQL: STUFF FOR XML

  Devin Manno        January 2, 2013

If you ever work with 3rd party systems or databases, you know that solving any given problem requires a certain level of creativity. When integrating a 3rd party storefront ordering system with a 3rd party inventory system, I ran across an interesting challenge. The storefront system allows the creation of items under a handful of high-level types. One such type is a “kit” that is composed of multiple items of other high-level types. A decision was made to assign each kit to a single identifier in the inventory system. Unfortunately, this decision was not well thought out, as both the quantity and type of items in these kits could be dynamically selected by customers placing an order. This meant that employees preparing a customer’s order would need a detailed list of the type and quantity of each item purchased as part of a kit. The business challenge was to figure out how to extract the ordering data from the storefront system so that it would pass into the inventory system and include a list of the sub-pieces for any kits that were purchased. My solution: the FOR XML clause combined with the STUFF function in Microsoft’s Transact-SQL (T-SQL).

The FOR XML clause allows the programmer to return structured XML from a SELECT statement. There are 4 modes you can choose from when using FOR XML:

1. RAW
2. AUTO
3. EXPLICIT
4. PATH

I had no interest in the actual XML tags, so I chose to use the PATH option. Detailed descriptions of all 4 modes can be found on Microsoft’s Technet. The PATH option lets you specify the wrapper element name for the XML tags. In my case, I used the empty string to omit the wrapper completely, (FOR XML PATH('')). This left me with just the data for each returned rowset without the standard XML tags: <>.

SELECT field1, field2, ..., fieldN-1, 
     (SELECT (',' + KO.LineItemName + ':' + KO.LineItemQuantity) AS [text()]
          FROM dbo.KitsOrdered KO
          WHERE KO.Order_Id = O.Order_Id
          FOR XML PATH('')) AS 'OrderedKitDetails',
     fieldN+1, fieldN+2
FROM dbo.Orders O

The above code will create a field called OrderedKitDetails that will contain a comma-delimited list of LineItemName:LineItemQuantity pairs for each line item in an ordered kit. This will actually give comma-delimited results, as desired. However, it will also always return an extra comma at the beginning of the field: ... SELECT (',' + KO.LineItemName.... The STUFF function can be used to fix this.

As the name implies, the STUFF function allows you to ‘stuff’ data into a single field. More specifically, it allows the insertion of a string into another string, while simultaneously allowing the deletion of characters in the first string. The STUFF function is used by specifying the following parameters:

1. The first string
2. The location in the first string to insert the second string
3. The number of characters to delete from the first string at the insertion point of the second string, if needed
4. The second string

When used with the STUFF function, the SELECT ... FOR XML clause can create a single field of comma-delimited rowsets without any extra commas. Note: I needed commas for my purposes, but any character(s) can be used in place of a comma.

With (STUFF... FOR XML PATH('')) used as a sub query, the combined code becomes:

SELECT field1, field2, ..., fieldN-1, 
     (STUFF((SELECT (',' + KO.LineItemName + ':' + KO.LineItemQuantity) AS [text()]
          FROM dbo.KitsOrdered KO
          WHERE KO.Order_Id = O.Order_Id
          FOR XML PATH('')), 1, 1, '')) AS 'OrderedKitDetails',
     fieldN+1, fieldN+2
FROM dbo.Orders O

The STUFF function wraps the SELECT ... FOR XML PATH('') statement to remove the leading comma character. The first parameter to STUFF is the SELECT ... FOR XML PATH('') statement that will return the first string: un-tagged XML for each of the LineItemName:LineItemQuantity pairs, preceded by a comma. The second parameter, 1, tells STUFF to take the results of SELECT ... BY XML PATH('') and insert the second string at the first character. The third parameter, 1, says to delete the first character before inserting the second string. In this case, the first character at position 1 is the leading comma. The last parameter is the second string, in this case the empty string ''. Therefore, the code will insert the empty string at position 1 of the SELECT ... FOR XML PATH('') rowset after deleting the first (comma) character. The result is a comma-delimited list of LineItemName:LineItemQuantity pairs without an extra leading comma:

Edited 1/9/2013: Added table of sample results for clarity