locked
Issue when use for xml path and HTML tag RRS feed

  • Question

  • 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 &lt; and &gt 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 &gt; and &lt; to < and >. 

    
    Is there any alternativr way to solve this without using replace funtion?
    Saturday, January 19, 2013 6:57 AM

Answers

  • 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 #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, esquel@sommarskog.se
    • Marked as answer by NikkRED Monday, January 21, 2013 4:26 AM
    Saturday, January 19, 2013 3:58 PM

All replies

  • Saturday, January 19, 2013 7:13 AM
  • 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 #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, esquel@sommarskog.se
    • Marked as answer by NikkRED Monday, January 21, 2013 4:26 AM
    Saturday, January 19, 2013 3:58 PM
  • Use type and value, see explanation and example in this blog post (close to the end of it):

    Making a list and checking it twice

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Sunday, January 20, 2013 5:15 AM