How to remove all attributes from (X)HTML tags in SQL Server

Requirement Statement

This post is to explain a quick and brilliant way of removing attributes from (X)HTML tags in SQL Server. The solution was given by one of my good friend | guru Yitzhak Khabinsky (in), would like to share it for the benefit of community.

Few interesting ways:

–Delete the style attributes

Declare @tbl TABLE (ID INT PRIMARY KEY Identity(1,1), xhtmlData xml);

Insert into @tbl (xhtmlData) Values
('<p><span style="font-size: 18px;"><strong>Hello World</strong></span></p>'),
('<p> </p>'),
('<h2 style="font-size: 24px; text-align: center;">123456</h2>'),
('<p> </p>'),
('<p class="next-steps-intro" attr= "" style="margin-bottom: 0;"><span style="font-size: 18px;">
<strong>You are at home:</strong></span></p>')

Select * From @tbl
update @tbl set xhtmlData.modify('delete //@*'); 
Select * From @tbl

–Delete the style attributes ONLY for span Tag

Select * From @tbl
update @tbl SET xhtmlData.modify('delete //span/@style');
Select * From @tbl

–Delete multiple attributes for a Tag

Select * From @tbl
update @tbl SET xhtmlData.modify('delete /p/@*[local-name()=("class","attr")]');
Select * From @tbl

I had spend almost an hour with Yitzhak and shared many stuffs around xml/SQL Server etc. In fact, when I contacted , it was a mid night for him and he immediately came for a skype discussion. There are some really interesting things he shared and I requested Yitzhak to share all of them in a form of a blog which would really be a great asset to SQL Server/any tech communities.

With that to conclude this post, we would like to enhance xml support further in SQL Server future releases and to request you all to vote for the below feedback for the same.

https://feedback.azure.com/forums/908035-sql-server/suggestions/38142115-sql-server-vnext-post-2019-and-nosql-functionali

If you enjoyed this blog post, please feel free to share it with your friends!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s