Skip to content
Fix Code Error

Azure Sql : Unable to Replace HTML String

July 15, 2021 by Code Error
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

Related Articles

  • How to properly do JSON API GET requests and assign output…
  • python 3.2 UnicodeEncodeError: 'charmap' codec can't encode…
  • How to parse JSON with XE2 dbxJSON
  • Azure Availability Zone ARM Config
  • Navbar not filling width of page when reduced to mobile view
  • The 'compilation' argument must be an instance of…
  • How to use html template with vue.js
  • Search match multiple values in single field in…
  • Event Snippet for Google only shows one event while testing…
  • Avoid creating new session on each axios request laravel

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

tqdm notebook – 2 inner bars

Next Post:

Run a workflow when multiple other workflows have finished in github actions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

  • Get code errors & solutions at akashmittal.com
© 2022 Fix Code Error