How can I avoid encoding when using "FOR XML PATH"?

Question

Using FOR XML PATH encodes my HTML making my hyperlinks useless. I see that others are using the value method to prevent encoding. However, this strips out the table structure I need. I could do a search replace, effectively decoding the html. That does not seem like the right approach.

The Company column in this query is the problem child:

DECLARE @tableHTML NVARCHAR(MAX)
DECLARE @subj VARCHAR(255)
    
SET @tableHTML =
    N'<style type="text/css">' +
    N'table, td {border-collapse: collapse; vertical-align: top; text-align: left;font-family: Calibri; font-size: 12px;}' +
    N'th {vertical-align: middle; text-align: left;border-bottom-style: solid; border-bottom-width: 0px; border-bottom-color: #CCCCCC; background-color: #000000; color: #FFFFFF;}' +
    N'td {border-bottom-style: solid; border-bottom-width: 0px; border-bottom-color: #CCCCCC}' +
    N'</style>' +

    N'<h3 style="font-family: Calibri">Contacts:</h3>' +

    N'<table id="bodyTable" cellpadding="5" cellspacing="5" style="border: 1px solid #C0C0C0; vertical-align: top; text-align: left; height: 100%; width: 700;">' +
    N'  <tr>' +
    N'      <th>ContactID</th>' +
    N'      <th>FullName</th>' +
    N'      <th>Salutation</th>' +
    N'      <th>Company</th>' +
    N'      <th>Email</th>' +
    N'  </tr>' +

    CAST((
            SELECT td = nContactID, ''
                , td = FullName, ''
                , td = Salutation, ''
                , td = N'<a href="' + Website + '">' + Company + N'</a>', ''
                , td = Email, ''
            FROM vContact
            WHERE LastName = 'Jetson'

            FOR XML PATH('tr'), TYPE 
        ) AS NVARCHAR(MAX)) +
    N'          </table>'
                                
    PRINT @tableHTML

score 5 · Answer 1 · answered Mar 02 '18 at 16:13

5

...FOR XML PATH(''),TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')

The .value('.', 'varchar(max)') is useful in many cases.

answered Mar 02 '18 at 16:13

Demian Berisford-Maynard

300
2
15

score 4 · Accepted Answer · edited May 23 '17 at 12:19

4

The point is: XML is not text with some extra characters!

Text within XML must not carry characters needed for the markup, especially the three chars of evil: <>&. You are not adding XML to your output but a string, that looks like XML. And the engine does what must be done: This string is encoded.

The path around: You must hand over XML instead of a string.

Try to replace this line

, td = N'<a href="' + Website + '">' + Company + N'</a>', ''

with this

,td = (SELECT Website AS [a/@href],Company AS a FOR XML PATH(''),TYPE)

UPDATE

Don't know if this would help you, but here you'll find a generic function which will create a XHTML table with header and body generically directly from a SELECT statement.

edited May 23 '17 at 12:19

Community

1
1

answered Oct 27 '16 at 23:04

Shnugo

66,100
9
53
114

Exactly what I needed. Thank you. Where can I find more information on the use of `[a/@href]`? I wonder what other escaped like tags are available. – Spencer Williamson Oct 28 '16 at 17:17
@SpencerWilliamson Sorry, did not see this earlier. [You find more information here](https://msdn.microsoft.com/en-us/library/ms189885.aspx) Just follow the links... – Shnugo Nov 08 '16 at 22:57

How can I avoid encoding when using "FOR XML PATH"?

2 Answers2

UPDATE