Issue when use for xml path and HTML tag
-
Saturday, January 19, 2013 6:57 AM
Hi
I am facing an html tag related issue when i use HTML tags and 'for XML path(' ')' in my query.I want to return HTML code from table using the following code.
select '<html><table><tr><td>'+col1+'</td></tr></table></html>' from table1 for xml path('').
But my result contains < and > instead of < and >.So i am not able to run this code in browser directly.Only solution i have found is using replace function for replacing > and < to < and >.
Is there any alternativr way to solve this without using replace funtion?
All Replies
-
Saturday, January 19, 2013 7:13 AMModeratorCan you post the query? Thanks.
Kalman Toth SQL 2008 GRAND SLAM
Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012 -
Saturday, January 19, 2013 3:58 PM
So that's the catch with FOR XML PATH we didn't tell you about. Since it is XML, special characters for XML are enticized.
Thankfully, there is a fairly simple solution, at the price of more XML mumbo-jumbo:
CREATE TABLE #temp (a varchar(120))
INSERT #temp (a) VALUES('<HTML><BODY><P>My text</P></BODY><HTML>'), ('Kalle Anka & co')
SELECT (SELECT a
FROM #temp
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
go
DROP TABLE #tempBy adding ,TYPE you instruct FOR XML PATH to generate the data with the xml data type. Then you can use the type method value to extract the value in the desired data type.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se- Marked As Answer by NikkRED Monday, January 21, 2013 4:26 AM
-
Sunday, January 20, 2013 5:15 AMModerator
Use type and value, see explanation and example in this blog post (close to the end of it):
Making a list and checking it twiceFor every expert, there is an equal and opposite expert. - Becker's Law
My blog
