Updating specific column in a table that has XML using SQL

Question

I am trying to update a specific column in a table that has multiple namespaces in it. My table structure is:

+------+---------+-----------------------------------------------------------------------+
| cid  | cidtype |                               xml_data                                |
+------+---------+-----------------------------------------------------------------------+
| 1001 | N       | <Main  xmlns:json=""http://www.samplenamespace.com/json"" >           |
|      |         | <ID json:ValueType=""Number"">1001</ID>                               |
|      |         | <details>                                                             |
|      |         | <name xmlns:json=""http://www.samplenamespace.com/json"">John</name>  |
|      |         | <age  xmlns:json=""http://www.samplenamespace.com/json"">12</age>     |
|      |         | </details>                                                            |
|      |         | </Main>                                                               |
| 1003 | N       | <Main  xmlns:json=""http://www.samplenamespace.com/json"" >           |
|      |         | <ID json:ValueType=""Number"">1003</ID>                               |
|      |         | <details>                                                             |
|      |         | <name xmlns:json=""http://www.samplenamespace.com/json"">Diane</name> |
|      |         | <age  xmlns:json=""http://www.samplenamespace.com/json"">25</age>     |
|      |         | </details>                                                            |
|      |         | </Main>                                                               |
| 1004 | N       | <Main  xmlns:json=""http://www.samplenamespace.com/json"" >           |
|      |         | <ID json:ValueType=""Number"">1004</ID>                               |
|      |         | <details>                                                             |
|      |         | <name xmlns:json=""http://www.samplenamespace.com/json"">Kippy</name> |
|      |         | <age  xmlns:json=""http://www.samplenamespace.com/json"">26</age>     |
|      |         | </details>                                                            |
|      |         | </Main>                                                               |
+------+---------+-----------------------------------------------------------------------+

In this table, I want to alter the xml_data column to remove the http://www.samplenamespace.com/json namespaces to all the subnodes except <Main> node.

My query:

update #final --#final is my table
set xml_data.modify(replace('xmlns:json="http://www.samplenamespace.com/json"',' ','') where.... ) -- I don't know to access the root node here

Any help?

Answer 1

Since you have two elements that have to be cleaned, you can use sql replace on the xml_data column to remove the namespace from the name element, then the age element. This will leave the main element as is with the required namespace.

How to replace a string in a SQL Server Table Column

create   TABLE [dbo].[foo]
(
cid int not null,
cidtype varchar(1) null,
xml_data varchar(max) null
)
go

insert into foo (cid,cidtype,xml_data) values
(1001,'N','<Main  xmlns:json=""http://www.samplenamespace.com/json"" >           
 <ID json:ValueType=""Number"">1001</ID>                               
 <details>                                                             
 <name xmlns:json=""http://www.samplenamespace.com/json"">John</name>  
 <age  xmlns:json=""http://www.samplenamespace.com/json"">12</age>     
 </details>                                                            
 </Main>')


 insert into foo (cid,cidtype,xml_data) values (1003, 'N',
 '<Main  xmlns:json=""http://www.samplenamespace.com/json"" >            
  <ID json:ValueType=""Number"">1003</ID>                                
  <details>                                                              
  <name xmlns:json=""http://www.samplenamespace.com/json"">Diane</name>  
  <age  xmlns:json=""http://www.samplenamespace.com/json"">25</age>      
  </details>                                                             
  </Main>')
  insert into foo (cid,cidtype,xml_data) values (1004,'N',
  '<Main  xmlns:json=""http://www.samplenamespace.com/json"" >            
 <ID json:ValueType=""Number"">1004</ID>                                
 <details>                                                              
 <name xmlns:json=""http://www.samplenamespace.com/json"">Kippy</name>  
 <age  xmlns:json=""http://www.samplenamespace.com/json"">26</age>      
 </details>                                                             
 </Main>')
 go
 select * from foo

 update foo
set xml_data = replace(xml_data, 'name xmlns:json=""http://www.samplenamespace.com/json""', 'name ')
go
update foo
set xml_data = replace(xml_data, 'age  xmlns:json=""http://www.samplenamespace.com/json""', 'age ')
go
select * from foo