Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Insert Trigger Working With XML and TSQL

Status
Not open for further replies.

fredclown

Programmer
Aug 15, 2005
7
US
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
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>')
 
Too bad it's not just a varchar, 'cause then you could do this:

Code:
[COLOR=blue]Declare[/color] @var [COLOR=blue]varchar[/color](100)
[COLOR=blue]select[/color] @var = [COLOR=red]'<B12></B12>asdfhkajshdf<B3></B3>asdfasdf<B12></B12>'[/color]
[COLOR=blue]select[/color] [COLOR=#FF00FF]Replace[/color]([COLOR=#FF00FF]Replace[/color](@var, [COLOR=red]'<B12>'[/color], [COLOR=red]'<B23>'[/color]), [COLOR=red]'</B12>'[/color], [COLOR=red]'</B23>'[/color])

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]
 
I'm not sure the example you gave would work anyway. I'm trying to take the text value of F[37] and put it into F[51]. The example you gave would just rename the node. Besides the fact that you didn't put the data between the opening and closing nodes.
 
I thought you had a node import error. Are you actually trying to massage the data in SQL? Working with the node data would probably be faster in something like .Net.

Wait. Are you trying to always replace the 51st node with the data in the 37th node? For each L Node?

Your example detail (before and after) is not enough to figure out what you are trying to do.

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]
 
just thinking out loud here as I'm not completely sure how to handle your problem. CAn you just import the xml to a temp table, update the table normally and then export it as XML? Anything beats looping. Never loop in SQL SErver if you can help it.

"NOTHING is more important in a database than integrity." ESquared
 
It's sounding like I should think up something else. I would do it int .net, but the problem is that table that this data is put into is a staging table for it being put elsewhere. There is no guarantee that any .net program could get to the data fast enough before it was moved out to the real tables. I;m trying to make this an automated process. I'm thinking I may have to work with the source files and change those first before they even get into SQL2005.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top