Modifing XML document is too slow

I need insert many dates from tables to XML file, I use T-SQL, everything is OK, but it works too slow.

Is there another way? Thanks' a lot.

I use such sequence T-SQL:

Declare

@.doc XML,

@.ixml nvarchar(4000)

SELECT @.ixml=(SELECT * FROM SAMPLE "d:SAMPLE" for XML AUTO)

SET @.ixml = 'SET @.Doc.modify(''

declare namespace dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution";

declare namespace d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields";

insert ('+@.ixml+') into (dfs:myFields/dfs:dataFields)[1] '')'

exec sp_ExecuteSql @.STMT = @.ixml, @.PARAMS = N'@.DOC XML OUT', @.DOC = @.DOC OUT

In order for me to help you troubleshoot the performance please explain the shape of your XML data and the scenario you are using XML in.

General notes.

If your scenario is collecting changes made to a set of tables and aggregating them into an XML document you may want to aggregate each change into a smaller XML instance in a dedicated XML column and then aggregate multiple instances into a bigger XML document upon retrieval.

If your scenario is adding data from database tables to an existing document on the client side you may retrieve your data from the server in XML form using FOR XML and then combine the new and the existing XML documents using XSLT.

If you need to add data from database tables to an XML document stored in an XML variable/parameter the most performing will be to compose a new instance, for example:

WITH XMLNAMESPACES(

'http://schemas.microsoft.com/office/infopath/2003/dataFormSolution' AS dfs,

'http://schemas.microsoft.com/office/infopath/2003/ado/dataFields' AS d)

SELECT @.doc =

(

SELECT

@.doc.query('dfs:myFields/dfs:dataFields/*') AS "dfs:dataFields",

(SELECT * FROM SAMPLE FOR XML RAW('d:SAMPLE'), TYPE) AS "dfs:dataFields"

FOR XML PATH, TYPE, ROOT('dfs:myFields')

);

If reconstruction is too complex you can combine the old and new XML into a single instance, insert the new part into the appropriate place, and then delete the new part:

-- combine the old and the new XML

WITH XMLNAMESPACES(

'http://schemas.microsoft.com/office/infopath/2003/ado/dataFields' AS d)

SELECT @.doc =

(

SELECT

@.doc,

(SELECT * FROM SAMPLE FOR XML RAW('d:SAMPLE'), TYPE) AS "Wrapper"

FOR XML PATH, TYPE

);

-- insert the new XML fragment into the appropriate place

SET @.doc = @.doc.modify('

declare namespace dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution";

insert /Wrapper/* into (dfs:myFields/dfs:dataFields)[1]

');

-- delete the new XML fragment

SET @.doc = @.doc.modify('delete /Wrapper');

Inserting bigger XML fragments using dynamic query with modify(‘insert…’) method on XML type is not recommended in terms of performance.

Best regards,

Eugene Kogan,

Technical Lead,

SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

The code samples were not verified and may contain syntax errors.

|||

Thanks’ a lot.

I use completely another way, simple serializing with some handwriting end tags, time was decreased from 2 minutes 38 seconds to 7 seconds.