Solved

tsql to search and replace XML tags

Posted on 2011-09-29
15
578 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
  • 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 500 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 59

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 59

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Query Missing Money orders... 6 70
SQL Script to find duplicates 16 19
Access Date Query 28 29
Fixed Length SQL Query Question 3 12
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now