SQL 处理 XML 性能:插入表中的列 [英] SQL process XML performance: Insert into columns in a table

问题描述

我在 SQL 过程中遇到问题，但似乎找不到合适的解决方案.存储过程包含一个 XML 数据类型的参数(名称 = @data).

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>

在我的 SQL 数据库中，我有一个名为Supplier"的表，它包含与 XML 中的节点完全相同的列(IDCONO、IDSUNO、IDSUTY 等)

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)

感谢任何帮助！注意SQL版本为SQL server 2012.

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

提前致谢.

推荐答案

我首先要尝试的是在使用 XML 数据类型时指定 text() 节点以防止 SQL Server搜索文本元素.

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)

如果这还不够好，我会尝试使用 OPENXML.

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

这篇关于SQL 处理 XML 性能:插入表中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！