THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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 runs 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
  stuff(
     (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
  stuff(
     (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
  stuff(
     (select ', <' + name + '>'
     from sys.databases
     where database_id > 4
     order by name
     for xml path(''), root('MyString'), type
     ).value('/MyString[1]','varchar(max)')
   , 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

Comments

 

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:

Rob,

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

select

 stuff(

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

    from sys.databases

    where database_id > 4

    order by name

    for xml path(''), type

    ).value('(./text())[1]','varchar(max)')

  , 1, 2, '') as namelist;

Cheers,

AMB

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,

Derek

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,

Derek.

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:

Esteban,

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)

Rob

January 21, 2011 6:52 PM
 

Rob Farley said:

Derek,

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?

Rob

January 21, 2011 6:56 PM
 

Ranu Mandan said:

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

ranu.mandan@gmail.com

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,

Try:

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

(

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);

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?

Thanks,

Sarah

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.

Rob

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:

ALTER PROCEDURE [dbo].[BBO1]

-- Add the parameters for the stored procedure here

AS

BEGIN

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

-- interfering with SELECT statements.

SET NOCOUNT ON;

   -- 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",

'dreambook@PChomes.com' as "DefaultLeadsEmail",

--Start Subdivision

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

--start Sales Office

(Select

--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

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

--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

    ).value('/MyString[1]','varchar(max)')

  , '&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",

'http://www.PChomes.com/images/NHDImages/">http://www.PChomes.com/images/NHDImages/' + 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",

'http://www.PChomes.com/images/NHDImages/">http://www.PChomes.com/images/NHDImages/' + 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

'http://www.PChomes.com/'+URLShortName as "SubWebsite",

--EndSubImage

--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 1

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

'http://www.PChomes.com/images/Elevation_Images/' + 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",

'http://www.PChomes.com/images/FPImages/' + 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",

'http://www.PChomes.com/images/HomeImages/' + 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",

'http://www.PChomes.com/images/FPImages/' + 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)

FOR XML PATH('Spec'), TYPE)

--End Spec

FROM PC_Floorplans "Plan"

left Join PC_AvailableHome "PlanHomes" ON

"PlanHomes".FloorplanID = "Plan".FloorplanID

inner join PC_MarketingStyle "MHS" On

"MHS".MHSID = "Plan".MHSID

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)

FOR XML PATH('Plan'), TYPE)

--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

FOR XML PATH('Builders'), ELEMENTS XSINIL

--End Builders

END

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

     <SubDecription>&lt;p&gt;

&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;

&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;

&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;

</SubDecription>

June 8, 2012 8:27 AM
 

Rob Farley said:

Hakim,

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.

Rob

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:

Hi

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

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

Regards

Klaus

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

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

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!)

Kon.

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

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

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,

Rob

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,

 STUFF((

   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,

Try:

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,

Rob

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:

boden_verd:  

boden_verd_ID,boden_verd  

1,value1  

2,value2  

3,value3  

baumkataster:  

baum_ID, boden_verd      

1,{2}  

2,{1,3}  

3,{1,2,3}  

What I need ist the following:

baum_ID,boden_verd  

1,{value2}  

2,{value3,value3}  

3,{value1,value2,value3}  

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!!

Patrick

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.

Rob

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  

*,

(

select   *

from tabular_coordinate where tabular_coordinate.form_id = M.form_id

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

(

   SELECT *

   FROM visit

where visit.id = M.id

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

  As drecontent

FROM visit AS M

FOR XML PATH('visit'), ROOT('PT'), TYPE

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,

Rob

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?

Thanks

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,

Try:

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.

Rob

March 6, 2014 3:09 PM
 

SandoLuke said:

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

select

 stuff(

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

    from sys.databases

    where database_id > 4

    order by name

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

    ).value('/MyString[1]','varchar(max)')

  , 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

replace(stuff(

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

   from sys.databases

   where database_id > 4

   order by name

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

   ).value('/MyString[1]','varchar(max)')

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

Or

select

replace(stuff(

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

   from sys.databases

   where database_id > 4

   order by name

   for xml path(''), type

   ).value('.','varchar(max)')

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

...if you prefer to not use ROOT.

Hope this helps

Rob

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
 

Using FOR XML to create arrays, JSON and JavaScript | SQL from the Trenches said:

June 26, 2014 2:18 AM
 

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:

Hi,

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

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement