5.3 Загрузка данных в формате XML в базу данных SQL Server

Загрузка данных в формате XML на SQL Server, хранимая процедура sp_xml_preparedocument, функция OPENXML, XML for SQL Tools (SQLXML 3.0), объект SQLXMLBulkLoad, загрузка данных в формате XML на SQL Server из скрипта VBScript

5.3.1 Общая информация

Как правило, если у вас реализован обмен данными в формате XML, то необходимо не только выгружать данные в документы XML из баз SQL Server, но и загружать их обратно. Общая схема выглядит так:

1) Получаем содержимое документа XML. Это делается средствами приложения, выполняющего загрузку. Самый простой способ - написать скрипт WSH, например:

Dim oFSO, oTxtStream, sXmlText

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oTxtStream = oFSO.OpenTextFile("C:\import.xml", 1, False, -1)

     sXmlText = oTxtStream.ReadAll()

Подробнее о том, как работать с объектом FSO - в курсе по скриптингу. Мы получили код XML в текстовую переменную sXmlText и можем его использовать дальше. Для простоты мы будем в примерах присваивать код XML переменной прямо в коде TSQL. Обычно весь дальнейший код TSQL упаковывается в хранимую процедуру (например, myProcImportXml) и код XML передается ей в качестве входящего параметра. Саму хранимую процедуру можно запустить из того же скрипта при помощи объектной библиотеки ADO или SQL-DMO (см. следующий модуль), например, так:

Set oServer = CreateObject("SQLDmo.SqlServer") 

oServer.Name = "london1"

oServer.LoginSecure = True

oServer.Connect

Set oDB = oServer.Databases("Northwind")

oDB.ExecuteImmediate("exec myProcImportXml " & " " & sXmlText)

2) далее - уже в теле оболочечной хранимой процедуры myProcImportXml генерируем внутреннее иерархическое представление документа XML при помощи системной хранимой процедуры sp_xml_preparedocument

3) далее в той же myProcImportXml используем функцию OPENXML для генерации в оперативной памяти ROWSET - табличного набора записей из файла XML. При помощи команд XPath (специальный язык для навигации по документам XML) можно дополнительно фильтровать то, что попадет в ROWSET.

4) далее работаем с созданным ROWSET как с обычной таблицей (только временной). Чаще всего данные оттуда при помощи INSERT вставляются в существующие таблицы на сервере или при помощи SELECT INTO - в автоматически создаваемые таблицы.

5) удаляем из оперативной памяти иерархическое представление документа XML при помощи системной хранимой процедуры sp_xml_removedocument. Внимание! Эта хранимая процедура должна идти в одном пакете с sp_xml_preparedocument, иначе ссылка на иерархическую структуру через локальную переменную теряется и эта структура так и останется в памяти до разрыва соединения.

Однако на практике такую схему использовать практически невозможно - из-за ограничений, которые налагаются типами данных для переменных в TSQL. В примерах Microsoft используются переменные типа Varchar с максимальной длиной 8000 байт. Конечно же, на практике большинство документов XML будет больше 8 Кбайт. Поэтому рекомендуется вместо средств TSQL использовать программные объекты, например, входящие в набор XML for SQL Tools (SQLXML 3.0) - о них будет рассказано ниже. После их установки у вас, в частности, появляется возможность использования программного объекта SQLXMLBulkLoad, который очень быстро и удобно грузит данные в формате XML на сервер. Соответствующий код скрипта может выглядеть так:

Dim objBL 

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")

objBL.ConnectionString = "provider=SQLOLEDB;data _ source=localhost;database=northwind;integrated security=SSPI"

objBL.ErrorLogFile = "c:\error.log"

objBL.SchemaGen = True 'создать необходимые таблицы

objBL.SGDropTables = True 'удалить старые, если совпадают имена

'сама загрузка данных

objBL.Execute "d:\Schema.xml", "d:\customers.xml"

Set objBL = Nothing

'поскольку скрипт молчаливый, сигнализируем о завершении

WScript.Echo "Done"

Получить учебные материалы по этому курсу