Pull back hyperlink from table intact

Question

Pull back hyperlink from table intact

donato1026

SSCrazy

Points: 2765
More actions
January 20, 2016 at 9:30 am

#310969

Hello everyone,
I am trying to pull back a hyperlink from a table, but when it is emailed to me, it shows the whole hyperlink and everything in the email. Here is my code:
SET @xml = CAST(( SELECT DEL_DOC_NUM AS 'td','',QTY AS 'td','',ITM_CD AS 'td','',LINK AS 'td'
FROM TransactionalData.dbo.EMAIL_REVIEW where email = 'email@company.com' GROUP BY DEL_DOC_NUM, QTY, ITM_CD, LINK FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX))
SET @body = '<html><H1>Rate and Review the following</H1><body><p>This is a test grabbing each line from the table and writing in it a table format with XML...</p><table border = 0 cellpadding=5><tr><th>DEL_DOC_NUM</th><th>QTY</th><th>ITEM_CD</th></tr>'
SET @body = @body + @xml + '</table></body></html>'
It sends the email like this though:
DEL_DOC_NUMQTYITEM_CD
1113529RH921.001234567<a href="http://www.mycompany.com/p-1111-cute-ottoman.aspx">Review this Product</a>
Is there a way for the URL to stay as a URL and just show the Review this Product text?
Any help would be greatly appreciate.
Cheers,
D
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

donato1026 (1/20/2016)
Hello everyone,
I am trying to pull back a hyperlink from a table, but when it is emailed to me, it shows the whole hyperlink and everything in the email. Here is my code:
...
It sends the email like this though:
DEL_DOC_NUMQTYITEM_CD
1113529RH921.001234567<a href="http://www.mycompany.com/p-1111-cute-ottoman.aspx">Review this Product</a>
Is there a way for the URL to stay as a URL and just show the Review this Product text?
Any help would be greatly appreciate.
Cheers,
D

Try this:

SET @xml = CAST((

SELECT DEL_DOC_NUM AS 'td',''

,QTY AS 'td',''

,ITM_CD AS 'td',''

,[highlight="#ffff11"]CAST(LINK AS XML)[/highlight] AS 'td'

FROM TransactionalData.dbo.EMAIL_REVIEW where email = 'email@company.com' GROUP BY DEL_DOC_NUM, QTY, ITM_CD, LINK FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX))

SET @body = '<html><H1>Rate and Review the following</H1><body><p>This is a test grabbing each line from the table and writing in it a table format with XML...</p><table border = 0 cellpadding=5><tr><th>DEL_DOC_NUM</th><th>QTY</th><th>ITEM_CD</th></tr>'

SET @body = @body + @xml + '</table></body></html>'

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

donato1026 SSCrazy Points: 2765 More actions · Answer 2

donato1026

SSCrazy

Points: 2765

January 21, 2016 at 3:00 pm

#1853662

That worked! Thank you SO MUCH!!!!!!!!

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

donato1026 (1/21/2016)
That worked! Thank you SO MUCH!!!!!!!!

Pleased to hear it.

Funnily enough, I had just been doing HTML email this way myself a day or so ago, so it was fresh in my mind 🙂

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

donato1026 SSCrazy Points: 2765 More actions · Answer 4

I guess it was meant to do then... 😉

Thanks again!