regex - Remove attribute from XmlDoc in database column on retrieve with t-SQL -

i have database table column named 'mapsql'.

the value of column contains nvarchar value of xml document.

value stored in database:

<root>   <node1 attr1="foobar">     <child1 attr1="foo">     </child1>   </node1>   <node2 attr2="hello">   </node2> </root> 

on retrieve of column database, want able strip out of node's attributes. in above case, want able remove 'attr1' attributes , values.

sql document want returned. note 'attr1' attributes gone:

<root>   <node1>     <child1>     </child1>   </node1>   <node2 attr2="hello">   </node2> </root> 

i found possible using replace function, , regex, t-sql not support regex in replace function. there other options?

solution regex:

replace({0}.{1}, 'tooltipresourseid=\"[^\"]*\"', '') 

thanks!

try smth this:

declare @xml xml = n'<root>   <node1 attr1="foobar">     <child1 attr1="foo">     </child1>   </node1>   <node2 attr2="hello">   </node2> </root>'  set @xml.modify('delete (//@*[local-name()="attr1"])')  select @xml 

for selecting, can create scalar funtion gets xml value , returns cleared value:

create function [dbo].[clearxml] (     @xml xml ) returns xml begin     set @xml.modify('delete (//@*[local-name()="attr1"])')     return @xml end  create table #temp (value xml) declare @xml xml = n'<root>   <node1 attr1="foobar">     <child1 attr1="foo">     </child1>   </node1>   <node2 attr2="hello">   </node2> </root>'  insert #temp values (@xml)  select     dbo.clearxml(t.value) #temp t  drop table #temp