Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Could you provide some sample XML that we can use to test on? –  Mikael Eriksson Dec 21 '11 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 Dec 21 '11 at 21:35
add comment

migrated from stackoverflow.com Dec 21 '11 at 21:14

This question came from our site for professional and enthusiast programmers.

2 Answers

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.