Azure Sql : Unable to Replace HTML String
Posted By: Anonymous
I have below html in the database
<table>
<tbody>
<tr>
<td>
<table height="36" align="center" valign="middle" border="0" cellpadding="0" cellspacing="0" class="tablet-button">
<tbody>
<tr>
<td width="auto" align="center" valign="middle" height="36" style=" background-color: __CustomButtonBackgroundColor__; border-top-left-radius: 4px; border-bottom-left-radius: 4px; border-top-right-radius: 4px; border-bottom-right-radius: 4px; background-clip: padding-box; font-size: 15px; font-family: Helvetica, arial, sans-serif; text-align: center; color: __CustomButtonForegroundColor__; font-weight: 10; padding-left: 25px; padding-right: 25px;">
<span style="color: __CustomButtonForegroundColor__; font-weight: 300;">
<a style="color: __CustomButtonForegroundColor__; text-align: center; text-decoration: none;" href="__ConfirmLink__" target="_blank">Confirm</a>
</span>
</td>
</tr>
</tbody>
</table>
</td>
</tr>
<!-- /button -->
<!-- spacing -->
<tr>
<td width="100%" height="15"></td>
</tr>
</tbody>
</table>
and am trying to append additional table row to make html as below
<tr>
<td>
<table height="36" align="center" valign="middle" border="0" cellpadding="0" cellspacing="0" class="tablet-button">
<tbody>
<tr>
<td width="auto" align="center" valign="middle" height="36" style=" background-color: __CustomButtonBackgroundColor__; border-top-left-radius: 4px; border-bottom-left-radius: 4px; border-top-right-radius: 4px; border-bottom-right-radius: 4px; background-clip: padding-box; font-size: 15px; font-family: Helvetica, arial, sans-serif; text-align: center; color: __CustomButtonForegroundColor__; font-weight: 10; padding-left: 25px; padding-right: 25px;">
<span style="color: __CustomButtonForegroundColor__; font-weight: 300;">
<a style="color: __CustomButtonForegroundColor__; text-align: center; text-decoration: none;" href="__ConfirmLink__" target="_blank">Confirm</a>
</span>
</td>
</tr>
<tr style="visibility: __displayProp__">
<td style="font-family: Helvetica, arial, sans-serif; font-size: 14px; color: #95a5a6; text-align: left; line-height: 35px; text-align: center">
<span style="color: #FFFFFF; font-weight: 300;">
<a style="color: #4d4d4d; text-align: center;" href="__CancelLink__" target="_blank">notification.templates.key</a>
</span>
</td>
</tr>
</tbody>
</table>
</td>
</tr>
when I replace the html manually as below it works fine.
DECLARE @html varchar(MAX) = '<table>
<tbody>
<tr>
<td>
<table height="36" align="center" valign="middle" border="0" cellpadding="0" cellspacing="0" class="tablet-button">
<tbody>
<tr>
<td width="auto" align="center" valign="middle" height="36" style=" background-color: __CustomButtonBackgroundColor__; border-top-left-radius: 4px; border-bottom-left-radius: 4px; border-top-right-radius: 4px; border-bottom-right-radius: 4px; background-clip: padding-box; font-size: 15px; font-family: Helvetica, arial, sans-serif; text-align: center; color: __CustomButtonForegroundColor__; font-weight: 10; padding-left: 25px; padding-right: 25px;">
<span style="color: __CustomButtonForegroundColor__; font-weight: 300;">
<a style="color: __CustomButtonForegroundColor__; text-align: center; text-decoration: none;" href="__ConfirmLink__" target="_blank">Confirm</a>
</span>
</td>
</tr>
</tbody>
</table>
</td>
</tr>
<!-- /button -->
<!-- spacing -->
<tr>
<td width="100%" height="15"></td>
</tr>
</tbody>
</table>'
SELECT @html = REPLACE(@html,
'<tbody>
<tr>
<td width="auto" align="center" valign="middle" height="36" style=" background-color: __CustomButtonBackgroundColor__; border-top-left-radius: 4px; border-bottom-left-radius: 4px; border-top-right-radius: 4px; border-bottom-right-radius: 4px; background-clip: padding-box; font-size: 15px; font-family: Helvetica, arial, sans-serif; text-align: center; color: __CustomButtonForegroundColor__; font-weight: 10; padding-left: 25px; padding-right: 25px;">
<span style="color: __CustomButtonForegroundColor__; font-weight: 300;">
<a style="color: __CustomButtonForegroundColor__; text-align: center; text-decoration: none;" href="__ConfirmLink__" target="_blank">Confirm</a>
</span>
</td>
</tr>
</tbody>'
,'<table height="36" align="center" valign="middle" border="0" cellpadding="0" cellspacing="0" class="tablet-button">
<tbody>
<tr>
<td width="auto" align="center" valign="middle" height="36" style=" background-color: __CustomButtonBackgroundColor__; border-top-left-radius: 4px; border-bottom-left-radius: 4px; border-top-right-radius: 4px; border-bottom-right-radius: 4px; background-clip: padding-box; font-size: 15px; font-family: Helvetica, arial, sans-serif; text-align: center; color: __CustomButtonForegroundColor__; font-weight: 10; padding-left: 25px; padding-right: 25px;">
<span style="color: __CustomButtonForegroundColor__; font-weight: 300;">
<a style="color: __CustomButtonForegroundColor__; text-align: center; text-decoration: none;" href="__ConfirmLink__" target="_blank">Confirm</a>
</span>
</td>
</tr>
<tr style="visibility: __displayProp__">
<td style="font-family: Helvetica, arial, sans-serif; font-size: 14px; color: #95a5a6; text-align: left; line-height: 35px; text-align: center">
<span style="color: #FFFFFF; font-weight: 300;">
<a style="color: #4d4d4d; text-align: center;" href="__CancelLink__" target="_blank">notification.templates.key</a>
</span>
</td>
</tr>
</tbody>')
print @html
but when I try doing it directly on the db colmn’s html as below, its not working where
DECLARE @html VARCHAR(MAX) = (SELECT top 1 BodyText
FROM [MessageTemplate]
WHERE body LIKE '%<table height="36" align="center" valign="middle" border="0" cellpadding="0" cellspacing="0" class="tablet-button">%')
SELECT @html = REPLACE(@html,
'<tbody>
<tr>
<td width="auto" align="center" valign="middle" height="36" style=" background-color: __CustomButtonBackgroundColor__; border-top-left-radius: 4px; border-bottom-left-radius: 4px; border-top-right-radius: 4px; border-bottom-right-radius: 4px; background-clip: padding-box; font-size: 15px; font-family: Helvetica, arial, sans-serif; text-align: center; color: __CustomButtonForegroundColor__; font-weight: 10; padding-left: 25px; padding-right: 25px;">
<span style="color: __CustomButtonForegroundColor__; font-weight: 300;">
<a style="color: __CustomButtonForegroundColor__; text-align: center; text-decoration: none;" href="__ConfirmLink__" target="_blank">Confirm</a>
</span>
</td>
</tr>
</tbody>'
,'<table height="36" align="center" valign="middle" border="0" cellpadding="0" cellspacing="0" class="tablet-button">
<tbody>
<tr>
<td width="auto" align="center" valign="middle" height="36" style=" background-color: __CustomButtonBackgroundColor__; border-top-left-radius: 4px; border-bottom-left-radius: 4px; border-top-right-radius: 4px; border-bottom-right-radius: 4px; background-clip: padding-box; font-size: 15px; font-family: Helvetica, arial, sans-serif; text-align: center; color: __CustomButtonForegroundColor__; font-weight: 10; padding-left: 25px; padding-right: 25px;">
<span style="color: __CustomButtonForegroundColor__; font-weight: 300;">
<a style="color: __CustomButtonForegroundColor__; text-align: center; text-decoration: none;" href="__ConfirmLink__" target="_blank">Confirm</a>
</span>
</td>
</tr>
<tr style="visibility: __displayProp__">
<td style="font-family: Helvetica, arial, sans-serif; font-size: 14px; color: #95a5a6; text-align: left; line-height: 35px; text-align: center">
<span style="color: #FFFFFF; font-weight: 300;">
<a style="color: #4d4d4d; text-align: center;" href="__CancelLink__" target="_blank">notification.templates.key</a>
</span>
</td>
</tr>
</tbody>')
print @html
Can someone please help me with this?
Solution
Please try the following solution.
It is treating HTML as (X)HTML, i.e. XML.
After that MS SQL Server’s XQuery makes it easy via .modify() method.
SQL
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, BodyText XML);
INSERT INTO @tbl (BodyText) VALUES
(N'<table>
<tbody>
<tr>
<td>
<table height="36" align="center" valign="middle" border="0"
cellpadding="0" cellspacing="0" class="tablet-button">
<tbody>
<tr>
<td width="auto" align="center" valign="middle"
height="36"
style=" background-color: __CustomButtonBackgroundColor__; border-top-left-radius: 4px; border-bottom-left-radius: 4px; border-top-right-radius: 4px; border-bottom-right-radius: 4px; background-clip: padding-box; font-size: 15px; font-family: Helvetica, arial, sans-serif; text-align: center; color: __CustomButtonForegroundColor__; font-weight: 10; padding-left: 25px; padding-right: 25px;">
<span style="color: __CustomButtonForegroundColor__; font-weight: 300;">
<a style="color: __CustomButtonForegroundColor__; text-align: center; text-decoration: none;"
href="__ConfirmLink__" target="_blank">Confirm</a>
</span>
</td>
</tr>
</tbody>
</table>
</td>
</tr>
<!-- /button -->
<!-- spacing -->
<tr>
<td width="100%" height="15"></td>
</tr>
</tbody>
</table>');
DECLARE @newTR XML =
N'<tr style="visibility: __displayProp__">
<td style="font-family: Helvetica, arial, sans-serif; font-size: 14px; color: #95a5a6; text-align: left; line-height: 35px; text-align: center">
<span style="color: #FFFFFF; font-weight: 300;">
<a style="color: #4d4d4d; text-align: center;" href="__CancelLink__"
target="_blank">notification.templates.key</a>
</span>
</td>
</tr>';
DECLARE @html XML = (SELECT BodyText FROM @tbl);
-- before
SELECT @html;
SET @html.modify('insert sql:variable("@newTR") into (/table/tbody/tr/td/table/tbody)[1]');
-- after
SELECT @html;
Answered By: Anonymous
Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.
