Производительность XML-процесса SQL: вставка в столбцы таблицы

У меня возникла проблема в процедуре SQL, и я не могу найти подходящее решение. Хранимая процедура содержит один параметр типа данных XML (name = @data).

Примером входящего сообщения является следующее (фактическое сообщение содержит намного больше узлов, но я оставил их для простоты):

<suppliers xmlns="">
 <supplier>
 <idcono>3</idcono>
 <idsuno>009999</idsuno>
 <idsuty>0</idsuty>
 </supplier>
</suppliers>

В моей базе данных SQL у меня есть таблица под названием "Поставщик" и она содержит те же столбцы, что и узлы в XML (IDCONO, IDSUNO, IDSUTY,..)

Мне нужно перебрать узлы и вставить данные в столбцы. Я выполнил описанную ниже процедуру, но это дает мне много проблем с производительностью в больших файлах (длительное время обработки, даже таймауты):

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-сервер 2012.

Спасибо заранее.

1 ответ

Первое, что я попробую, это указать text() node при использовании типа данных XML, чтобы SQL Server не выполнял глубокий поиск текстовых элементов.

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.

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

licensed under cc by-sa 3.0 with attribution.