Replacing xml namespace prefixes in SQL Server using XQuery

Question

I have an xml structure in an existing SQL Server 2005 database table with a specific namespace, I need to query for XML and in the process, change the namespace prefix to become the default namespace. Old xml has a namespace defined in the root directory of the node and child nodes, and I know how easy it is to replace the root, but not the child namespace of the node.

Current data:

<sg:Settings xmlns:sg="uri:mynamespace">
    <sg:SomeData xmlns:sg="uri:mynamespace"/>
</sg:Settings>

Using SQL:

WITH XMLNAMESPACES 
('uri:mynamespace' as sg)
SELECT 
    settingsNode.query('<Settings xmlns="uri:mynamespace"> { sg:SomeData } </Settings> ')
FROM 
    SettingsTable CROSS APPLY
    SettingsXml.nodes('/sg:Settings') T(settingsNode)

I can get the following:

<Settings xmlns="uri:mynamespace">
    <sg:SomeData xmlns:sg="uri:mynamespace"/>
</Settings>

But I'm trying to do this:

<Settings xmlns="uri:mynamespace">
    <SomeData/>
</Settings>

Is there a way to merge namespaces using SQL XQuery?

Answer 1

update tbl_Archive set XML.modify('delete /*:Document/*:Volume[text()]/@xmlns:sg')