How can the remove the attribute "CRS" from all the nodes of the XML through T-sql. Looking to remove the attribute "CRS" if exist in the XML.
<list>
<group id="12345">
<entry id="1" type="Audio" lang="en-us">
<p data-its-style="">
<audio xmlns="http://www.imsglobal.org/xsd/imsqtiv2p2_html5_v1p0" class="sound_explicit">
<source crs="test1_en.ogg" type="audio/ogg; " src="1234.ogg" />
<source crs="test1_en.m4a" type="audio/mp4;" src="4567.mp4" />
</audio>
</p>
</entry>
</group>
<group id="67890">
<entry id="4" type="Audio" lang="es-mx">
<p data-its-style="">
<audio xmlns="http://www.imsglobal.org/xsd/imsqtiv2p2_html5_v1p0" class="sound_explicit">
<source crs="test4_en.ogg" type="audio/ogg; " src="1234.ogg" />
<source crs="test4_en.m4a" type="audio/mp4;" src="4567.mp4" />
</audio>
</p>
</entry>
</group>
this should do the trick
DECLARE @xml XML = '<list>
<group id="12345">
<entry id="1" type="Audio" lang="en-us">
<p data-its-style="">
<audio xmlns="http://www.imsglobal.org/xsd/imsqtiv2p2_html5_v1p0" class="sound_explicit">
<source crs="test1_en.ogg" type="audio/ogg; " src="1234.ogg" />
<source crs="test1_en.m4a" type="audio/mp4;" src="4567.mp4" />
</audio>
</p>
</entry>
</group>
<group id="67890">
<entry id="4" type="Audio" lang="es-mx">
<p data-its-style="">
<audio xmlns="http://www.imsglobal.org/xsd/imsqtiv2p2_html5_v1p0" class="sound_explicit">
<source crs="test4_en.ogg" type="audio/ogg; " src="1234.ogg" />
<source crs="test4_en.m4a" type="audio/mp4;" src="4567.mp4" />
</audio>
</p>
</entry>
</group>
</list>'
/*Before*/
SELECT @xml
/*Delete*/
SET @xml.modify ('declare namespace ns="http://www.imsglobal.org/xsd/imsqtiv2p2_html5_v1p0"; delete list/group/entry/p/ns:audio/ns:source/@crs')
/*After*/
SELECT @xml