The results of a SQL Server SELECT query can be transformed into an XML document by using the FOR XML command. Building XML documents using string functions – concatenating XML tags and embedding values between them using string operations – is not a recommended method. This post tries to look into some of the problems this approach can bring in.

Generating a simple XML fragment using String Concatenation.

Let us see a simple example that generates an XML fragment using string operation.

DECLARE @t TABLE (Category VARCHAR(50) )
INSERT INTO @t SELECT 'Cloths'

SELECT
	'<Category>' + Category + '</Category>'
FROM @t
/*
output: 

<Category>Cloths</Category>
*/

This produces a correct XML fragment, however the data type of the output value is VARCHAR. The output can be converted to an XML data type value by using the CAST() function as given below.

SELECT
	CAST ((
		SELECT
			'<Category>' + Category + '</Category>'
		FROM @t)
	 AS XML)
	 
/*
output: 

<Category>Cloths</Category>
*/

This produces a true XML fragment. However, this may have a few issues as explained below.

The code we saw above works for only one row. If your query returns more than one row, the query will generate an error. The following example demonstrates that.

DECLARE @t TABLE (Category VARCHAR(50) )
INSERT INTO @t SELECT 'Cloths'
INSERT INTO @t SELECT 'Cosmetics'

SELECT
	'<Category>' + Category + '</Category>'
FROM @t

SELECT
	CAST ((
		SELECT
			'<Category>' + Category + '</Category>'
		FROM @t)
	 AS XML)
	 
/*
Msg 512, Level 16, State 1, Line 9
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
*/	 	 

To fix this, you might need a different version of the query that can handle multiple rows. One option is to write a SELECT query that generates a string as given in the following example.

DECLARE @t TABLE (Category VARCHAR(50) )
INSERT INTO @t SELECT 'Cloths'
INSERT INTO @t SELECT 'Cosmetics'

DECLARE @xml VARCHAR(MAX)
SELECT @xml = ''

SELECT @xml = @xml + 
	'<Category>' + Category + '</Category>'
FROM @t


SELECT CAST(@xml AS XML)

/*
output:

<Category>Cloths</Category>
<Category>Cosmetics</Category>
*/

Encoding Problems

You might still come across a number of other problems when writing a solution that builds XML documents using string concatenation. One of those cases is when special characters are present in the values. For example, “&” is a special character in XML and it should be encoded when generating the XML document. Here is a new version of the above code that breaks when a new item with special characters is added.

DECLARE @t TABLE (Category VARCHAR(50) )
INSERT INTO @t SELECT 'Cloths'
INSERT INTO @t SELECT 'Cosmetics'
INSERT INTO @t SELECT 'Food & Beverages'

DECLARE @xml VARCHAR(MAX)
SELECT @xml = ''

SELECT @xml = @xml + 
	'<Category>' + Category + '</Category>'
FROM @t

SELECT CAST(@xml AS XML)

/*
Msg 9421, Level 16, State 1, Line 13
XML parsing: line 1, character 74, illegal name character
*/

In most cases, you will find that FOR XML is a better way to go. It can generate an XML document in the desired structure with almost no additional efforts. Here is the FOR XML version of the above code which is much more reliable.

DECLARE @t TABLE (Category VARCHAR(50) )
INSERT INTO @t SELECT 'Cloths'
INSERT INTO @t SELECT 'Cosmetics'
INSERT INTO @t SELECT 'Food & Beverages'

SELECT
	Category
FROM @t t
FOR XML AUTO

/*
<t Category="Cloths" />
<t Category="Cosmetics" />
<t Category="Food &amp; Beverages" />
*/

The output still needs some changes to match our original XML structure. This version generated values as attributes whereas we need them as elements. This can be achieved by adding ELEMENTS directive.

DECLARE @t TABLE (Category VARCHAR(50) )
INSERT INTO @t SELECT 'Cloths'
INSERT INTO @t SELECT 'Cosmetics'
INSERT INTO @t SELECT 'Food & Beverages'

SELECT
	Category
FROM @t t
FOR XML RAW(''), ELEMENTS

/*
<Category>Cloths</Category>
<Category>Cosmetics</Category>
<Category>Food &amp; Beverages</Category>
*/

What we just generated is an XML fragment not a document. An XML document needs to have a single ROOT element. Let us generate a root element by adding the ROOT directive to the above query.

DECLARE @t TABLE (Category VARCHAR(50) )
INSERT INTO @t SELECT 'Cloths'
INSERT INTO @t SELECT 'Cosmetics'
INSERT INTO @t SELECT 'Food & Beverages'

SELECT
	Category
FROM @t t
FOR XML RAW(''), ELEMENTS
, ROOT('Categories')

/*
<Categories>
  <Category>Cloths</Category>
  <Category>Cosmetics</Category>
  <Category>Food &amp; Beverages</Category>
</Categories>
*/

FOR XML can be used with AUTO, RAW, PATH and EXPLICIT to generate XML documents with the desired structure and shape. FOR XML may be more efficient than building XML documents using string concatenation. In addition, code that uses string concatenation should take care of encoding special characters which may be very complex in many cases.