SQL XML Process Performance: Insert into Table Columns

Question

I am having a problem in an SQL procedure and I cannot find a suitable solution. The stored procedure contains one parameter of the XML data type (name = @data).

An example of an incoming message is the following (the actual message contains many more nodes, but I left them for simplicity):

<Suppliers xmlns=""> <Supplier> <IDCONO>3</IDCONO> <IDSUNO>009999</IDSUNO> <IDSUTY>0</IDSUTY> </Supplier> </Suppliers> 

In my SQL database, I have a table called "Provider" and it contains the same columns as the nodes in XML (IDCONO, IDSUNO, IDSUTY, ..)

I need to iterate over nodes and insert data into columns. I followed the procedure below, but it gives me a lot of performance problems in large files (long processing time, even timeouts):

 INSERT INTO SUPPLIER (IDCONO ,IDSUNO ,IDSUTY) SELECT TCvalue('IDCONO[1]', 'VARCHAR(50)') as IDCONO, TCvalue('IDSUNO[1]', 'VARCHAR(50)') as IDSUNO, TCvalue('IDSUTY[1]', 'VARCHAR(50)') as IDSUTY from @data.nodes('/Suppliers/Supplier') T(C) 

Any help is appreciated! Please note that the SQL version is SQL Server 2012.

Thanks in advance.

Answer 1

The first thing I'll try is to specify the text() node when using the XML data type so that SQL Server does not perform a deep search for text elements.

 INSERT INTO SUPPLIER (IDCONO ,IDSUNO ,IDSUTY) SELECT TCvalue('(IDCONO/text())[1]', 'VARCHAR(50)') as IDCONO, TCvalue('(IDSUNO/text())[1]', 'VARCHAR(50)') as IDSUNO, TCvalue('(IDSUTY/text())[1]', 'VARCHAR(50)') as IDSUTY FROM @data.nodes('/Suppliers/Supplier') T(C) 

If this is not good enough, I would try OPENXML.

 DECLARE @idoc INT EXEC sp_xml_preparedocument @idoc OUT, @data INSERT INTO SUPPLIER (IDCONO ,IDSUNO ,IDSUTY) SELECT IDCONO, IDSUNO, IDSUTY FROM OPENXML(@idoc, '/Suppliers/Supplier', 2) WITH (IDCONO VARCHAR(50), IDSUNO VARCHAR(50), IDSUTY VARCHAR(50)) EXEC sp_xml_removedocument @idoc