Когда нужно быстро загрузить XML из внешнего файла и не хочется делать через SSIS, очень кстати будет следующий скрипт.
Допустим есть внешний файл следующей структуры.
1. Загружаем данные из файла через OPENROWSET и BULK
2. Подготавливаем документ к обработке sp_xml_preparedocument
3. Разбираем файл инструкцией OPENXML
3. Освобождаем память, удаляем документ sp_xml_removedocument
DECLARE @doc AS XML
SELECT @doc = P
FROM OPENROWSET (BULK 'C:\SHARED\SAMPLEXML.XML',SINGLE_BLOB) AS TEST(P)
DECLARE @hdoc int;
--Процедура sp_xml_preparedocument возвращает дескриптор, по которому можно обратиться к вновь созданному внутреннему представлению XML-документа.
--Считывает входной XML-текст, проводит его синтаксический анализ при помощи средства синтаксического анализатора MSXML (Msxmlsql.dll) и выдает проанализированный документ, готовый к потреблению.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc, '<ROOT xmlns:xyz="urn:MyNamespace"/>';
select @doc
--OPENXML Параметры:
--idoc -Дескриптор документа внутреннего представления XML-документа.
--rowpattern - Шаблон XPath, используемый для идентификации узлов
--flags -Указывает на сопоставление, которое должно использоваться между XML-данными и реляционным набором строк
-- 0 по атрибутам; 1 по атрибутам затем по элементам; 2 по элементам затем по атрибутам;
SELECT *
FROM OPENXML(@hdoc,'/SampleXML/row',2)
WITH
(TITLE NVARCHAR(100) '../@TITLE', --.. путь к вышестоящему элементу ../../ два элемента наверх
GROUPNAME INT '@GROUP', -- сопоставление с атрибутом задается @
ID INT,
NAME NVARCHAR(100)
)
--Удаляет встроенное представление XML-документа, заданного дескриптором документа и делает недействительным дескриптор документа.
EXEC sp_xml_removedocument @hdoc
