4

I am having an issue in a SQL procedure and I can't seem to find the proper solution. The stored procedure is containing one parameter of the XML datatype (name = @data).

An example of the incoming message is the following (the actual message is containing a lot more nodes, but I left them out 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 "Supplier" and it contains the exact same columns as the nodes in the XML (IDCONO, IDSUNO, IDSUTY,..)

I need to loop over the nodes and insert the data in the columns. I have implemented the procedure below, but this is giving me a lot of perfomance issues on the bigger files (long processing time, even timeouts):

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

Any help is appreciated! Note that the SQL version is SQL server 2012.

Thanks in advance.

Improve this question

1 Answer 1

Reset to default
6

The first I would try is the specify the text() node when using the XML datatype to prevent SQL Server from doing a deep search for text elements.

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

If that is not good enough I would try OPENXML instead.

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
Improve this answer
5
  • 1
    Hello, Thanks a lot for the reply. Adding the /text() only improves the performance a little bit. Using the OPENXML solution gives me the performance I need. I did some research and is it true that the OPENXML function allocates 1/8 of the memory of the server? Can I have issues if I call the stored procedure several times in a small time frame (with large xml files)?
    – Rise_against
    Commented May 27, 2013 at 14:00
  • 1
    @Rise_against I can not tell if you will have a problem. I can tell you that I use and have used openxml a lot to load data into a database. Largest batch would be about 25 GB XML files where the individual files is between 1 and 15 MB each. I have not seen any issues because we are loading a lot of files nor any problem because the files are big. But yes it is true. AFAIK, SQL Server allocates 1/8 of memory to the XML processing.
    – Mikael Eriksson
    Commented May 27, 2013 at 14:08
  • openxml is impressively faster. For 3000 records (4 columns), I reduced the execution time from 100.000 ms to 300 ms !
    – Yann39
    Commented May 6, 2015 at 15:05
  • @Yann39 that does not sound right. If you are on SQL Server 2008/2008 R2 you may be experiencing a bug that was fixed in CU1 but you need to enable a trace flag to enable the fix. Have a look at stackoverflow.com/a/3979266/569436
    – Mikael Eriksson
    Commented May 7, 2015 at 5:26
  • Indeed I'm on SQL Server 2008 v10.00.2573. Many thanks for the info!
    – Yann39
    Commented May 7, 2015 at 8:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.