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
|
1 2 3 4 5 6 7 8 9 |
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.
|
1 2 3 4 5 6 7 8 9 |
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 <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.
|
1 2 3 4 5 6 7 8 9 |
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.
This Post Has 141 Comments
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..
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)’)
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
Yes, true. Thanks Brad & Alejandro. I’ve put an "Edit" on my post accordingly.
perfect! i was starting to worry i’d have to write something dreadful to handle concatenating strings with special characters. thank you!!
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" <Name>Croydon</Name></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
..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.
Cool trick…
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?
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
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
this is the best hack ever..Thanx a ton !!
ranu.mandan@gmail.com
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……….
I am serious here but is there anybody here able to convert sql special characters to XML characters for example
& &
< <
> >
" "
‘ '
To the XML characters?
Something like this should do it for you:
select ‘&’ for xml path(”)
Hope this helps… 🙂
On the lines of your solution, i am trying to solve & 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)
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).
thanks for you reply but no luck.
Fails with the same error. "Incorrect syntax near ‘.’.
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).
thanks Rob, that worked like a dream.
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
Thanks Rob!!!! helped me solve my issue with in a minute 🙂
Hi Sarah,
It should be fine.
Rob
Ok this is great and it works on a one by one basis but i have been struggling with this & 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), ‘>’,’>’)),’<’,'<‘)),””,”)) 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), ‘>’,’>’)),’<’,'<‘)),’’’,’'’)) as "DrivingDirections",
— End Driving Directions
————————-My Big Problem Area ————————–
–Start Description
–‘<![CDATA[‘ + (REPLACE((REPLACE((REPLACE((Left(NCopyXml,1999)), ‘>’,’>’)),’<’,'<‘)),””,”)) + ‘]]>’ as "SubDescription",
–REPLACE((REPLACE((REPLACE(NCopyxml, ‘>’,’>’)),’<’,'<‘)),””,”) 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)’)
, ‘’’,’'’)) 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, ‘>’,’>’)),’<’,'<‘)),””,”)) 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, ‘>’,’>’)),’<’,'<‘)),””,”)) 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 & every time. It is killing me. for example ’ becomes &rsquo; Do you have any fix.
the field outputs like this
<SubDecription><p>
<strong>Ranked 2012 by Golfweek &ndash; Ask About Free Golf Membership with Home Purchase for a Limited Time!</strong></p>
<p>
Championship golf, resort-style amenities, charming streetscapes and friendly front porches &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&#39;re sure to love the way of life with recreation and relaxation at your doorstep all just 5 minutes with easy access</p>
<p>
A variety of homes from Paco&#39;s award-winning portfolio offer innovative plans designed for the way you want to live with first floor owner&#39;s suites available, lots of flexible space, today&#39;s best features and our highest standards for quality and efficiency. Personalize your new home inside and out at Paco&#39;s assistance of a dedicated designer. And all Paco homes are protected by the Paco 5 Year &ndash;Warranty, homebuilding&#39;s best.</p>
</SubDecription>
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
Thanks agam!!
Thanks, my problem solved.
Hi
I have german umlaute (?, ä, ..) in my sql table
Is there a way to xml-lize them during SELECT … FOR XML PATH?
Regards
Klaus
Yes. If you don’t put the .value stuff in there, it’ll encode it for you.
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!
Great post!
It’s very helpful.
Thanks Rob, very helpful for my Report Data fetching across multiple rows :).
Brilliant. Cheers.
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.
This is the best way to kepp HTML Tags while using XML PATH!
Thank you!!!
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.
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.
Thumbs up!
Hi Rob
can you help me with resolving issue with ‘
’ value after each word like as you were getting ‘>’after each name.
Yes Tusky – that’s what this post is about. Follow the instructions and if should do the trick.
Thanks Rob
This absolutely brillant code that I’ve used more than once. Thanks Rob!
Thanks Rob. Would you please explain the use of below code
,type).value(‘(./text())[1]’,’varchar(max)’)
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
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
This is the best solution forever.
Thank you very much Rob.
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
Very good Trick! I was stuck because the string contains HTML codes, but I found this. Thank you
Thanq So Much I never Forget U r Help…..
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
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 & in the xml output for Servicename, rather get the ‘… & ….’
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
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?
Thanks for responding so quickly Rob, unfortunately it didn’t work
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!!!!
🙂
Thanks man. Thanks a lot. its worked…..
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
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
Hi Rob, thanks for answering!
that was the proplem. Meanwhile I got the same answers at other forums…
Thanks anyway!
Rob, your post saved me much time and frustration. Just wanted to say thanks!
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
Sorry just found a problem on the previous post:
Heres what I really have.
FOR XML PATH(”), TYPE ).value(‘.’,’VARCHAR(MAX)’)
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
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
If the table is being dynamically generated, why not dynamically generate a view over it that includes what you need?
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 >
< as <
“ as "
& as &
’ as '
Please help me out.
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
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?
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
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 ‘’ , replace it with the SQL equivalent CHAR(0x001A) for the update operation and use a placeHolter to store the data as XML. A mess…
Yeah, sounds like it.
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,”))
Thanks. Great soulution for an annoying "problem"
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?
I think you’ve got misplaced parentheses – I see three after the @metric_name variable.
Ali – sorry for not responding sooner. Try using a binary collation…
Fantastic, saved me a lot of time and trouble!
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
Thanks a lot, nice tip!!!
Thanks and this article is so helpful to me.
Thanks for the post. This is most useful.
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 & in the main query since one of the values is &.
Previously it was returning &amp;…now the output is & which should be only &
Thanks
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?
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 &
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
You’re doing "XML Auto, Elements" at the end, so that will make your contents be & rather than &. You’re actually wanting an XML form, right? So it’s valid to have & – and you’ve solved the fact that it’s not &amp;
Thank you. Appreciate your comments and your help.
Thanks for article. A very nce article for XML Datatype.
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.
Thanks for this, I hit this issue and you have helped me solve it in no time
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?
Sorry Warren – "SET QUOTED_IDENTIFIER must be ON when you invoke XML data type methods."
This is from the official documentation, at https://msdn.microsoft.com/en-us/library/ms174393.aspx
Thanks Rob (+ Brad and Alejandro), a really useful article and a very handy little line of code.
Hi Rob,
–Cleanup
UPDATE dbo.Note
SET Note = ltrim(rtrim(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(Note ,’ ’,char(20))
,’<’,'<‘)
,’>’,’>’)
,’ ’,char(10))
,’ ’,char(9))
,’’,’char(7)’)
,’’,’char(11)’)
,’’,’char(1)’)
,’’,’char(21)’)
,’’,’char(19)’)
,’’,’char(12)’)
–,’&’,’&’
))
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
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;
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
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)
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.
Rip out that character first, as it’s not allowed in XML. Then see how you go.
What is the syntax for ).value(‘.’, ‘NVARCHAR(MAX)’) and what does it mean ?
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.
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’,
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
Hey Rob, I sorted it out. All I needed was to use Type and ignore .value code.
Great article here anyway. Cheers
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)
G – what output are you trying to achieve?
hi could you please explain the XQuery you use? Thank you.
Hi John – which bit?
hi rob, this part: ‘(./text())[1]’ Thank you.
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).
Great Code.. Thank you Rob 🙂
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, ”)
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
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?
Hi Zaheer,
What’s the error you’re getting?
Rob
Hi Rob,
The solution provided on 3rd June 2012 worked for me.. Thank You.
Great. I’m not sure which one you mean, but great!
Hi Rob, I have an xml column type that has the < and > 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!
Maybe try:
select ‘, <‘ + cast(name as varchar(max)) + ‘>’
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.
Thanks Rob – Great post – I’ve been looking for a solution to this for awhile. Your time is very much appreciated.
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 < and >. How do i replaced them by < and >.
Try:
SELECT (
select Body
from W6SETTINGS
where Category = ‘Background Optimization’
FOR XML PATH(”)
,TYPE).value(‘.’,’varchar(max)’)
…or nvarchar, of course.
Thanks Rob. That worked like a charm.
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.
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)’)
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>..
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)’)
Perfect 🙂
Just one correction in above.. Row 4 – After w6settings, ‘b’ is missing.
Thanks a lot Rob.
Fixed that now. I wrote it on my phone…
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
Really, it’s cool trick. it helped me a lot.
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
Thank you for sharing Rob.
All the best!
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.
Thanks Brad. it helped me lot.
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??
Lol, people straight up giving you their entire issues to solve… contracting isn’t free people!
Great post, worked out nicely.
This is still great. 7 years on people are still finding this immensely helpful and informative!
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 ‘>’ and ‘<’.
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><![CDATA[UT02]]></Plant><ItemNumber><![CDATA[ROD_FG]]></ItemNumber><ItemRev><![CDATA[N/A]]></ItemRev><ItemDescription><![CDATA[RodF FG test item]]></ItemDescription><UOM><![CDATA[EA]]></UOM><Family><![CDATA[TBV]]></Family><Type><![CDATA[S]]></Type><IBUserReserveRef><![CDATA[]]></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
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.