XML Query

I have a table with 2 billion rows of which one column contains XML data. Actually am writing a SQL to extract data from the XML column ad populate in to another table. it really takes lot of time.. processing 3k rows per second. My XML query has about 30 columns mostly extracting data with xpath statements like below. Am using SQL server 2012 standard edition, so no table partitioning is possible.

SELECT Id,    a1 = CASE SortOrder                        WHEN 0 THEN Contents.value('(/a/Code)[1]', 'varchar(255)')                        ELSE NULL END,    a2 = CASE SortOrder                        WHEN 1 THEN Contents.value('(/a/Code)[1]', 'varchar(255)')                        ELSE NULL END,    a3 = Contents.value('(/b/c)[1]', 'varchar(255)'),         a4 = Contents.value('(/d/e/f[@category="aa"]/@categoryDate)[1]', 'datetimeOffset'),      a5 = Convert(varchar(max),STUFF((SELECT ',' + OTH.value('(.)', 'varchar(150)')     FROM Contents.nodes('/d/e/f[@category="g"]/SelectedCategories/SelectedCategory') AS Node(OTH) FOR XML PATH('')), 1, 1, '')) + ' & '    + ISNULL(Contents.value('(/d/e/f[@category="g"]/Freeform)[1]', 'varchar(255)'), ' ' )    FROM tbl

The query resides in SP and called from SSIS data flow task. I have added "Option MAXDOP 1" to avoid multiple threads.

Hardware is Intel Xenon CPU with 4 cores and 32 GB RAM. DFT has 70 MB buffer size and 12000 rows as buffer rows. Table is around 60 GB

  • Moved by Olaf HelperMVP 22 minutes ago Moved from "Database Engine" to a more specific forum for a better response
April 26th, 2015 2:38pm

 I am not familar with XML indexes .. but I wonder if adding XML indexes would help..

i would a second opnion on XML indexes and see if it helps.

Free Windows Admin Tool Kit Click here and download it now
April 26th, 2015 2:52pm

When retrieving element-centric data, it often helps to write the access path as '(/a/Code/text())[1]'.

It is also a good idea to use the nodes function to navigate to a common root, but the query is confusing because there appears to be more than one top node. (Which there should not be in a proper XML document.)

April 26th, 2015 3:40pm

Thanks for response. There are approximately 20 types of XML (different schemas) can exist in the records. Some records can have schema A, some can have schema "B" etc...so the different root nodes.

I will not be able to create XML indexes as load takes huge amount of time (5x times).

Please suggest some ideas

Free Windows Admin Tool Kit Click here and download it now
April 27th, 2015 3:25am

I will not be able to create XML indexes as load takes huge amount of time (5x times).
April 27th, 2015 3:25am

Thanks for response. There are approximately 20 types of XML (different schemas) can exist in the records. Some records can have schema A, some can have schema "B" etc...so the different root nodes.

I will not be able to create XML indexes as load takes huge amount of time (5x times).

Please suggest some ideas

What determines the schema of the XML stored in a particular row?
Free Windows Admin Tool Kit Click here and download it now
April 27th, 2015 3:29am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics