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)
     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?


  • 1
    Could you provide some sample XML that we can use to test on? Commented Dec 21, 2011 at 17:19
  • Could you also include the query plan? Hard to tell what's wrong without seeing what SQL Server is trying to do.
    – Anon246
    Commented Dec 21, 2011 at 21:35

3 Answers 3


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) 
     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.


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:


Thanks, Eric


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.

      <properties ... />

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.

  • 1
    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. Commented Apr 23, 2014 at 18:38
  • 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.
    – Matt
    Commented Apr 23, 2014 at 19:25
  • @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.
    – JNK
    Commented Apr 23, 2014 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.