Tuesday, September 04, 2012 4:01 AM
I am working on XML data in Sql ,we are facing many performance issue in this process, can any one help me to short out this issue.
- Moved by Maggie LuoMicrosoft Contingent Staff, Moderator Sunday, September 09, 2012 9:27 AM (From:SQL Server Replication)
Tuesday, September 04, 2012 4:18 AM
Tuesday, September 04, 2012 4:24 AM
We are fetching XML data in a procedure,and when we are executing the procedure its taking 10-15 min.
Tuesday, September 04, 2012 4:47 AM
Tuesday, September 04, 2012 5:08 AM
In Stored procedure we are passing a XML data in a varchar() parameter ,later on we are fetching data from XML into a temporary table that we are using further in the procedure.
For XML manipulation we are using following Query.
SELECT status into #Status1 FROM (
SELECT cast('<x>'+replace(@source,',','</x><x>')+'</x>' as xml) as thexml) Status CROSS APPLY
(SELECT x.value('.','varchar(100)') as Status FROM thexml.nodes('x') as f(x)
Monday, September 24, 2012 9:32 AM
Try to create XML index for better performance.
If you create XML primary index,it will internally maintain each nodes as one recordset,so it will help in performance wise while shredding XML data into relational tables.
Refer for basic information on XML index - http://sathyadb.blogspot.in/2012/09/xqueryxpathxmlschemaxml-index_6.html
Refer for more informations on XML index - http://msdn.microsoft.com/en-us/library/ms345121(v=sql.90).aspx
Monday, September 24, 2012 10:11 AMAnswerer
We're going to need more information. Can you post some sample XML and all the proc code?
Wednesday, October 03, 2012 11:37 AM