I have a nightly process that shreds XML data from a 17 million row table (120 GB) into another table, it takes about 3 hours and inserts about 1.5 million records.
I currently do a complete drop and reload. I've tested just shredding and inserting new records, but haven't seen much of an improvement (tried
left outer join and
not exists and
except). I use
cross apply; the source table does not have a clustered index so I cannot create an XML index (vendor table that I cannot modify). Is there a faster way to get this finished in less than 3 hours?
The query is basically this:
WITH XMLNAMESPACES(DEFAULT 'blahblahblah') INSERT INTO mytable SELECT ID, Value1.Field1.value('element1/text())','varchar(255)') AS myfieldvalue1 Value2.Field1.value('element2/text())','varchar(255)') AS myfieldvalue2 FROM my table CROSS APPLY mytable.data.nodes('//path1') AS Value1(Field1) CROSS APPLY mytable.data.nodes('//path2') AS Value2(Field2)
I can't really post a sample of the XML, but it is big for some records.