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.
If you enjoyed this blog post, please feel free to share it with your friends!