Just to start I have a product (that I did not create, so I cannot change the code) and it is outputting some XML derived from a fixed width text file. The XML is in the below format. I've written a query to replace the value of F[51] with F[37]. However, it is really slow. For a record with say 300 "L" nodes it takes about 1.5 minutes. This is going to be an insert trigger, so I'd like it to be a bit faster. I've included the SQL below. I've marked the part that is causing the slow down. Does anybody have any ideas to make this work a bit faster? Thanks.
XML
SQL Code
XML
Code:
<I>
<L>
<F></F>
<F></F>
etc...
</L>
<L>
<F></F>
<F></F>
etc...
</L>
etc...
</I>
SQL Code
Code:
declare @i int, @xml xml, @newxml varchar(max), @count int
set @xml = [some code to get the xml]
set @count = [some code to get the number of "L" nodes ... this part works fine already and is quick]
set @i = 1
-- Loop though the "L" records and replace F[51] with F[37]
/*************************/
/* This is the slow part */
/*************************/
while @i <= @count begin
set @xml.modify('insert text{(/I/L[sql:variable("@i")]/F[37]/text())} into (/I/L[sql:variable("@i")]/F[51])[1]')
set @i = @i + 1
end
/*************************/
/* Ends the slow part */
/*************************/
-- Need the <F/> to be in the format <F></F> when this is done.
select @newxml = replace(cast(@xml as varchar(max)), '<F/>', '<F></F>')