Tag Archives: XML

XML AUTO – Basic Examples

20 Jun


Continuing my series of XML articles I’m going to move from XML RAW to XML AUTO with a handful of examples that share features between the two. Continue reading

XML RAW, ELEMENTS and ROOT – Basic Examples

29 Apr


Most systems that I have worked with make use of the XML data type. I don’t find it to be the most intuitive data type to work with but most aspects of it can be built upon from a few basic examples. This will be part of a developing series that will cover XML and JSON. Continue reading

Preserving White Space in ‘Empty’ XML Elements

29 Dec

When setting the value of an XML column, so-called ‘insignificant’ white space can be removed. Generally this is not an issue but if the white space is required there are a couple of ways to prevent this.
Continue reading


19 Dec

I tend not to use XML very often, so have limited experience with it. Whilst reading Itzik Ben-Gan’s (b|t) book for 70-761 I came across XMLNAMESPACES for the first time.
The section on this aspect is quite brief, so this blog contains the additional work I used to ensure I understood it.
Continue reading

XML and STUFF for a simple CSV column

27 Dec

I’m not a big fan of XML via T-SQL – far too long-winded a monster for my liking. However, there is no denying the usefulness of it and the fact that on occasions it simply doesn’t matter if you like an aspect of SQL Server, you may simply have to learn to use it. If I could pick and choose which bits I get to play with I suspect I wouldn’t be a DBA for long.

One nice little ‘trick’ with the XML commands that I’m grateful for is used to provide a comma-separated list of data. It’s an interesting solution that is often shown to people who need such a thing and is far faster than other solutions I’ve seen.
As an example, I would like to produce a csv list of the surnames from the following data:


To obtain the following results:


First of all – the test data:

CREATE TABLE #PersonList( 
		Forename	VARCHAR (50), 
		Surname		VARCHAR(50) 
INSERT INTO #PersonList ( Forename, Surname ) 
VALUES  ( 'John', 'Smith'), 
		( 'Tom', 'Brown'), 
		( 'Tom', 'Sawyer'), 
		( 'Harry', 'Flashman'), 
		( 'Anna', 'Howe'), 
		( 'Julian', 'Sorel'), 
		('Sherlock', 'Holmes'); 
FROM #PersonList; 

The command to produce the csv data is:

SELECT STUFF((SELECT ', ' + Surname  
                            FROM #PersonList 
		          ORDER BY Surname 
                            FOR XML PATH('')), 1, 2, ''); 

It comprises of two commands. The inner command is FOR XML in it’s simplest form, with a comma added to the selected data:

SELECT ', ' +  Surname 
           FROM  #PersonList 
           ORDER BY Surname 
           OR XML PATH(''); 

This is a very simple example of the XML command. Normally the PATH would have something within it and would therefore produce a very basic XML.
For example:

SELECT Surname  
         FROM  #PersonList 
         FOR XML PATH('test'); 

will produce XML output thus:


Not valid XML in this form (hence the red underline) but you get the idea.

Change the command to add a comma between each surname and remove the element name (the ‘test’) and you get a strange XML value like this:


The next requirement is to remove the leading comma and convert this back to a character field. This is where STUFF comes in. In this case the first parameter for STUFF is the XML query.
Then it looks at the first character position of the query result and replaces the two characters from that point with a zero length field (”).
As STUFF returns character data or binary data depending upon the data type it is processing, in this case it returns the result as a character field, with the leading comma removed.
Two problems solved with one command.

I’ve added the ORDER BY just to make the data look more organised and of course the ‘inner’ XML query can be more involved than this one but it should give an idea of how to use this for this particular purpose:

SELECT STUFF((SELECT ', ' + Surname 
                            FROM #PersonList 
		          ORDER BY Surname 
                            FOR XML PATH('')), 1, 2, '');