How do I optimise XPath Querying over a large dataset? I have a table that has 220 M rows. Also the table has a xml column that has XML data. I have configured single file for data and another ndf for index. Table's primary key is guid column. When am querying the XML contents using xpath query and load the flatten XML data to another table it takes around 30 hrs to load the data. Since am in standard edtition, partition is not an option. Need some advice on same 
1. how to improve data load performance.
2. I have a non-clustered index configured on the table which will be rebuilt after load.
Create an XML index on the column, you will have to create a primary index and then a secondary index on the path or value you are querying. 
CREATE PRIMARY XML INDEX PXML_NAME ON SCHEMA.TABLE (XMLCOLUMN);
then create a secondary index for the { VALUE | PATH | PROPERTY } you are querying with Xpath

https://msdn.microsoft.com/en-us/library/ms191497.aspx and 
https://msdn.microsoft.com/en-us/library/bb934097.aspx