asked on

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>

wdosanjos

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

ModSpace

ASKER

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

wdosanjos

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".

ModSpace

ASKER

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

ModSpace

ASKER

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

wdosanjos

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

ModSpace

ASKER

This does not work on the new XML

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

wdosanjos

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

ModSpace

ASKER

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.

ASKER CERTIFIED SOLUTION

wdosanjos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

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.

ModSpace

ASKER

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.

ModSpace

ASKER

I guess I will use SQL jobs or command line to save the output to files. Thanks for your help

ModSpace

ASKER

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.

Kevin Cross

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/