Hello jacob

The question is this:

I need to add a progressive for each element of an XML document. I read your two interesting articles "XML Workshop 25" and "XML Workshop XVII - Writing a LOOP to process XML elements in TSQL" and merged the two.

The problem is this:

Until I have a few XML elements from loop (say up to 10) all right, but increasing the processing is very slow so that I stop.

xml docoment:

<flsProSoc>

<Accesso>

<Identificativo>2008123456789000</Identificativo>

<Erogatore>

<CodiceIstituto>010ABAZZ</CodiceIstituto>

</Erogatore>

<Entrata>

<Data>2008-03-18</Data>

<Ora>09:30</Ora>

</Entrata>

<!--

many other XML elements and subelements XML

-->

</Accesso>

.......................................

.......................................

</flsProSoc>

Code:

DECLARE @max-2 INT, @i INT, @xml XML

SELECT @xml=

(SELECT * FROM OPENROWSET(BULK

'C:\MYXML.xml'

, SINGLE_BLOB) as x)

SELECT

@max-2 = @xml.query('<e>

{ count(/flsProSoc/Accesso) }

</e>').value('e[1]','int')

SET @i = 1

WHILE @i <= @max-2 BEGIN

SET @xml.modify('

insert element MyId {sql:variable("@i")}

as first

into (flsProSoc/Accesso[position()=sql:variable("@i")])[1]

')

SET @i = @i + 1

END

I thank you in advance for your interest