4

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
Improve this question

2 Answers 2

Reset to default
6

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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.
@SpencerWilliamson Sorry, did not see this earlier. You find more information here Just follow the links...
5 
...FOR XML PATH(''),TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.