Saturday, January 19, 2013 6:57 AM
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?
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
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
DROP TABLE #temp
By 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, email@example.com
- Marked As Answer by NikkRED Monday, January 21, 2013 4:26 AM
Sunday, January 20, 2013 5:15 AMModerator