• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 612
  • Last Modified:

tsql to search and replace XML tags

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
ModSpace
Asked:
ModSpace
  • 8
  • 5
  • 2
1 Solution
 
wdosanjosCommented:
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
 
ModSpaceAuthor Commented:
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
 
wdosanjosCommented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
ModSpaceAuthor Commented:
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
 
ModSpaceAuthor Commented:
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
 
wdosanjosCommented:
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
 
ModSpaceAuthor Commented:
This does not work on the new XML

@No is only picking up the value in <FieldHeader> tag not the subtag of <No>
0
 
wdosanjosCommented:
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
 
ModSpaceAuthor Commented:
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
 
wdosanjosCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
ModSpaceAuthor Commented:
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
 
ModSpaceAuthor Commented:
I guess I will use SQL jobs or command line to save the output to files. Thanks for your help
0
 
ModSpaceAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 8
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now