THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

Handling special characters with FOR XML PATH('')

Because I hate seeing > or & in my results…

Since SQL Server 2005, we’ve been able to use FOR XML PATH('') to do string concatenation. I’ve blogged about it before several times. But I don’t think I’ve blogged about the fact that it all goes a bit wrong if you have special characters in the strings you’re concatenating.

Generally, I don’t even worry about this. I should, but I don’t, particularly when the solution is so easy.

Suppose I want to concatenate the list of user databases on my system, in alphabetical order. Easy: Note the lack of

     (select ', ' + name -- Note the lack of column name
     from sys.databases
     where database_id > 4
     order by name
     for xml path('')
   , 1, 2, '') as namelist;

This is easy, and it just works. The STUFF command lets me strip the leading comma and space, and my list of database names is done.

But if I wanted to list the names of database with triangular brackets around them… then that’s bit more complicated.

     (select ', <' + name + '>'
     from sys.databases
     where database_id > 4
     order by name
     for xml path('')
   , 1, 2, '') as namelist;

It still runs, but I my results don’t show the triangular brackets, it shows &lt;databasename&gt;, &lt;databasename2&gt;. It’s not what I want to see. I effectively need to HTMLDecode the results – but T-SQL doesn’t have a function like that.

However, if I actually make my FOR XML call return actual well-formed XML, then I can extract the contents out, and it returns the data to me in its original (correct) form.

     (select ', <' + name + '>'
     from sys.databases
     where database_id > 4
     order by name
     for xml path(''), root('MyString'), type
   , 1, 2, '') as namelist;

To be valid XML, it needs a ROOT element. I also need to tell it to be XML, using the TYPE directive.

But this lets me hook into the value of /MyString[1], and return that as varchar(max).

And it works, my data comes back as <databasename>, <databasename2>, etc.

It’s a habit I need to use more often.

Edit: I can also skip the ROOT element (but keep TYPE) and use .value('.','varchar(max)') - but for some reason it's always felt strange to me to be returning something which I declare to be XML (using TYPE) without it being well-formed.

Published Thursday, April 15, 2010 3:35 PM by Rob Farley
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Ben Laan said:

Cool Trick!! That's the best CSV hack I've seen.. Still, I'd prefer if MS would ship a few aggregate functions like this instead.. WITHOUT requiring CLR..

April 15, 2010 2:38 AM

Brad Schulz said:

Hi Rob...

You don't absolutely need the ROOT directive... SQL will still gladly convert whatever string you put together as XML via the TYPE directive... it may not be well-formed XML in the pure sense, but we don't care (because our goal is not to create true XML but to take advantage of it for a trick), and SQL will not yell at you about it.

Just doing this will do the trick:

...FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')

or, for a teensy-weensy extra bit of performance:

...FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)')

April 15, 2010 1:14 PM

Alejandro Mesa said:


That is a nice trick, but the root element is not needed though.



    (select ', <' + name + '>'

    from sys.databases

    where database_id > 4

    order by name

    for xml path(''), type


  , 1, 2, '') as namelist;



April 15, 2010 2:02 PM

Rob Farley said:

Yes, true. Thanks Brad & Alejandro. I've put an "Edit" on my post accordingly.

April 15, 2010 8:54 PM

erin said:

perfect! i was starting to worry i'd have to write something dreadful to handle concatenating strings with special characters. thank you!!

August 27, 2010 1:37 PM

Derek said:

Hi Gents..

I am exporting a view to XML like your example and too have issues with the < > characters.. When I do try your examples, the fields with the < > characters in them now show them correctly, but my other fields..  example with out your extra code..  note each fieldname has <> and </> around them..  

<PrimaryOffice>xmlns="PrimaryOffice" &lt;Name&gt;Croydon&lt;/Name&gt;</PrimaryOffice><ApplicantID>75E89A8D-45DC-4F1C-9146-C70B06239C58</ApplicantID><WinkworthID>WnkCroy0</WinkworthID>

and in the second example it has reversed the <> situation

xmlns="PrimaryOffice" <Name>Croydon</Name>75E89A8D-45DC-4F1C-9146-C70B06239C58WnkCroy0falsefalse

I hope you can shed some light..  Kind regards,


October 13, 2010 10:05 AM

Derek said:

..In fact I have just reaslised it is removing the field names and not just the <> and </>..  I am sure I am missing a setting out?

Kind regards,


October 13, 2010 10:51 AM

Vedika said:

Cool trick...

October 28, 2010 3:48 AM

Esteban said:

Thanks for this post. It help me solve my issue. But it created another one.

Now I must SET ARITHABORT ON in order to use this.

So you know why? Is there any way to avoid it?

January 21, 2011 6:06 PM

Rob Farley said:


ARITHABORT is needed because replacing error parts with NULL would be the wrong move here.

(Therefore, MS deemed it a requirement, as is the case whenever an index is created, or something like xml. ARITHABORT is generally for DML populating fields, so that you can tell it to store NULL if something hasnt worked. Ignoring arith errors doesn't apply when there's a greater dependency between the target object and multiple rows like this)


January 21, 2011 6:52 PM

Rob Farley said:


Sorry I hadn't replied yet - slipped off my todo list. :(

I think there may be som confusion between using the XML type to do concatenation of a single field and handling XML or multiple fields here.

Could you show me your query and desired output so that we can work it out?


January 21, 2011 6:56 PM

Ranu Mandan said:

this is the best hack ever..Thanx a ton !!

May 27, 2011 9:34 AM

sai said:

what if my select statement is returning strings in which i have say ASCII24 or ASCII13 etc. FOR XML Could not serialize the data for node because it contains a character (0x00..) which is not allowed in XML. To retrieve To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

how to solve this case. svutukuru at hotmail dot com..........

January 20, 2012 4:08 PM

Curiosity said:

I am serious here but is there anybody here able to  convert sql special characters to XML characters for example

& &amp;

< &lt;

> &gt;

" &quot;

' &apos;

February 17, 2012 7:16 AM

Rob Farley said:

To the XML characters?

Something like this should do it for you:

select '&' for xml path('')

Hope this helps... :)

February 18, 2012 3:25 AM

agam said:

On the lines of your solution, i am trying to solve &amp problem.

But running the below query in sql studio gives this error:

Incorrect syntax near '.'.

it does not identify .value, or is there some other problem.

select ShipCompanyNames.list FROM

                          (SELECT distinct (LocationTemplates.CompanyName) + ','

                           FROM Shipment

                           INNER JOIN LocationTemplates ON LocationTemplateID = Shipment_SPI.ShipLocationTemplateID

                           WHERE Shipment.InvoiceGroupID =1896

                           FOR XML PATH (''),type).value('.','varchar(max)') AS ShipCompanyNames(list)

March 21, 2012 7:19 PM

Rob Farley said:

Hi agam,


select ShipCompanyNames.list FROM

(SELECT distinct (LocationTemplates.CompanyName) + ','

FROM Shipment

INNER JOIN LocationTemplates ON LocationTemplateID = Shipment_SPI.ShipLocationTemplateID

WHERE Shipment.InvoiceGroupID =1896

FOR XML PATH (''),type).value('(./text())[1]','varchar(max)') AS ShipCompanyNames(list);

But you might also prefer to use "GROUP BY LocationTemplates.LocationTemplateID, LocationTemplates.CompanyName" instead of DISTINCT (or whatever ID makes CompanyName unique).

March 21, 2012 7:41 PM

agam said:

thanks for you reply but no luck.

Fails with the same error. "Incorrect syntax near '.'.

March 21, 2012 8:47 PM

Rob Farley said:

Sorry - yes. Try:

select ShipCompanyNames.list FROM




SELECT distinct (LocationTemplates.CompanyName) + ','

FROM Shipment

INNER JOIN LocationTemplates ON LocationTemplateID = Shipment_SPI.ShipLocationTemplateID

WHERE Shipment.InvoiceGroupID =1896


(''),type).value('(./text())[1]','varchar(max)')) AS ShipCompanyNames(list);

You see, the FOR XML PATH...value(...) produces a single value. So you need to wrap that up in another SELECT to make it a table-valued expression. Notice the extra "SELECT (", and the ")" after the varchar(max).

March 21, 2012 9:10 PM

agam said:

thanks Rob, that worked like a dream.

March 22, 2012 12:12 PM

Sarah M. said:

Great post!  Solved my problem but created a new one like Esteban mentioned.  The script worked great in sql but when I pulled it into Word documents it returned a warning.  I had to add the command, SET ARITHABORT ON.  Works great now but should I be aware of any potential issues?



April 13, 2012 3:48 PM

swapna said:

Thanks Rob!!!! helped me solve my issue with in a minute :)

April 16, 2012 8:20 PM

Rob Farley said:

Hi Sarah,

It should be fine.


April 19, 2012 5:18 PM

Hakim Hunter said:

Ok this is great and it works on a one by one basis but i have been struggling with this &amp; for too long and it is killing me. Here is the code:


-- Add the parameters for the stored procedure here



-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.


   -- Insert statements for procedure here


--Start Builders

Select GETDATE() as "@DateGenerated",

--Start Corporation

(Select 'CORP1' as "CorporateBuilderNumber" ,'GA' as "CorporateState", 'Paco Homes' as "CorporateName",

--Start Builder

(Select  rank() OVER (ORDER BY Location.MMarketName) as "BuilderNumber",MMarketName as "BrandName",MMarketName as "ReportingName",

'' as "DefaultLeadsEmail",

--Start Subdivision

(SELECT NeighborhoodID as "SubdivisionNumber", NName as "SubdivisionName",  

--start Sales Office


--Start Address

(SELECT '0' as "@OutOfCommunity", NStreet1 as "Street1", NStreet2 as "Street2", NCity as "City", NState as "State", NZIP as "ZIP", 'USA' as "Country"

From PC_Neighborhood "NHDADDR"

Where "NHDADDR".NeighborhoodID = Neighborhood.NeighborhoodID

FOR XML PATH('Address'), TYPE),

--End Address

--Begin Phone Number

(SELECT Left(NPhone,3) as "AreaCode", SubString(NPhone,5,3) as "Prefix", Right(NPhone,4) as "Suffix"

From PC_Neighborhood "NHDPH"

Where "NHDPH".NeighborhoodID = Neighborhood.NeighborhoodID


--End Phone Number

NEmailAddress as "Email", dbo.ulf_StripHTML (REPLACE((REPLACE((REPLACE(Left(NHours,99), '&gt;','>')),'&lt;','<')),'''','')) as "Hours"

From PC_Neighborhood "NHDSO"

Where "NHDSO".NeighborhoodID = Neighborhood.NeighborhoodID

FOR XML PATH('SalesOffice'), TYPE),

--End Sales Office

-- Start Driving Directions

dbo.ulf_StripHTML (REPLACE((REPLACE((REPLACE(Left(NCopyMap,999), '&gt;','>')),'&lt;','<')),'&rsquo;','&#39;')) as "DrivingDirections",

-- End Driving Directions

-------------------------My Big Problem Area --------------------------

--Start Description

--'<![CDATA[' + (REPLACE((REPLACE((REPLACE((Left(NCopyXml,1999)), '&gt;','>')),'&lt;','<')),'''','')) + ']]>' as "SubDescription",

--REPLACE((REPLACE((REPLACE(NCopyxml, '&gt;','>')),'&lt;','<')),'''','') as "SubDescription",

--(Select NCopyXML from PC_Neighborhood nd where nd.neighborhoodid = Neighborhood.NeighborhoodID FOR XML PATH('SubDescription'),root('MyString'), type).value('/MyString[1]','varchar(1999)') as SubDescription,

(select Replace((Select NCopyXML from PC_Neighborhood nd where nd.neighborhoodid = Neighborhood.NeighborhoodID for xml path(''), root('MyString'), type


  , '&rsquo;','&#39;')) as SubDecription,

--End Descriptions

-------------------------------------End My Big Problem Area ------------------

--Start SubImage

(SELECT Top 6 rank() OVER (ORDER BY LLinkSequence) as "@SequencePosition", 'URL' as "@ReferenceType",

'">' + LLinkLocation

FROM PC_MediaLinks "NImage"

WHERE "NImage".LReferenceID= Neighborhood.NeighborhoodID

and LinkTypeID = '1' Order By "NImage".LLinkSequence

FOR XML PATH('SubImage'), TYPE),

--Direction Map

(SELECT 'DrivingDirectionsMap' as "@Type", '1' as "@SequencePosition", 'URL' as "@ReferenceType",

'">' + LLinkLocation

FROM PC_MediaLinks "NImage"

WHERE "NImage".LReferenceID= Neighborhood.NeighborhoodID

and MMediaTypeID='15'

and LinkTypeID = '1' Order By "NImage".LLinkSequence

FOR XML PATH('SubImage'), TYPE),

--End Direction Map

''+URLShortName as "SubWebsite",


--Start Plan

(Select 'SingleFamily' as "@Type", "Plan".FloorplanID as "PlanNumber",

FPName as "PlanName",

(Select Min(minav.APrice) from PC_Availablehome minav where minav.floorplanid = "Plan".FloorplanID and minav.Neighborhoodid = Neighborhood.NeighborhoodID)  as "BasePrice" ,

FPSquareFootage as "BaseSqft",

FPFLoors as "Stories",

FPBathrooms as "Baths",

FPHalfBathrooms as "HalfBaths",

(Case When FPOwnersSuite='1' then 'Down' When FPOwnersSuite='0' then 'Up' END) as "Bedrooms/@MasterBedLocation", FPBedrooms as "Bedrooms/text()",

FPGarage as "Garage",

FPBasement as "Basement",

dbo.ulf_StripHTML (REPLACE((REPLACE((REPLACE(FPXMLDescription, '&gt;','>')),'&lt;','<')),'''','')) as "Description",

--Start PlanImages

   --Elevation Images


(SELECT Top 5 ElevationID as "@SequencePosition", 'URL' as "@ReferenceType",

'' + LLinkLocation

FROM PC_MediaLinks "EImage"

WHERE "EImage".LReferenceID= "FPEL".ElevationID

and LinkTypeID = '7' Order By "EImage".LLinkSequence

FOR XML PATH('ElevationImage'), TYPE),

  --End Elevation Images

  --Floorplan Images

(SELECT LLinkID as "@SequencePosition",  'URL' as "@ReferenceType",

'' + LLinkLocation  

FROM PC_MediaLinks "fpim"

WHERE "fpim".LinkTypeID = '4'

AND "fpim".MMediaTypeID = '8'

AND "fpim".LReferenceID = "Plan".FloorplanID

Order By LLinkSequence

FOR XML PATH('FloorPlanImage'), TYPE)

--End Floorplan Images

From PC_Elevations "FPEL"

Where "FPEL".FLoorplanID = "Plan".FloorplanID

FOR XML PATH(''), TYPE,root('PlanImages')),

--End PlanImages

--Start Spec

(SELECT 'SingleFamily' as "@Type", AvailableHomeID as "SpecNumber",

--Start SpecAddress

(Case When ALotnumber is Null Then '0' Else ALotnumber END) as "SpecAddress/SpecLot",

(Case When AStreet1 is Null Then nnhd.NStreet1 Else AStreet1 END) as "SpecAddress/SpecStreet1",

(Case When AStreet2 is Null Then nnhd.NStreet2 Else AStreet2 END) as "SpecAddress/SpecStreet2",

(Case When ACity is Null Then nnhd.NCity Else ACity END) as "SpecAddress/SpecCity",

(Case When AState is Null Then nnhd.NState Else AState END) as "SpecAddress/SpecState",

(Case When AZIP is Null Then nnhd.NZip Else AZIP END) as "SpecAddress/SpecZIP",

--'USA' as "SpecAddress/SpecCountry",

--End SpecAddress

AIsmodel as "SpecIsModel",

CONVERT(money, APrice) as "SpecPrice",

--AName as "SpecName",

ASquareFootage as "SpecSqft",

AFLoors as "SpecStories",

ABathrooms as "SpecBaths",

AHalfBathrooms as "SpecHalfBaths",

(Case When AOwnersSuite='1' then 'Down' When (AOwnersSuite='0' or AOwnersSuite is Null) then 'Up' END) as "SpecBedrooms/@MasterBedLocation", FPBedrooms as "SpecBedrooms/text()",

AGarage as "SpecGarage",

ABasement as "SpecBasement",

dbo.ulf_StripHTML (REPLACE((REPLACE((REPLACE(AXMLDescritption, '&gt;','>')),'&lt;','<')),'''','')) as "SpecDescription",  

(Select Top 1

(SELECT Top 5 rank() OVER (ORDER BY LLinkSequence) as "@SequencePosition", 'URL' as "@ReferenceType",

'' + LLinkLocation

FROM PC_MediaLinks "Image"

WHERE "Image".LReferenceID= "Homes".AvailableHomeID

and LinkTypeID = '2'

FOR XML PATH('SpecElevationImage'), TYPE),

--Floorplan Images

(SELECT Top 5 rank() OVER (ORDER BY LLinkSequence) as "@SequencePosition",  'URL' as "@ReferenceType",

'' + LLinkLocation  

FROM PC_MediaLinks "fpim"

WHERE "fpim".LinkTypeID = '4'

AND "fpim".MMediaTypeID = '8'

AND "fpim".LReferenceID = "Plan".FloorplanID

Order By LLinkSequence

FOR XML PATH('SpecFloorPlanImage'), TYPE)

FROM PC_AvailableHome "lkHomes"

FOR XML PATH('SpecImages'), TYPE)

--End Floorplan Images

FROM PC_AvailableHome "Homes"

inner join PC_Neighborhood nnhd on

nnhd.NeighborhoodID = "Homes".NeighborhoodID

WHERE "Homes".NeighborhoodID = Neighborhood.NeighborhoodID

and "Homes".FloorplanID = "Plan".FloorplanID

and "Homes".AActive = '1'

and "Homes".AHSID <> '8'

and "Homes".AXMLExport = '1'

and "Homes".AforSale = '1'

and "Homes".APrice > '2'

and ("Homes".AUnderContract = '0' or "Homes".AUnderContract is Null)


--End Spec

FROM PC_Floorplans "Plan"

left Join PC_AvailableHome "PlanHomes" ON

"PlanHomes".FloorplanID = "Plan".FloorplanID

inner join PC_MarketingStyle "MHS" On


Where "PlanHomes".NeighborhoodID = Neighborhood.NeighborhoodID

and "PlanHomes".AActive = '1'

and "PlanHomes".APrice > '2'

--and "PlanHomes".AHSID <> '8'

and "PlanHomes".AXMLExport = '1'

and "PlanHomes".AforSale = '1'

and ("PlanHomes".AUnderContract = '0' or "PlanHomes".AUnderContract is NUll)


--End Plan

FROM PC_Neighborhood Neighborhood

Where Neighborhood.MMarketID = Location.MMarketID

and Neighborhood.NActive = '1'

FOR XML PATH('Subdivision'), TYPE)

--End Subdivision

FROM PC_Market Location

FOR XML PATH('Builder'), TYPE)

--End Builder

FOR XML PATH('Corporation'), TYPE)

--End Corporation


--End Builders


I have tried a bunch of solutions but the For XML changes the & at the beginning of my special characters HTML to &amp; every time. It is killing me. for example &rsquo; becomes &amp;rsquo; Do you have any fix.

the field outputs like this


&lt;strong&gt;Ranked 2012 by Golfweek &amp;ndash; Ask About Free Golf Membership with Home Purchase for a Limited Time!&lt;/strong&gt;&lt;/p&gt;


Championship golf, resort-style amenities, charming streetscapes and friendly front porches &amp;ndash;  has it all in one convenient address unlike any other. Hit the links, dive in at the pool, join neighbors for a friendly tennis match or stretch your legs along miles of sidewalks and neighborhood parks. Whatever your age or interest you&amp;#39;re sure to love the way of life with recreation and relaxation at your doorstep all just 5 minutes with easy access&lt;/p&gt;


A variety of homes from Paco&amp;#39;s award-winning portfolio offer innovative plans designed for the way you want to live with first floor owner&amp;#39;s suites available, lots of flexible space, today&amp;#39;s best features and our highest standards for quality and efficiency. Personalize your new home inside and out at Paco&amp;#39;s assistance of a dedicated designer. And all Paco homes are protected by the Paco 5 Year &amp;ndash;Warranty, homebuilding&amp;#39;s best.&lt;/p&gt;


June 8, 2012 8:27 AM

Rob Farley said:


That's a big query you have there...

Everywhere you have "FOR XML PATH(''), TYPE)", put ".value('(./text())[1]','varchar(max)')" on the end of it. See how that works for you. I haven't looked in detail, but it's probably a good start.


June 8, 2012 9:01 AM

john said:

Thanks agam!!

August 16, 2012 11:32 AM

Vikas said:

Thanks, my problem solved.

September 10, 2012 8:27 AM

Klaus said:


I have german umlaute (?, ä, ..) in my sql table

Is there a way to xml-lize them during SELECT ... FOR XML PATH?



September 27, 2012 5:30 AM

Rob Farley said:

Yes. If you don't put the .value stuff in there, it'll encode it for you.

September 27, 2012 5:57 AM

Dan said:

Hi Rob.  I just want to thank you for your time.  I was having issues with special character codes being returned in my query when using XML PATH ('').  You suggestion to use


worked perfect.  Thanks!

November 13, 2012 12:20 PM

Nebojsa said:

Great post!

It's very helpful.

February 11, 2013 6:01 AM

Nitika said:

Thanks Rob, very helpful for my Report Data fetching across multiple rows :).

April 5, 2013 12:15 AM

Ben said:

Brilliant. Cheers.

April 17, 2013 8:20 AM

Erik said:

I was creating an SQL query where old data was to be displayed color coded differently than new data entered and this article (and comments) were a great help. Thanks.

April 22, 2013 4:56 PM

SH said:

This is the best way to kepp HTML Tags while using XML PATH!

Thank you!!!

June 10, 2013 12:33 AM

Kon said:

Rob - This has saved me a heap of time!

In my use though (SQL Server 2005), I have found that an ORDER BY is ignored. eg. I have a custom sort order (int) that I would like the order of the concatenation to 'read'.

My workaround thus far, is to create a temp table and have everything in the correct order, and then do XML PATH on that.

Anyway, I thought Id post a comment here for the benefit of all the googlers (me being one of them!)


June 13, 2013 9:07 AM

Rob Farley said:

Hi Kon - ordering should work. My email address is on the right, so drop me a line and we'll figure out what's going wrong.

June 13, 2013 9:46 AM

Jun Reyes said:

Thumbs up!

July 8, 2013 5:28 AM

Tusky said:

Hi Rob

can you help me with resolving issue with '&#X0D' value after each word like as you were getting '&gt'after each name.

August 1, 2013 10:32 AM

Rob Farley said:

Yes Tusky - that's what this post is about. Follow the instructions and if should do the trick.

August 1, 2013 5:19 PM

Tusky said:

Thanks Rob

August 2, 2013 8:51 AM

ARiest said:

This absolutely brillant code that I've used more than once.  Thanks Rob!

August 2, 2013 4:32 PM

Jay said:

Thanks Rob. Would you please explain the use of below code


September 4, 2013 8:44 AM

Rob Farley said:

Hi Jay,

TYPE tells the result to be returned as the XML type rather than a string.

This means that the XML allows a method to be called to get the content of the XML, with special characters being returned in their real form. That function needs to tell it where that value should be taken from, and what type to cast it into.

Hope this helps,


September 4, 2013 8:53 AM

Luca said:

Hi there,

I have tried this solution but am still encountering the error message below. Any assistance would be much appreciated...

"FOR XML could not serialize the data for node 'NoName' because it contains a character (0x000B) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive."

SELECT  programid,


   SELECT ',javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("ctl00$_$ctl00$_$ctl00$ctl00$bcr$ctl00$_$form$_$btnSubmit", "", true, "", "", false, true)) ' + createddate + note

   FROM #TempTable

   WHERE (programid = Results.programid)

   FOR XML PATH(''),type).value('(./text())[1]','varchar(max)')

 ,1,2,'') as NameValues

FROM   #TempTable Results

GROUP BY programid

October 8, 2013 11:17 AM

Siva Sankar Gorantla said:

This is the best solution forever.

October 17, 2013 3:16 AM

Siva Sankar Gorantla said:

Thank you very much Rob.

October 17, 2013 3:17 AM

Sai said:

What if the field you have to concatenate is having unicode characters in it or different language other than english. how do we concatenate the string then

October 28, 2013 2:11 PM

Milacay said:

Very good Trick! I was stuck because the string contains HTML codes, but I found this.  Thank you

October 28, 2013 11:36 PM

V286 said:

Thanq So Much I never Forget U r Help.....

October 30, 2013 2:25 AM

Siva Sankar Gorantla said:

Thank you so much ...I used to get ampersand in my dreams as I have critical bug because of ampersand issue.Now I am very happy...Thank you great job

November 15, 2013 9:15 AM

Liz said:

Hey Rob, Hoping you can help. I am using the following statement to concatenate values from a field.

STUFF((SELECT ServiceName FROM ServiceHistory as sh2

inner join Service as s2 on sh2.ServiceKey = s2.ServiceKey

where sh2.PersonKey = p.PersonKey and sh2.DateofService = sh.DateOfService

FOR XML PATH('')),1,1,'') as ServicesProvided

Note: the 'p' and 'sh' table alias refer to table aliases in the much larger select statement which actually inserts all the data into a temp table.

Some of the entries for the field Servicename contain ampersands and I need to fix it so that I don't get the &amp in the xml output for Servicename, rather get the '... & ....'

December 2, 2013 2:45 PM

Rob Farley said:

Hi Liz,


STUFF((SELECT ServiceName FROM ServiceHistory as sh2

inner join Service as s2 on sh2.ServiceKey = s2.ServiceKey

where sh2.PersonKey = p.PersonKey and sh2.DateofService = sh.DateOfService

FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'') as ServicesProvided

Hope this helps,


December 2, 2013 3:13 PM

Rob Farley said:

Oops, missed some messages.

Sai: Use nvarchar instead of varchar as your target type.

Luca:  I'm not sure where those strange characters are coming from. Maybe check the note field to see if everything is normal in there?

December 2, 2013 3:18 PM

Liz said:

Thanks for responding so quickly Rob, unfortunately it didn't work

December 2, 2013 4:47 PM

Liz said:

Ignore my previous comment Rob, I should have double checked my code first. That's what I get for copying and pasting! It works great, thanks so much for your assistance!!!!


December 2, 2013 4:53 PM

tareq said:

Thanks man. Thanks a lot. its worked.....

January 16, 2014 6:28 AM

patrick said:

Hi Rob!

Would this also work in my case?

I’ve the following tables in a spatialite database:

This tables are filled as follows:







baum_ID, boden_verd      




What I need ist the following:





I found this code-example (already adapted for my needs) but it returns an syntax error near "for" and I don't realy know where I'am wrong:

SELECT baumkataster.baum_ID AS baum_ID,

stuff((select  DISTINCT  ', ' +  boden_verd.boden_verd

            from boden_verd

            WHERE ','+baumkataster.boden_verd+',' LIKE '%,'+boden_verd.boden_verd_ID+',%'

            for xml path(''),type).value('.','nvarchar(max)'), 1, 2, '' )  AS boden_verd

FROM baumkataster;

Is this possible? Thanks for your answers!!


January 26, 2014 3:37 PM

Rob Farley said:

Hi Patrick,

Sounds like your system might not support "for xml"

Start by testing:

select boden_verd

from boden_verd

for xml path('');

If that gives you an error, you're out of luck with your environment.


January 26, 2014 5:41 PM

patrick said:

Hi Rob, thanks for answering!

that was the proplem. Meanwhile I got the same answers at other forums...

Thanks anyway!

January 27, 2014 10:41 AM

William said:

Rob, your post saved me much time and frustration.  Just wanted to say thanks!

February 23, 2014 2:30 PM

Sherwin said:

Hi Rob,

I have the following query.  the drecontent is not all concatenated together without any space, which is not what i want.  I need to drecontent to show all the data on the the current record.  COLUMN: VALUE would be preferable but .value is just fine.  But i need them to be have space or delimited.




select   *

from tabular_coordinate where tabular_coordinate.form_id = M.form_id

FOR XML PATH('tabular_coordinate'), TYPE),



   FROM visit

where =

 FOR XML PATH(''), TYPE ).value('(.','VARCHAR(MAX)')

  As drecontent

FROM visit AS M


March 2, 2014 2:01 AM

Sherwin said:

Sorry just found a problem on the previous post:

Heres what I really have.

 FOR XML PATH(''), TYPE ).value('.','VARCHAR(MAX)')

March 2, 2014 2:04 AM

Rob Farley said:

Hi Sherwin,

If you want spaces in there, put spaces in between the data being returned by your subquery. Like this:

SELECT CONCAT(id, ' ', col2, ' ', col3) FROM visit ...

Hope this helps,


March 2, 2014 2:28 AM

Sherwin said:

Thanks for the response.

Thats what I cant do, specify the the column names, as the table is being dynamically created.  Users could redefine the fields.  So the only way to get them is by *.

Do you have any other advice?


March 2, 2014 10:22 AM

Rob Farley said:

If the table is being dynamically generated, why not dynamically generate a view over it that includes what you need?

March 2, 2014 3:28 PM

Sandhya said:

Hi Rob,

  I have a sql query, which will fetch some of the columns from the table. One of the columns may contain special characters like &,",',> and <. I need a query which will fetch the column value with the special characters converted to

> as &gt;

< as &lt;

“ as &quot;

& as &amp;

’ as &apos;

Please help me out.

March 6, 2014 8:48 AM

Rob Farley said:

Hi Sandhya,


SELECT col1, col2, (SELECT col3 AS [*] FOR XML PATH('')) AS col3Coded, col4

FROM dbo.SomeTable;

The "AS [*]" is there to remove the name of the column.


March 6, 2014 3:09 PM

SandoLuke said:

Nice solution, but I still have problems with special chars:



    (select ', <' + name + char(CAST(0x001A as int)) + '>'

    from sys.databases

    where database_id > 4

    order by name

    for xml path(''), root('MyString'), type


  , 1, 2, '') as namelist;

Any ideas?

April 3, 2014 8:38 AM

Rob Farley said:

Hi Luke,

Your 0x001A character isn't allowed in XML, so sadly, this method won't work at all. You may prefer to use a different character (pipe, perhaps - or even a longer string - several pipes maybe), and then do a REPLACE at the end.



   (select ', <' + name + '|' + '>'

   from sys.databases

   where database_id > 4

   order by name

   for xml path(''), root('MyString'), type


 , 1, 2, ''),'|',char(CAST(0x001A as int))) as namelist;




   (select ', <' + name + '|' + '>'

   from sys.databases

   where database_id > 4

   order by name

   for xml path(''), type


 , 1, 2, ''),'|',char(CAST(0x001A as int))) as namelist;

...if you prefer to not use ROOT.

Hope this helps


April 3, 2014 6:50 PM

SandoLuke said:

Thank you for the answer.

Sadly I can't use other characters because I'm reading from a database on which I have no control.

The only solution was to use replace in combination with a unique placeholder.

I'm starting to hate XML... to solve this problem I had to change my application (c# winform) because the XML object had the same problem, so I had to use a String, pass it to a Procedure (changing the XML parameter in VARCHAR(MAX)).

Then search for '&#x1A;' , replace it with the SQL equivalent CHAR(0x001A) for the update operation and use a placeHolter to store the data as XML.  A mess...

April 7, 2014 8:34 AM

Rob Farley said:

Yeah, sounds like it.

April 7, 2014 8:37 AM

Ali said:

Hi Rob, I have used your XML solution to get around the special char but unfortunately my data must contain <Tab>s (user request) as a delimiter! These <tab>s are getting converted to spaces!!! I need the char(9) , <tab>s to be intact and not be converted in the xml process.  Is there a trick to this?  Thank you in advance.

Select  [WS Summary] = convert(varchar(max), stuff((Select isnull((w.Title + @tab + convert(varchar,w.BeginDate,101) + @tab + (LTRIM(RIGHT(CONVERT(datetime, BeginTime, 109), 7)) + @tab + LTRIM(RIGHT(CONVERT(datetime, EndTime, 109), 7))) + @tab + w.Address )   ,'') + @tab as [data()]

from regRegistrationWorkshop regw

inner join ProjectWorkshop w on regw.projectid = w.ProjectID and regw.WorkshopID = w.WorkshopID

where regw.customerid = rw.customerid and regw.ProjectID = rw.projectid

FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,0,''))

April 7, 2014 1:19 PM

John McC said:

Thanks.  Great soulution for an annoying "problem"

July 9, 2014 5:59 AM

Vijay Sharma said:

I have the query like below

(SELECT STUFF((SELECT ',' + 'SUM(['+LTRIM(RTRIM(@metric_item)))+']) AS' + MetricName FROM Config.Metric WHERE DataSetId = @DataSetID FOR XML PATH ('')), 1,1,'')

Which is throwing error while i am providing Column alias using AS in the query.

Could you please help me on this?

July 10, 2014 11:53 PM

Rob Farley said:

I think you've got misplaced parentheses - I see three after the @metric_name variable.

July 11, 2014 12:39 AM

Rob Farley said:

Ali - sorry for not responding sooner. Try using a binary collation...

July 11, 2014 12:42 AM

Richard Lee said:

Fantastic, saved me a lot of time and trouble!

July 18, 2014 7:06 AM

Arne Drews said:


Is there an solution to put the result in elements when using PATH(''), TYPE?

I think about an combination of TYPE and ELEMENTS.

This way I have to build the Tags myself.

This hack/trick is pretty nice, realy.

But I search for an solution to use <![CDATA[ in my results without using EXPLICIT.

The reason is that my query has many datafields and joins so the EXPLICIT-method semms very confused and error-prone.

Can I hope of an better way instead of building the field-tags myself?

Thanks a lot

July 23, 2014 3:45 AM

yves said:

Thanks a lot, nice tip!!!

August 14, 2014 8:44 AM

Muhammad Zubaid said:

Thanks and this article is so helpful to me.

September 3, 2014 10:16 AM

DevD said:

Thanks for the post. This is most useful.

November 12, 2014 7:29 AM

Prat said:


Thank you for the sulotion. It works for me. it resolved part of the prob.

The query i have has 2 xml parts. The inner query i have resolved your solution which has Stuff, but the outer query reads then inner query results and returns &amp in the main query since one of the values is &.

Previously it was returning &amp;amp; the output is &amp; which should be only &


November 24, 2014 4:31 PM

Rob Farley said:

Prat - hopefully you're on the right track. It sounds like you must be using two FOR

XMLs, and will need to use another .value as well?

November 24, 2014 5:03 PM

Prat said:

Yes. I am using 2 For and i am trying to use .value but it is giving me an error Incorrect syntax near '.'.

My query is below. As you can see i have resolved the inner part but when i execute the whole query i get &amp













WHERE LDLOAD = 7951033




), 1, 1, '')







FOR XML Auto, Elements

November 24, 2014 5:27 PM

Rob Farley said:

You're doing "XML Auto, Elements" at the end, so that will make your contents be &amp; rather than &. You're actually wanting an XML form, right? So it's valid to have &amp; - and you've solved the fact that it's not &amp;amp;

November 24, 2014 9:05 PM

Prat said:

Thank you. Appreciate your comments and your help.  

November 25, 2014 10:37 AM

Zeeshan said:

Thanks for article. A very nce article for XML Datatype.

December 10, 2014 6:38 AM

Dean said:

Thanks so much for this little bit of explanation along with the progressive examples:

"To be valid XML, it needs a ROOT element. I also need to tell it to be XML, using the TYPE directive.

But this lets me hook into the value of /MyString[1], and return that as varchar(max)."

That was the little bit of knowledge I was missing to understand other 'for xml path' examples I have found.

April 9, 2015 11:29 AM

Mark said:

Thanks for this, I hit this issue and you have helped me solve it in no time

April 10, 2015 6:13 AM

Warren said:

Somehow a stored procedure that was using this method got compiled with "SET QUOTED_IDENTIFIER OFF" on one particular server, and the stored procedure stopped working (giving a "SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'" error.

I recompiled the stored procedure with the correct setting and got it working again, but is there a way to make this work with QUOTED_IDENTIFIER set to OFF?

April 10, 2015 8:00 PM

Rob Farley said:

Sorry Warren - "SET QUOTED_IDENTIFIER must be ON when you invoke XML data type methods."

This is from the official documentation, at

April 10, 2015 8:09 PM

Gert said:

Thanks Rob (+ Brad and Alejandro), a really useful article and a very handy little line of code.

May 11, 2015 6:43 AM

Ram said:

Hi Rob,


UPDATE dbo.Note

  SET Note = ltrim(rtrim(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(Note ,'&#x20;',char(20))      






,'&#x0B;',' ')    




,'&#x0C;',' ')    



In the above update I'm replacing the unwanted characters I'm getting after using the  FOR XML PATH('') .

Is there any way I can Eliminate them during the Use of  FOR XML PATH('') .

Please let me know as I stuck up

May 19, 2015 7:22 AM

Rob Farley said:

Hi Ram,

Try putting it inside something which you can cast to xml, and then extracting the contents from it.

select cast('<t>'+Note+'</t>' as xml).value('/t[1]','varchar(max)')

from dbo.Note;

May 19, 2015 8:40 AM

Ram said:

UPDATE V1          

SET Note=isnull(V1.Note,'') +isnull(d.Note,'')        

FROM  Note v1 with(nolock)        

CROSS APPLY ( SELECT  ' ' +LTRIM(RTRIM(Claim_Note))            

             FROM Note_Detail v2 with(nolock)        

             WHERE v2.num = v1.num and Seq_Num <> 1      

             ORDER BY Seq_Num        

             FOR XML PATH('') )  D ( Note)  

Here I'm getting the unwanted caharacters.

Can you please let me know how to eliminate in this step

May 20, 2015 6:40 AM

Rob Farley said:


CROSS APPLY (SELECT ( SELECT  ' ' +LTRIM(RTRIM(Claim_Note))            

            FROM Note_Detail v2 with(nolock)        

            WHERE v2.num = v1.num and Seq_Num <> 1      

            ORDER BY Seq_Num        

            FOR XML PATH(''), TYPE ).value('.','varchar(max)') as Note )  D ( Note)  

May 20, 2015 8:13 AM

Ram said:

Getting below error.


Msg 6841, Level 16, State 1, Line 1

FOR XML could not serialize the data for node 'Note' because it contains a character (0x0001) which is not allowed in XML.

To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

May 20, 2015 8:27 AM

Rob Farley said:

Rip out that character first, as it's not allowed in XML. Then see how you go.

May 20, 2015 9:13 AM

Yogesh said:

What is the syntax for ).value('.', 'NVARCHAR(MAX)')  and what does it mean ?

June 6, 2015 11:48 AM

Rob Farley said:

Hi Yogesh,

There are quite a few examples above for the syntax. Essentially, it's just @TheXML.value('.','nvarchar(max)')

So having done:


you have a string which looks like an ordinary concatenated string, but is actually XML, with some characters replaced by encoded equivalents. Using the .value() method can take that and turn it into a something which is a different type. You're wanting the whole lot, so use '.', and you're wanting nvarchar(max), so that makes your second parameter.

June 6, 2015 7:15 PM

Nitin said:

I'm trying to replicate one of solutions provided above for .Value wrapping all the values in one xml element whereas I want thme to be sepeate.



(RTRIM(CONVERT(varchar(10),customer.uvw_Telephone_Number_bomi4.phone_country_code)) + ' ' +

RTRIM(CONVERT(varchar(10),customer.uvw_Telephone_Number_bomi4.phone_area_code)) + ' ' +


from customer.uvw_Telephone_Number_bomi4

where Customer.customer_id = [customer].uvw_Telephone_Number_bomi4.customer_id

--and[customer].uvw_Telephone_Number_bomi4.preferred_ind = 1

and [customer].uvw_Telephone_Number_bomi4.customer_Id = @customerID

--FOR XML PATH(''))),

FOR XML PATH(''),type).value('(./text())[1]','nvarchar(max)'))) as 'Telephone_Number',

July 8, 2015 6:35 AM

Rob Farley said:

Hi Nitin,

What results are you after?

FOR XML combines multiple rows into a single one. If you just want the columns combined within each row, then just use + and don't do the FOR XML bit at all.

But maybe I'm not understanding what you're after.


July 8, 2015 6:46 AM

Nitin said:

Hey Rob, I sorted it out. All I needed was to use Type and ignore .value code.

Great article here anyway. Cheers

July 8, 2015 9:04 AM

G said:

Hey Rob,

Is it possible to use this and have a path value set? Whenever I use this the PATH value gets removed from the output.

Something like...




FROM Table

FOR XML PATH('tr'),TYPE).value('.','NVARCHAR(MAX)')


July 24, 2015 8:07 AM

Rob Farley said:

G - what output are you trying to achieve?

July 24, 2015 8:10 AM

john said:

hi could you please explain the XQuery you use? Thank you.

August 12, 2015 9:02 AM

Rob Farley said:

Hi John - which bit?

August 12, 2015 9:09 AM

john said:

hi rob, this part: '(./text())[1]' Thank you.

August 13, 2015 5:53 AM

Rob Farley said:

That bit means to get the text at the root node, as as it returns a collection of pieces of text, get the first one (which is the [1] part).

August 13, 2015 6:28 AM

Suvasish said:

Great Code.. Thank you Rob :)

August 26, 2015 3:54 AM

Joy said:

Hye Rob,

Can i use it with select from openrowset file? The code works until the type.but return null after I use .value('(./text())[1]','varchar(max)'


                     (BULK 'Z:\CTADMCL0000120151003.xml',

                      SINGLE_CLOB) AS XMLDATA FOR XML PATH(''), root('MyString'), TYPE).value('(./text())[1]','varchar(max)'), 1, 2, '')

October 3, 2015 9:40 PM

Rob Farley said:

Hi Joy,

There's something you're not quite getting...

The STUFF function is there to remove the leading comma.

Start by just doing the SELECT * FROM ... AS XMLDATA part.

Then add the "FOR XML PATH(''), TYPE" bit in there.

You'll notice that you have tags for your column names, so change your "*" to ', ' + someColumnName.

Then use .value, and use STUFF, and see how you go.


October 5, 2015 2:48 AM


Hi Rob,

I tried STUFF below queries

Query 1: select stuff((select ', ' + E.description FROM mm_item_master E FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')alldesc;

I got xml syntax error.. then I saw the posted comments "environment not set" you mentioned above and the below query has executed

Query 2: select boden_verd from boden_verd for xml path('');

still I got same xml syntax error.  How do I solve my problem?

October 12, 2015 5:53 AM

Rob Farley said:

Hi Zaheer,

What's the error you're getting?


October 12, 2015 6:00 AM

Neha said:

Hi Rob,

The solution provided on 3rd June 2012 worked for me.. Thank You.

February 10, 2016 3:59 AM

Rob Farley said:

Great. I'm not sure which one you mean, but great!

February 10, 2016 4:17 AM

Riza said:

Hi Rob, I have an xml column type that has the &lt; and &gt; in place of < and > and when I try applying your query, it gives me this error: "The data types varchar and xml are incompatible in the add operator.".

I I guess this part of the query: select  ', <' + name + '>' isn't able to be used with the xml column type. Any suggestions? Thanks!

February 10, 2016 2:53 PM

Rob Farley said:

Maybe try:

select  ', <' + cast(name as varchar(max)) + '>'

February 10, 2016 3:02 PM

JRStern said:

Hey Rob and all, thanks for this post, it helped me today!

I haven't the foggiest idea *why* it works, but I'm often that way for XML syntax.

March 2, 2016 6:26 PM

Ken said:

Thanks Rob - Great post - I've been looking for a solution to this for awhile. Your time is very much appreciated.

March 3, 2016 4:22 PM

Ankur said:

Hi Rob,

I am using this query:

select Body


where Category = 'Background Optimization'


It combines all the bodies and gives one result, but has &lt; and &gt;. How do i replaced them by < and >.

March 11, 2016 5:17 AM

Rob Farley said:



select Body


where Category = 'Background Optimization'



...or nvarchar, of course.

March 11, 2016 5:32 AM

Ankur said:

Thanks Rob. That worked like a charm.

March 14, 2016 1:47 AM

Ankur said:

Rob, i need to extend this query now.

Now every BODY has a name. So when i give this:


select Name, Body


where Category = 'Background Optimization'

FOR XML PATH(''),TYPE).value('.','varchar(max)')

i get something like this:


How can i get:


where Alex is the name of the first body.

March 14, 2016 2:16 AM

Rob Farley said:

I'm not completely sure what you mean, but maybe something like:


(select top (1) Name from W6SETTINGS

where Category = 'Background Optimization')


(select Body


where Category = 'Background Optimization'

FOR XML PATH(''),TYPE).value('.','varchar(max)')

March 14, 2016 2:30 AM

Ankur said:

Sorry, will make it more clear.

This is how i have data in w6settings:

Name               Body


Alex               <Body><BackgroundOptim...

John               <Body><BackgroundOptim...

I want to get one single body for this like:


March 14, 2016 2:52 AM

Rob Farley said:

Then you need a correlated sub-query. This is getting messy, but it might be easiest for you. I'm grouping by the name in the outer query so that you get one row per name.

select (

select  '<Name>' + n.Name + '</Name>' +

(select Body


where b.Category = 'Background Optimization'

and b.Name = n.Name

FOR XML PATH(''),TYPE).value('.','varchar(max)')


where n.Category = 'Background Optimization'


FOR XML PATH(''),TYPE).value('.','varchar(max)')

March 14, 2016 3:10 AM

Ankur said:

Perfect :)

Just one correction in above.. Row 4 - After w6settings, 'b' is missing.

Thanks a lot Rob.

March 14, 2016 3:52 AM

Rob Farley said:

Fixed that now. I wrote it on my phone...

March 14, 2016 3:58 AM

Sita said:

I need to export data from SQL table to XML.One of the column in table has special characters.I need all those to be transferred without any error.When I use the below code its throwing an error 'illegal XML character'. Can you help me out with this.

code using

DECLARE @XmlOutput xml

SET @XmlOutput =(


from tbalename


SELECT @XmlOutput

March 15, 2016 11:36 AM

Madhuri said:

Really, it's cool trick. it helped me a lot.

April 12, 2016 6:21 AM

Rose said:

Thanks Rob - Great post - I've been looking for a solution to this for awhile.i really appreciate ,i just wanted to tell you you use cool trick and That's the best CSV that I've seen.. Still

July 15, 2016 2:29 AM

Petko Dinev said:

Thank you for sharing Rob.

All the best!

July 28, 2016 9:07 AM

mohana said:


FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1, 1, '') as Director_Name

i am getting

Msg 6841, Level 16, State 1, Line 1

FOR XML could not serialize the data for node 'NoName' because it contains a character (0x001A) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

please help me.

August 3, 2016 8:17 AM

sanket said:

Thanks Brad. it helped me lot.

August 10, 2016 4:42 AM

Leave a Comment


This Blog



News? Haven't you read my blog?

My Company

Can't find something?

Contact Me

Twitter: @rob_farley
Skype: rob_farley

MVP (SQL Server)


Adelaide SQL UG

Privacy Statement