Handling special characters with FOR XML PATH(”)

April 15, 2010

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

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.

It still runs, but I my results don’t show the triangular brackets, it shows <databasename>, <databasename2>. 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.

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.

This Post Has 141 Comments

  1. Ben Laan

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

  2. Brad Schulz

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

  3. Alejandro Mesa

    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

  4. Rob Farley

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

  5. erin

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

  6. Derek

    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

  7. Derek

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

  8. Vedika

    Cool trick…

  9. Esteban

    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?

  10. Rob Farley

    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

  11. Rob Farley

    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

  12. sai

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

  13. Curiosity

    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;

  14. Rob Farley

    To the XML characters?
    Something like this should do it for you:
    select ‘&’ for xml path(”)
    Hope this helps… 🙂

  15. agam

    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)

  16. Rob Farley

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

  17. agam

    thanks for you reply but no luck.
    Fails with the same error. "Incorrect syntax near ‘.’.

  18. Rob Farley

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

  19. agam

    thanks Rob, that worked like a dream.

  20. Sarah M.

    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

  21. swapna

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

  22. Rob Farley

    Hi Sarah,
    It should be fine.
    Rob

  23. Hakim Hunter

    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;’,’'’)) 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;’,’'’)) 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>

  24. Rob Farley

    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

  25. john

    Thanks agam!!

  26. Vikas

    Thanks, my problem solved.

  27. Klaus

    Hi
    I have german umlaute (?, ä, ..) in my sql table
    Is there a way to xml-lize them during SELECT … FOR XML PATH?
    Regards
    Klaus

  28. Rob Farley

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

  29. Dan

    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!

  30. Nebojsa

    Great post!
    It’s very helpful.

  31. Nitika

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

  32. Ben

    Brilliant. Cheers.

  33. Erik

    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.

  34. SH

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

  35. Kon

    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.

  36. Rob Farley

    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.

  37. Jun Reyes

    Thumbs up!

  38. Tusky

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

  39. Rob Farley

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

  40. Tusky

    Thanks Rob

  41. ARiest

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

  42. Jay

    Thanks Rob. Would you please explain the use of below code
    ,type).value(‘(./text())[1]’,’varchar(max)’)

  43. Rob Farley

    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

  44. Luca

    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

  45. Siva Sankar Gorantla

    This is the best solution forever.

  46. Siva Sankar Gorantla

    Thank you very much Rob.

  47. Sai

    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

  48. Milacay

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

  49. V286

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

  50. Siva Sankar Gorantla

    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

  51. Liz

    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 ‘… & ….’

  52. Rob Farley

    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

  53. Rob Farley

    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?

  54. Liz

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

  55. Liz

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

  56. tareq

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

  57. patrick

    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

  58. Rob Farley

    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

  59. patrick

    Hi Rob, thanks for answering!
    that was the proplem. Meanwhile I got the same answers at other forums…
    Thanks anyway!

  60. William

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

  61. Sherwin

    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

  62. Sherwin

    Sorry just found a problem on the previous post:
    Heres what I really have.
     FOR XML PATH(”), TYPE ).value(‘.’,’VARCHAR(MAX)’)

  63. Rob Farley

    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

  64. Sherwin

    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

  65. Rob Farley

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

  66. Sandhya

    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.

  67. Rob Farley

    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

  68. SandoLuke

    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?

  69. Rob Farley

    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

  70. SandoLuke

    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…

  71. Rob Farley

    Yeah, sounds like it.

  72. Ali

    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,”))

  73. John McC

    Thanks.  Great soulution for an annoying "problem"

  74. Vijay Sharma

    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?

  75. Rob Farley

    I think you’ve got misplaced parentheses – I see three after the @metric_name variable.

  76. Rob Farley

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

  77. Richard Lee

    Fantastic, saved me a lot of time and trouble!

  78. Arne Drews

    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

  79. yves

    Thanks a lot, nice tip!!!

  80. Muhammad Zubaid

    Thanks and this article is so helpful to me.

  81. DevD

    Thanks for the post. This is most useful.

  82. Prat

    Rob,
    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;…now the output is &amp; which should be only &
    Thanks

  83. Rob Farley

    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?

  84. Prat

    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
    SELECT  DISTINCT
    TBLLOADS.LDLOAD AS [TBLLOADS_LDLOAD],
    TBLLOADS.LDNAME AS [TBLLOADS_LDDISP],
    FROM DBO.TBLLOADS  (NOLOCK)
    LEFT OUTER JOIN
    (
    SELECT PZZIP,
    LEFT((SELECT STUFF ((
    SELECT ‘,’ + PIDESC
    FROM TBLLOADS
    LEFT OUTER JOIN DBO.TBLPERMITSZIPPRICINGMASTER (NOLOCK) ON TBLPERMITSZIPPRICINGMASTER.PZZIP = TBLLOADS.LDZIP
    LEFT OUTER JOIN DBO.TBLPRICING  (NOLOCK) ON TBLPERMITSZIPPRICINGMASTER.pzcode = TBLPRICING.picode
    WHERE LDLOAD = 7951033
    FOR XML PATH (”)
    ,ROOT(‘MyString’)
    ,TYPE).value(‘/MyString[1]’,’Varchar(max)’
    ), 1, 1, ”)
    ),55) AS PIDESC
    FROM dbo.TBLPERMITSZIPPRICINGMASTER (NOLOCK)
    )
    AS [DBO.TBLPRICING] ON [DBO.TBLPRICING].PZZIP = TBLLOADS.LDZIP
    LEFT OUTER JOIN DBO.TBLZIP (NOLOCK) ON TBLZIP.ZPZIPCODE = TBLLOADS.LDZIP
    WHERE TBLLOADS.LDLOAD = 7951033
    FOR XML Auto, Elements

  85. Rob Farley

    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;

  86. Prat

    Thank you. Appreciate your comments and your help.  

  87. Zeeshan

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

  88. Dean

    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.

  89. Mark

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

  90. Warren

    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?

  91. Gert

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

  92. Ram

    Hi Rob,

    –Cleanup        

    UPDATE dbo.Note

      SET Note = ltrim(rtrim(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(Note ,’ ’,char(20))      

    ,’&lt;’,'<‘)      

    ,’&gt;’,’>’)      

    ,’ ’,char(10))      

    ,’ ’,char(9))    

    ,’&#x07;’,’char(7)’)    

    ,’&#x0B;’,’char(11)’)    

    ,’&#x01;’,’char(1)’)    

    ,’&#x15;’,’char(21)’)    

    ,’&#x13;’,’char(19)’)    

    ,’&#x0C;’,’char(12)’)    

    –,’&AMP;’,’&’    

    ))

    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

  93. Rob Farley

    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;

  94. Ram

    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

  95. Rob Farley

    Use:
    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)  

  96. Ram

    Getting below error.
    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.

  97. Rob Farley

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

  98. Yogesh

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

  99. Rob Farley

    Hi Yogesh,
    There are quite a few examples above for the syntax. Essentially, it’s just @TheXML.value(‘.’,’nvarchar(max)’)
    So having done:
    … FOR XML PATH(”), TYPE
    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.

  100. Nitin

    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.
    SELECT(
    (SELECT
    (RTRIM(CONVERT(varchar(10),customer.uvw_Telephone_Number_bomi4.phone_country_code)) + ‘ ‘ +
    RTRIM(CONVERT(varchar(10),customer.uvw_Telephone_Number_bomi4.phone_area_code)) + ‘ ‘ +
    RTRIM(CONVERT(varchar(10),customer.uvw_Telephone_Number_bomi4.phone_number)))
    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’,

  101. Rob Farley

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

  102. Nitin

    Hey Rob, I sorted it out. All I needed was to use Type and ignore .value code.
    Great article here anyway. Cheers

  103. G

    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…
    SELECT
    CAST((
    SELECT *
    FROM Table
    FOR XML PATH(‘tr’),TYPE).value(‘.’,’NVARCHAR(MAX)’)
    AS NVARCHAR(MAX)

  104. Rob Farley

    G – what output are you trying to achieve?

  105. john

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

  106. Rob Farley

    Hi John – which bit?

  107. john

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

  108. Rob Farley

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

  109. Suvasish

    Great Code.. Thank you Rob 🙂

  110. Joy

    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)’
    select stuff((SELECT * FROM OPENROWSET
                         (BULK ‘Z:\CTADMCL0000120151003.xml’,
                          SINGLE_CLOB) AS XMLDATA FOR XML PATH(”), root(‘MyString’), TYPE).value(‘(./text())[1]’,’varchar(max)’), 1, 2, ”)

  111. Rob Farley

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

  112. ZAHEERHUSSAIN

    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?

  113. Rob Farley

    Hi Zaheer,
    What’s the error you’re getting?
    Rob

  114. Neha

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

  115. Rob Farley

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

  116. Riza

    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!

  117. Rob Farley

    Maybe try:
    select  ‘, <‘ + cast(name as varchar(max)) + ‘>’

  118. JRStern

    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.

  119. Ken

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

  120. Ankur

    Hi Rob,
    I am using this query:
    select Body
    from W6SETTINGS
    where Category = ‘Background Optimization’
    FOR XML PATH(”)
    It combines all the bodies and gives one result, but has &lt; and &gt;. How do i replaced them by < and >.

  121. Rob Farley

    Try:
    SELECT (
    select Body
    from W6SETTINGS
    where Category = ‘Background Optimization’
    FOR XML PATH(”)
    ,TYPE).value(‘.’,’varchar(max)’)
    …or nvarchar, of course.

  122. Ankur

    Thanks Rob. That worked like a charm.

  123. Ankur

    Rob, i need to extend this query now.
    Now every BODY has a name. So when i give this:
    SELECT (
    select Name, Body
    from W6SETTINGS
    where Category = ‘Background Optimization’
    FOR XML PATH(”),TYPE).value(‘.’,’varchar(max)’)
    i get something like this:
    Alex<Body><BackgroundOptimizat….
    How can i get:
    <Name>Alex</Name><Body><BackgroundOptimizat….
    where Alex is the name of the first body.

  124. Rob Farley

    I’m not completely sure what you mean, but maybe something like:
    SELECT
    (select top (1) Name from W6SETTINGS
    where Category = ‘Background Optimization’)
    FOR XML PATH(”)) +
    (select Body
    from W6SETTINGS
    where Category = ‘Background Optimization’
    FOR XML PATH(”),TYPE).value(‘.’,’varchar(max)’)

  125. Ankur

    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:
    <Name>Alex</Name><Body><BackgroundOptim….</Body><Name>John</Name><Body><BackgroundOptim….</Body>..

  126. Rob Farley

    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
    from W6SETTINGS AS b
    where b.Category = ‘Background Optimization’
    and b.Name = n.Name
    FOR XML PATH(”),TYPE).value(‘.’,’varchar(max)’)
    FROM W6SETTINGS AS n
    where n.Category = ‘Background Optimization’
    GROUP BY n.Name
    FOR XML PATH(”),TYPE).value(‘.’,’varchar(max)’)

  127. Ankur

    Perfect 🙂
    Just one correction in above.. Row 4 – After w6settings, ‘b’ is missing.
    Thanks a lot Rob.

  128. Rob Farley

    Fixed that now. I wrote it on my phone…

  129. Sita

    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 =(
    SELECT  *
    from tbalename
    FOR XML AUTO,ROOT(‘Table’),ELEMENTS)
    SELECT @XmlOutput

  130. Madhuri

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

  131. Rose

    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

  132. Petko Dinev

    Thank you for sharing Rob.
    All the best!

  133. mohana

    hi,
    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.

  134. sanket

    Thanks Brad. it helped me lot.

  135. percussionies

    select a.list from (
    select(
    SELECT
    PROPERTY_ID
    AUCTION_ID,
    CASE_NO AS CaseNumber,
    PROCEEDING_ID ,
    FC_STATE_CD,  
    LAND_TITLE,
    RESTRICTION_IN_INTEREST,
    RESERVED_PRICE, 1 as PropertyInAuction, COURT_LOCATION_NAME, PROCEEDING_DATE AS AuctionDate, 1 AS NumberOfAuction, MINIMUM_BID AS MinimumBid, DEPOSIT_PERCENTAGE AS DepositPercentage,
    PANEL_BANK_PROCESSING_FEE AS PanelBankProcessingFee, STAMP_DUTY AS StampDuty, PAYMENT_TERMS AS PaymentTerm, COUNSEL_ID AS CounsellorId, ATTACHMENT_PATH AS DocumentId, 0 AS STATUS, GETDATE() AS CreatedDate,
    1 AS CreatedBy, GETDATE() AS ModifiedDate, 1 AS ModifiedBy,
    (
    SELECT DISTINCT
    PR.PROPERTY_ID,
    AD.AUCTION_ID,
    CD.CASE_NO AS [Property/CaseNumber],
    CE.PROCEEDING_ID AS [Property/ProceedingId],
    PR.PROPERTY_ID AS [Property/PropertyId],
    PR.TITLE_NUMBER AS [Property/TitleNumber],
    PR.BANK_NAME AS [Property/BankName],
    PR.MUKIM AS [Property/Mukim],
    PR.DISTRICT AS [Property/District],
    PR.LOT_NUMBER AS [Property/LotNumber],
    PR.PARCEL_NUMBER AS [Property/ParcelNumber],
    PR.STORY_NUMBER AS [Property/StoreyNumber],
    PR.BUILDING_NUMBER AS [Property/BuildingNumber],
    PR.EXPRESS_CONDITION AS [Property/ExpressCondition],
    PR.TENURE AS [Property/Tenure],
    PR.BUILT_UP AS [Property/BuiltUp],
    PR.LAND_AREA AS [Property/LandArea],
    PR.REGISTER_OWNER AS [Property/RegisterOwner],
    PR.FACILITIES AS [Property/Facilities],
    PR.EMCUMBRANCES AS [Property/Emcumbrances],
    PR.PROPERTY_TYPE AS [Property/PropertyType],
    PR.UNIT_NUMBER AS [Property/UnitNumber],
    PR.FLOOR AS [Property/Floor],
    PR.BUILDING_NAME AS [Property/BuildingName],
    PR.STREET AS [Property/Street],
    PR.SECTION AS [Property/Section],
    PR.POST_CD AS [Property/Postcode],
    PR.CITY AS [Property/City],
    PR.POSTAL_STATE_CD AS [Property/PostalStatecode],
    GETDATE() AS [Property/CreatedDate],
    1 AS [Property/CreatedBy],
    GETDATE() AS [Property/ModifiedDate],
    1 AS [Property/ModifiedBy]
    from TBL_CASE_DETAIL cd WITH(NOLOCK)
    inner join TBL_AUCTION_DETAIL AD on AD.CASE_DETAIL_ID = cd.CASE_DETAIL_ID
    LEFT JOIN TBL_AUCTION_PROPERTY_MAPPING TAPM WITH(NOLOCK) ON TAPM.AUCTION_ID = AD.AUCTION_ID
    LEFT JOIN TBL_PROPERTY PR WITH(NOLOCK) ON PR.PROPERTY_ID = TAPM.PROPERTY_ID
    LEFT JOIN TBL_AUCTION_PROCEEDING_MAPPING APM WITH(NOLOCK) ON APM.AUCTION_ID = AD.AUCTION_ID
    LEFT JOIN TBL_PROCEEDING CE WITH(NOLOCK)  ON CE.PROCEEDING_ID = APM.PROCEEDING_ID
    LEFT JOIN TBL_PROPERTY_PARTY_MAPPING PPM WITH(NOLOCK) ON PPM.PROPERTY_ID = PR.PROPERTY_ID
    LEFT JOIN TBL_PARTY PA WITH(NOLOCK) ON PA.PARTY_ID = PPM.PARTY_ID
    LEFT JOIN TBL_CASE_DETAIL_PARTY_MAPPING CDPM WITH(NOLOCK) ON CDPM.CASE_DETAIL_ID = CD.CASE_DETAIL_ID
    LEFT JOIN TBL_CASE_DETAIL_PARTY_COUNSEL_REPRESENT CDPCR WITH(NOLOCK) ON CDPCR.CASE_DETAIL_PARTY_DETAIL_ID = CDPM.CASE_DETAIL_ID
    LEFT JOIN COMMON.dbo.TBL_COURT  C WITH(NOLOCK) ON C.COURT_ID = CD.COURT_ID
    LEFT JOIN COMMON.dbo.VIEW_SELT_COURT_LOCATION vSCL ON vSCL.COURT_LOCATION_ID = C.COURT_LOCATION_ID
    WHERE  tPR.PROPERTY_ID = PR.PROPERTY_ID AND CULTURE_CODE = ‘ms-MY’
    FOR XML PATH(”), TYPE
    )
    from
    (SELECT DISTINCT tPR.PROPERTY_ID, TAD.AUCTION_ID, aude.CASE_NO, CE.PROCEEDING_ID ,tPR.FC_STATE_CD,  tPR.LAND_TITLE, tPR.RESTRICTION_IN_INTEREST,
    TAD.RESERVED_PRICE, 1 as PropertyInAuction,vSCL.COURT_LOCATION_NAME, CE.PROCEEDING_DATE , 1 AS NumberOfAuction, TAD.MINIMUM_BID, TAD.DEPOSIT_PERCENTAGE,
    PANEL_BANK_PROCESSING_FEE, STAMP_DUTY, PAYMENT_TERMS, COUNSEL_ID, ATTACHMENT_PATH, 0 AS STATUS, GETDATE() AS CreatedDate,
    1 AS CreatedBy, GETDATE() AS ModifiedDate, 1 AS ModifiedBy  from TBL_CASE_DETAIL aude
    inner join TBL_AUCTION_DETAIL TAD on TAD.CASE_DETAIL_ID = aude.CASE_DETAIL_ID
    LEFT JOIN TBL_AUCTION_PROPERTY_MAPPING TAPM WITH(NOLOCK) ON TAPM.AUCTION_ID = TAD.AUCTION_ID
    LEFT JOIN TBL_PROPERTY tPR WITH(NOLOCK) ON tPR.PROPERTY_ID = TAPM.PROPERTY_ID
    LEFT JOIN TBL_AUCTION_PROCEEDING_MAPPING APM WITH(NOLOCK) ON APM.AUCTION_ID = TAD.AUCTION_ID
    LEFT JOIN TBL_PROCEEDING CE WITH(NOLOCK)  ON CE.PROCEEDING_ID = APM.PROCEEDING_ID
    LEFT JOIN TBL_PROPERTY_PARTY_MAPPING PPM WITH(NOLOCK) ON PPM.PROPERTY_ID = tPR.PROPERTY_ID
    LEFT JOIN TBL_PARTY PA WITH(NOLOCK) ON PA.PARTY_ID = PPM.PARTY_ID
    LEFT JOIN TBL_CASE_DETAIL_PARTY_MAPPING CDPM WITH(NOLOCK) ON CDPM.CASE_DETAIL_ID = aude.CASE_DETAIL_ID
    LEFT JOIN TBL_CASE_DETAIL_PARTY_COUNSEL_REPRESENT CDPCR WITH(NOLOCK) ON CDPCR.CASE_DETAIL_PARTY_DETAIL_ID = CDPM.CASE_DETAIL_ID
    LEFT JOIN COMMON.dbo.TBL_COURT  C WITH(NOLOCK) ON C.COURT_ID = aude.COURT_ID
    LEFT JOIN COMMON.dbo.VIEW_SELT_COURT_LOCATION vSCL ON vSCL.COURT_LOCATION_ID = C.COURT_LOCATION_ID
    WHERE  CULTURE_CODE = ‘ms-MY’
    ) tPR
    FOR XML PATH(‘Foreclosure’),type).value(‘.’,’varchar(max)’)) AS a(list);
    I can get the result like one row like string but I need to display like xml file but need one row.. how I can get it??

  136. Stephen

    Lol, people straight up giving you their entire issues to solve… contracting isn’t free people!
    Great post, worked out nicely.

  137. Matt

    This is still great. 7 years on people are still finding this immensely helpful and informative!

  138. Rodf1021

    This is GREAT!! It got me really close to what I need. Wondering if you can advise on why it correctly returns the <ItemMaster> node with all elements surrounded by ‘<‘ and ‘>’, but leaves out the root node and its attributes.
    Return should be Filename, XML where XML is a string literal with a valid XML. The SQL below returns the correct XML but has the ‘&gt;’ and ‘&lt;’.
    Value of XML column
    <?xml version="1.0" encoding="utf-8"?><Root TransactionType="ItemMasterDownload" TransactionDateTime="2017-06-16T15:45:35-07:00" SystemID="DraperMES" Environment="JDE_CRP" MessageID="FCCBC642-4EC9-4478-B176-47857760E7CB"><ItemMaster><Plant>&lt;![CDATA[UT02]]&gt;</Plant><ItemNumber>&lt;![CDATA[ROD_FG]]&gt;</ItemNumber><ItemRev>&lt;![CDATA[N/A]]&gt;</ItemRev><ItemDescription>&lt;![CDATA[RodF FG test item]]&gt;</ItemDescription><UOM>&lt;![CDATA[EA]]&gt;</UOM><Family>&lt;![CDATA[TBV]]&gt;</Family><Type>&lt;![CDATA[S]]&gt;</Type><IBUserReserveRef>&lt;![CDATA[]]&gt;</IBUserReserveRef></ItemMaster></Root>
    SQL statement excluding the create of CTE x
    SELECT
    Convert(varchar(10),GetDate(),120) + ‘_ManualItemSubscription_’ + x.ItemNumber +’.xml’ ‘Filename’
    , ‘<?xml version="1.0" encoding="utf-8"?>’ + (
    select
    ‘ItemMasterDownload’ ‘@TransactionType’
    , Substring(replace(convert(varchar,sysdatetimeoffset(),120),’ ‘,’T’),1,charindex(‘T’,replace(convert(varchar,sysdatetimeoffset(),120),’ ‘,’T’),12)-1)
    + right(replace(convert(varchar,sysdatetimeoffset(),120),’ ‘,’T’),6) ‘@TransactionDateTime’
    , ‘DraperMES’ ‘@SystemID’
    , DB_Name() ‘@Environment’
    , newid() ‘@MessageID’
    ,(
    SELECT
    ‘<![CDATA[‘ + X.PLANT  + ‘]]>’ ‘Plant’
    , ‘<![CDATA[‘ + X.ITEMNUMBER  + ‘]]>’ ‘ItemNumber’
    , ‘<![CDATA[‘ + x.ItemRev  + ‘]]>’ ‘ItemRev’
    , ‘<![CDATA[‘ + x.ItemDescription  + ‘]]>’ ‘ItemDescription’
    , ‘<![CDATA[‘ + x.UOM  + ‘]]>’ ‘UOM’
    , ‘<![CDATA[‘ + x.Family  + ‘]]>’ ‘Family’
    , ‘<![CDATA[‘ + x.[Type]  + ‘]]>’ ‘Type’
    , ‘<![CDATA[‘ + x.IBUserReserveRef  + ‘]]>’ ‘IBUserReserveRef’
    FOR XML PATH (‘ItemMaster’), TYPE
    )
    FOR XML PATH (‘Root’)
    ) ‘XML’
    FROM x
    When I implement your solution, it executes, but returns the following:
    <?xml version="1.0" encoding="utf-8"?><![CDATA[UT02]]><![CDATA[ROD_FG]]><![CDATA[N/A]]><![CDATA[RodF FG test item]]><![CDATA[EA]]><![CDATA[TBV]]><![CDATA[S]]><![CDATA[]]>
    Here is the SQL used to get the return above:
    SELECT
    Convert(varchar(10),GetDate(),120) + ‘_ManualItemSubscription_’ + x.ItemNumber +’.xml’ ‘Filename’
    , ‘<?xml version="1.0" encoding="utf-8"?>’ + (
    select
    ‘ItemMasterDownload’ ‘@TransactionType’
    , Substring(replace(convert(varchar,sysdatetimeoffset(),120),’ ‘,’T’),1,charindex(‘T’,replace(convert(varchar,sysdatetimeoffset(),120),’ ‘,’T’),12)-1)
    + right(replace(convert(varchar,sysdatetimeoffset(),120),’ ‘,’T’),6) ‘@TransactionDateTime’
    , ‘DraperMES’ ‘@SystemID’
    , DB_Name() ‘@Environment’
    , newid() ‘@MessageID’
    ,(
    SELECT
    ‘<![CDATA[‘ + X.PLANT  + ‘]]>’ ‘Plant’
    , ‘<![CDATA[‘ + X.ITEMNUMBER  + ‘]]>’ ‘ItemNumber’
    , ‘<![CDATA[‘ + x.ItemRev  + ‘]]>’ ‘ItemRev’
    , ‘<![CDATA[‘ + x.ItemDescription  + ‘]]>’ ‘ItemDescription’
    , ‘<![CDATA[‘ + x.UOM  + ‘]]>’ ‘UOM’
    , ‘<![CDATA[‘ + x.Family  + ‘]]>’ ‘Family’
    , ‘<![CDATA[‘ + x.[Type]  + ‘]]>’ ‘Type’
    , ‘<![CDATA[‘ + x.IBUserReserveRef  + ‘]]>’ ‘IBUserReserveRef’
    FOR XML PATH (‘ItemMaster’), TYPE
    )
    FOR XML PATH (‘Root’),Type
    ).value(‘/Root[1]’,’nvarchar(max)’) ‘XML’
    FROM x

  139. neha

    STUFF((
                        SELECT ‘#!’ + RTRIM(x.PersonInstnAlias)
                       FROM ObjectViews.dbo.LookupPersonInstnAlias x
                       WHERE x.KeyPersonInstnAlias = x2_0.KeyPersonInstnAlias
                             ORDER BY x.KeyPersonInstnAlias
                       FOR XML PATH(”) ,  root(‘x.PersonInstnAlias’)
                              ,TYPE
                       ).value(‘.’, ‘varchar(max)’), 1, 2, ”) AS PersonInstnAlias
    Hi my piece of code is this .
    FOR XML could not serialize the data for node ‘NoName’ because it contains a character (0x001C) 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.
    and I am getting this error . please can anyone help ?
    Its very important .
    Thanks .

Comments are closed.

LobsterPot Blogs

Blog posts by Rob Farley and other LobsterPot Solutions team members.

Search