2

I tried to shred XML into a temporary table by using XQuery .nodes as follows. But, I got performance problem. It is taking much time to shred. Please give me an idea on alternatives for this.

My requirement is to pass bulk records to a stored procedure and parse those records and do some operation based on record values.

 CREATE TABLE #DW_TEMP_TABLE_SAVE(  
[USER_ID] [NVARCHAR](30), 
[USER_NAME] [NVARCHAR](255)
)   

insert into #DW_TEMP_TABLE_SAVE
   select 
       A.B.value('(USER_ID)[1]', 'nvarchar(30)' ) [USER_ID], 
       A.B.value('(USER_NAME)[1]', 'nvarchar(30)' ) [USER_NAME]
   from 
       @l_n_XMLDoc.nodes('//ROW') as A(B)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1587872
  • 345
  • 1
  • 3
  • 14
  • Look into XML-indexes, and schemacollections. Basically, XML-shredding in SQL Server has a way of being insanely slow, until you find a way to put the data somewhere you can index it, and preferably even on an XML column typed as a specific schemacollection. Together they can make an operation that lasted for days, take no longer than 30 seconds for instance (that's what happened with me). Check here for a good example: http://stackoverflow.com/questions/61233/the-best-way-to-shred-xml-data-into-sql-server-database-columns – Kahn Apr 10 '14 at 05:05
  • 1
    Try to avoid using an XPath expression of `//Row` - that's **not** good for performance! Be **more specific** with your XPath! – marc_s Apr 10 '14 at 05:15
  • @Kahn Using XML indexes and especially the selective XML indexes in SQL Server 2012 SP1 can be a good thing but only if your data is stored in a table. Here the source is a XML variable `@l_n_XMLDoc`. – Mikael Eriksson Apr 10 '14 at 05:30
  • Thanks for your comments. Making XPath more specific made some difference for me. – user1587872 Apr 10 '14 at 05:46
  • @MikaelEriksson: You're right, in which case it's good to know that those options are open should there be a need to change the variable to a table, at least temporarily. I've had a similar scenario where initially some large amount of XML data was stored in a variable and had to find a way to increase the performance. In which case the solution was indeed to put it into a table so it could be indexed and made to use the proper schemacollection. :) – Kahn Apr 10 '14 at 09:24

1 Answers1

3

Specify the text() node in your values clause.

insert into #DW_TEMP_TABLE_SAVE
select A.B.value('(USER_ID/text())[1]', 'nvarchar(30)' ) [USER_ID], 
       A.B.value('(USER_NAME/text())[1]', 'nvarchar(30)' ) [USER_NAME]
from @l_n_XMLDoc.nodes('/USER_DETAILS/RECORDSET/ROW') as A(B)

Not using text() will create a query plan that tries concatenate the values from the specified node with all its child nodes and I guess you don't want that in this scenario. The concatenation part of the query if you don't use text() is done by the UDX operator and it is a good thing not to have it in your plan.

enter image description here

Another thing to try is OPENXML. In some scenarios (large xml documents) I have found that OPENXML performs faster. 

declare @idoc int
exec sp_xml_preparedocument @idoc out, @l_n_XMLDoc

insert into #DW_TEMP_TABLE_SAVE
select USER_ID, USER_NAME
from openxml(@idoc, '/USER_DETAILS/RECORDSET/ROW', 2) 
  with (USER_ID  nvarchar(30), USER_NAME nvarchar(30))

exec sp_xml_removedocument @idoc
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281