SQL Server XML Insert Optimize?

Question

I have a 400k XML document trying to insert into an 86-column SQL Server 2008 table. It takes about 35 minutes to get that XML into a temp table using this code:

with XmlNamespaces ( 'urn:xpaykj-report-xml-1.0' as xkj)
 Select 
 xItem.value('declare namespace xkj="urn:xpaykj-report-xml-1.0"; (xkj:prop[@idx=1]) [1] ','Date') as [InputDate] 
 -- and then 85 other columns --
Into #loadtemp
 From @xmldoc.nodes('xkj:output-data/xkj:childs/xkj:child[@name="output"]/xkj:childs/xkj:child[@name="lvla"]/xkj:childs/xkj:child[@name="lvlb"]/xkj:childs/xkj:child[@name="lvlc"]/xkj:properties') as x(xItem)

I need to optimize... Something. But I am not sure what. Can anyone help me with ideas?

Thanks.

• Mikael Eriksson: Could you provide some sample XML that we can use to test on?
• Anon246: Could you also include the query plan? Hard to tell what's wrong without seeing what SQL Server is trying to do.

Answer 1

Can you stage it twice? First, shred the data into rows of XML, and then read the columns from the rows. Something like:

with XmlNamespaces ( 'urn:xpaykj-report-xml-1.0' as xkj) 
 Select 
 xItem.query('.') as RowXML
Into #loadtemp 
 From @xmldoc.nodes('xkj:output-data/xkj:childs/xkj:child[@name="output"]/xkj:childs/xkj:child[@name="lvla"]/xkj:childs/xkj:child[@name="lvlb"]/xkj:childs/xkj:child[@name="lvlc"]/xkj:properties') as x(xItem) 

should give you rows of XML; then apply your value statements to shred the table into columns.

Answer 2

Despite the answer depending on what SQL is doing, I've always used this whitepaper to tune my XML XPath in SQL Server with very good results. Some of the techniques have yielded 100's - 1000's of times speed improvements using some of the techniques. I highly recommend it for tuning cases like these:

http://msdn.microsoft.com/library/ms345118

Thanks, Eric

Answer 3

Is this a regular import? Try Integration Services. Its a bit different than T-SQL but, There XML Source should be quick.

1. A new SSIS project in SSDT
2. add a "file Connection manager" to represent the xml doc
3. add a "OLE Connection manager" to represent the destination database
4. Add a dataflow in the task pane
   1. XML Source component to bring the data into the pipeline
   2. Connect the correct source output to a OLE database destination

other additional steps that might be needed:

1. variable source file name

2. use an XML Task to run an XSLT to simplify the document, to filter the doc down to.

   <root>
    <properties ... />
   ...
   </root>
   

If You are uncomfortable with SSIS I would definitely tune, the article linked by Anon246 above, brought me massive improvements to we saw improvements there.

• Colin 't Hart: This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post.
• Matt: The how to answer page dose include "or a viable alternative". Should I have stated the alternative more clearly? SSIS is a posible option but, only worth it if the import happens often. Should I have left off the my concurring opinion in general? Or, sperated to comment on the prior answer? I would like to answer an more useful way.
• JNK: @Matt a more thorough answer would include a synopsis of how SSIS could be used for this, and if you refer to another user's answer being specific (i.e. "The section on optimizing for XML indexes was extremely useful") would improve the quality of the answer.