?
Solved

tsql to search and replace XML tags

Posted on 2011-09-29
15
Medium Priority
?
595 Views
Last Modified: 2012-05-12
How can I search and replace in an XML file using TSQL?
For example I have an XML file as follows in a file at C:\TEMP\sample.xml
How can I read this file using tsql and search for tag <No> with value 1234 and change Value tag from Hello to "Hello World" and write the file back to C:\Temp\sample.new.xml

<root>
  <id="1" date="9/9/2009">
      <batchid">1</batchid>
      <batchtotal>100</batchtotal
      <FieldHeader No="1234" id="11">
           <No>1234</No>
           <Value>Hello</Value>
           <details>These are the details for 1234</details>
     </FieldHeader>
 </id>
  <id="2" date="9/9/2009">
      <batchid">2</batchid>
      <batchtotal>110</batchtotal
      <FieldHeader No="1235" id="11">
           <No>1235</No>
           <Value>Hello !</Value>
           <details>These are the details for 1235</details>
     </FieldHeader>
 </id>
</root>

         
0
Comment
Question by:ModSpace
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
15 Comments
 
LVL 23

Expert Comment

by:wdosanjos
ID: 36815697
Here is the first part (read and update the xml content).  I'm not sure how to write the file back, perhaps other experts can fill in on that. BTW, the xml file posted is malformed the corrected file follows.

declare @content xml
SELECT @content = cast(BulkColumn as xml)
  FROM OPENROWSET(BULK N'C:\temp\sample.xml', SINGLE_CLOB) as sample
  
set @content.modify('replace value of (/root/id[@id=1]/FieldHeader[1]/No/text())[1] with "1234"')
set @content.modify('replace value of (/root/id[@id=1]/FieldHeader[1]/Value/text())[1] with "Hello World!"')
set @content.modify('replace value of (/root/id[@id=2]/FieldHeader[1]/No/text())[1] with "1234"')
set @content.modify('replace value of (/root/id[@id=2]/FieldHeader[1]/Value/text())[1] with "Hello World!"')
	
select @content

Open in new window


sample.xml (corrected)
<root>
  <id id="1" date="9/9/2009">
      <batchid>1</batchid>
      <batchtotal>100</batchtotal>
      <FieldHeader No="1234" id="11">
           <No>1234</No>
           <Value>Hello</Value>
           <details>These are the details for 1234</details>
     </FieldHeader>
 </id>
  <id id="2" date="9/9/2009">
      <batchid>2</batchid>
      <batchtotal>110</batchtotal>
      <FieldHeader No="1235" id="11">
           <No>1235</No>
           <Value>Hello !</Value>
           <details>These are the details for 1235</details>
     </FieldHeader>
 </id>
</root>

Open in new window

0
 

Author Comment

by:ModSpace
ID: 36815827
Hi Sage,
Thanks for your solution but what I am looking for is search the xml file find the tag id which has 1234 in the No field and only change the Value field of that id not touching the other ids

Thanks for correcting the XML file. This was just a sample. Original file has 100s of these ids.

A loop over all the fields and replace the desired values would help.

Thanks
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 36816439
I think this what you are looking for.  

Updates the Value element of the FieldHeader where No = 1234.
declare @content xml
SELECT @content = cast(BulkColumn as xml)
  FROM OPENROWSET(BULK N'C:\temp\sample.xml', SINGLE_CLOB) as sample
  
set @content.modify('replace value of (/root/id/FieldHeader[@No=1234]/Value/text())[1] with "Hello World!"')
	
select @content

Open in new window


Please elaborate on this statement "A loop over all the fields and replace the desired values would help".
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:ModSpace
ID: 36816521
Hi wdosanjos,
I modified the XML to remove ambiguity. I want to search for same level fields and replace another same level field
Here in the example I want to search for <No></No> and replace <Value></Value>
Thanks for your patience. and help.
<root>
  <id id="1" date="9/9/2009">
      <batchid>1</batchid>
      <batchtotal>100</batchtotal>
      <FieldHeader Number="1234" id="11">
           <No>1234</No>
           <Value>Hello</Value>
           <details>These are the details for 1234</details>
     </FieldHeader>
 </id>
  <id id="2" date="9/9/2009">
      <batchid>2</batchid>
      <batchtotal>110</batchtotal>
      <FieldHeader Number="1235" id="11">
           <No>1235</No>
           <Value>Hello !</Value>
           <details>These are the details for 1235</details>
     </FieldHeader>
 </id>
</root>

Open in new window

0
 

Author Comment

by:ModSpace
ID: 36816620
Please elaborate on this statement "A loop over all the fields and replace the desired values would help".  -- I was thinking Regular programming not SQL
for each id
  if field No == some value
       replace field Value = New value.
   end if
end for
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 36816671
Please check if the following addresses your request:

declare @content xml
declare @update table (id varchar(10), value varchar(50))
declare @id varchar(10), @value varchar(50)

-- IDs and the corresponding update value
insert into @update (id, value) values ('1234', 'Hello World! 1234')
insert into @update (id, value) values ('1235', 'Hello World! 1235')

-- Load xml
SELECT @content = cast(BulkColumn as xml)
  FROM OPENROWSET(BULK N'C:\temp\sample.xml', SINGLE_CLOB) as sample

-- Update content
declare csr cursor for select id, value from @update

open csr

fetch next from csr into @id, @value
while @@fetch_status = 0 begin
	set @content.modify('replace value of (/root/id/FieldHeader[@No=sql:variable("@id")]/Value/text())[1] with sql:variable("@value")')
	fetch next from csr into @id, @value
end

-- Resulting xml
select @content

Open in new window

0
 

Author Comment

by:ModSpace
ID: 36816781
This does not work on the new XML

@No is only picking up the value in <FieldHeader> tag not the subtag of <No>
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 36817658
Please try the following. I fixed the XQuery accordingly.

declare @content xml
declare @update table (id varchar(10), value varchar(50))
declare @id varchar(10), @value varchar(50)

-- ID's and the corresponding update value
insert into @update (id, value) values ('1234', 'Hello World! 1234')
insert into @update (id, value) values ('1235', 'Hello World! 1235')

-- Load xml
SELECT @content = cast(BulkColumn as xml)
  FROM OPENROWSET(BULK N'C:\temp\sample.xml', SINGLE_CLOB) as sample

-- Update content
declare csr cursor for select id, value from @update

open csr

fetch next from csr into @id, @value
while @@fetch_status = 0 begin
	set @content.modify('replace value of (/root/id/FieldHeader[@Number=sql:variable("@id")]/Value/text())[1] with sql:variable("@value")')
	fetch next from csr into @id, @value
end

-- Resulting xml
select @content

Open in new window

0
 

Author Comment

by:ModSpace
ID: 36817771
Hi wdosanjos:
I think you got my question wrong. I want to look for <No> value not Number in the new example. and if <No> is certain value I want to replace <Value> tag with something else.

I think just a modify would not work in my case as I am searching for the tags and replacing them in the same level. I guess another approach would be to get a list of values from the xml as follows

array x = list of <id> which have  <No> = 1234
in this case x will be 1 the value assigned to Number of FieldHeader not the <No>1234</No>

using this value(s)
we can use the modify as above to replace the <Value> Tag of that <id> element.

If you can help me with a query / variable where I can get all the ids from this xml then that will assist a lot.

Thanks a lot for your help.
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 2000 total points
ID: 36817991
Please try the following.

declare @content xml, @icontent int
declare @id int, @value varchar(50)

-- Load xml
SELECT @content = cast(BulkColumn as xml)
  FROM OPENROWSET(BULK N'C:\temp\sample.xml', SINGLE_CLOB) as sample
  
exec sp_xml_preparedocument @icontent output, @content

declare csr cursor for 
	select id, Value
	  from openxml(@icontent, '/root/id', 1) 
		   with (id int './@id', 
			 No int './FieldHeader/No', 
			 Value varchar(200) './FieldHeader/Value')
	  where No = 1234
	  
open csr

-- Update content
fetch next from csr into @id, @value
while @@fetch_status = 0 begin
	set @value = 'Hello World!'
	set @content.modify('replace value of (/root/id[@id=sql:variable("@id")]/FieldHeader/Value/text())[1] with sql:variable("@value")')

	fetch next from csr into @id, @value
end

close csr
deallocate csr
exec sp_xml_removedocument @icontent

-- Resulting xml
select @content

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36869756
Since you are not storing this at all in SQL, why not do this transformation with XSLT? Not to discourage the wonderful technical conversation going on as I was very intrigued by it and was going to just monitor, but curious and figured I would speak up as it has already been difficult reading/modifying the XML and we have not even gotten to the challenging part which will be how do you write it back to the file system which has its own set of challenges.
0
 

Author Comment

by:ModSpace
ID: 36891933
Thanks wdosanjos: The last update works perfect for me. I will look for saving the results of the xml into a file and update this chain. If you find any before that please do share. I read that xp_cmdshell has some security risks in production so not considering it.

mwvisa1: Thanks for your comments. I have chose TSQL as part of the parameters are in SQL server tables that need to be applied to the XML transformations.
0
 

Author Comment

by:ModSpace
ID: 36892058
I guess I will use SQL jobs or command line to save the output to files. Thanks for your help
0
 

Author Closing Comment

by:ModSpace
ID: 36892076
Thanks. There are many option that I can try to save the results to file. The most important and difficult part was the XML manipulation which is addressed perfectly in the solution.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36894349
Glad that helped you. As I said, I was just curious. Here is one way to save the file:
http://daytabase.org/2011/08/20/bcp-utility/
0

Featured Post

Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question