Working on XML Data

Question

Hi,

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.

Thanks

Mukesh

Answer 1

Which type of replication is being used and what performance issues are you facing?

---

Brandon Williams (blog | linkedin)

Answer 2

Hi,

We are fetching XML data in a procedure,and when we are executing the procedure its taking 10-15 min.

Thanks

Mukesh

Answer 3

Hello Mukesh,

Can you please provide us more detailed information, how you process the XML data (and the relation to SQL Server Replication, the content of this forum)?

---

Olaf Helper
Blog Xing

Answer 4

Hi Olaf,

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)                                                              
                )Status1 

Thanks

Mukesh

Answer 5

Hi Mukesh,

               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

Regards,

Sathya

http://sathyadb.blogspot.in/

---

sathyas

Answer 6

We're going to need more information.  Can you post some sample XML and all the proc code?

Answer 7

Thanks Sathyanarrayanan ; its working perfectly