Использование PowerShell для загрузки XML-данных в SQL Server

.

Чтобы разобраться в задаче, рассмотрим таблицу с именем Products, которую предстоит загрузить в T-SQL с использованием Xquery. В листинге 1 показана DDL-схема таблицы.

XML-файл источника соответствует таблице; данные в столбцах определены как элементы, а вид данных показан на рисунке 1.

Рисунок 1. Вводимые данные

Теперь, используя T-SQL, нужно определить XML-переменную, загрузить ее из исходного файла, а затем использовать Xquery для синтаксического разбора и загрузки содержимого файла в таблицу Products. Программный код T-SQL приведен в листинге 2.

Используя установку SET STATISTICS TIME ON, можно заметить, что если XML-данные составляют 1000 строк, то следует сначала загрузить файл в XML-переменную, а затем выполнить синтаксический разбор и загрузить XML-данные в таблицу Products. Результаты показаны на рисунке 2.

Рисунок 2. Выполнение разбора

Неплохо, но число строк — всего 1000. Для сравнения рассмотрим тот же процесс в PowerShell.

Для начала нам необходим файл XML-схемы, чтобы определить типы данных для каждого столбца. Если не определить схему, все столбцы будут определены как строковые значения, и вам не удастся загрузить данные в таблицу. На рисунке 3 приведено содержимое файла XML-схемы для файла Products.xml.

Рисунок 3. Содержимое файла XML-схемы для файла Products.xml

На первый взгляд, все это сложно, но можно переложить основную часть работы на PowerShell, чтобы сформировать файл схемы. Начните с подключения к SQL Server и определения запроса к таблице Products, который не принесет результатов, см. листинг 3.

Затем заполните объект DataSet с использованием запроса и задействуйте метод WriteXmlSchema() для записи файла.

$ds = New-Object System.Data.DataSet
$da.Fill($ds, «Products») | Out-Null
$ds.WriteXmlSchema('C:\Work\ProdSchema.xsd')

Необходимо изменить xsd-файл в соответствии со структурой XML в файле данных. Сформированный xsd-файл имеет идентификатор схемы и значение элемента верхнего уровня «NewDataSet». В данном случае его нужно заменить на «Products». Затем измените имя элемента «Products» на имя «row», чтобы обеспечить работоспособность сформированной схемы. Все определения столбцов будут верными.

Используйте тот же метод для массовой загрузки данных в таблицу способом, описанным в статье «Bulk Copy Data into SQL Server with PowerShell» (sqlmag.com/powershell/bulk-copy-data-sql-server-powershell). Для начала необходимо иметь данные в ADO.NET DataTable. Это удобно, так как объект DataSet, содержащий DataTable, располагает двумя полезными методами, ReadXML() и ReadXMLSchema().

Далее следует создать объект DataSet и применить метод ReadXMLSchema(), чтобы загрузить схему в DataSet.

$ds = new-object «System.Data.DataSet» «dsServers»
$ds.ReadXmlSchema('C:\Work\ProdSchema.xsd')

Затем можно использовать метод ReadXML() для загрузки XML-данных в DataSet. Обратите внимание, что поскольку XML-схема уже загружена, процесс ReadXML(), применяемый для определения схемы, игнорируется. Затем единственная таблица в DataSet загружается в переменную $dtProd.

$ds.ReadXml(«C:\Work\Products1000.XML»)
$dtProd = $ds.Tables[0]

Располагая DataTable с данными Product, можно задействовать метод SqlBulkCopy для быстрой загрузки данных в таблицу, см. листинг 4.

Необходимо измерить время загрузки данных. Это можно сделать с помощью команды Measure-Command в PowerShell. Для упрощения процесса предшествующий программный код PowerShell помещен в сценарий с именем XmlData.ps1 и выполнена следующая команда:

Measure-Command {C:\Work\Load-XmlData.ps1}

Полученные результаты показаны на рисунке 4.

Рисунок 4. Результаты работы Measure-Command

Мы видим, что общее время загрузки XML-файла с 1000 строками с использованием T-SQL составило 182 мс (11 мс для загрузки файла, 171 мс для синтаксического разбора и загрузки таблицы), а общее время выполнения сценария для загрузки XML-файла в DataSet с последующим массовым копированием данных в SQL Server составило 63 мс, приблизительно одну треть времени для достижения того же результата.

После увеличения размера XML-файла с 1000 строк до 10 000 строк были получены временные показатели для T-SQL, как на рисунке 5.

Рисунок 5. Время исполнения запроса на 10?000 строк

Временные показатели при обработке тех же данных в сценарии PowerShell с использованием Measure-Command показаны на рисунке 6.

Рисунок 6. Measure-Command для?10?000 строк

Таким образом, общее время выполнения решения T-SQL составило 2503 мс, а решение PowerShell выполнялось 743 мс, то есть более чем в три с половиной раза быстрее.

Это наглядно свидетельствует о том, что PowerShell больше подходит для загрузки объемных наборов данных в SQL Server из XML, нежели T-SQL.

CREATE TABLE [dbo].[Products](
[ProductID] [int] NOT NULL,
[ProductName] [nvarchar](40) NOT NULL,
[SupplierID] [int] NULL,
[CategoryID] [int] NULL,
[QuantityPerUnit] [nvarchar](20) NULL,
[UnitPrice] [money] NULL,
[UnitsInStock] [smallint] NULL,
[UnitsOnOrder] [smallint] NULL,
[ReorderLevel] [smallint] NULL,
[Discontinued] [bit] NOT NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)
)
GO

DECLARE @messagebody XML
SELECT @messagebody = BulkColumn
FROM OPENROWSET(BULK 'C:\Work\Products1000.XML', SINGLE_CLOB) AS X
INSERT INTO [dbo].[Products]
select a.value(N'(./ProductID)[1]', N'int') as [ProductID],
a.value(N'(./ProductName)[1]', N'nvarchar(40)') as [ProductName],
a.value(N'(./SupplierID)[1]', N'int') as [SupplierID],
a.value(N'(./CategoryID)[1]', N'int') as [CategoryID],
a.value(N'(./QuantityPerUnit)[1]', N'nvarchar(20)') as [QuantityPerUnit],
a.value(N'(./UnitPrice)[1]', N'money') as [UnitPrice],
a.value(N'(./UnitsInStock)[1]', N'smallint') as [UnitsInStock],
a.value(N'(./UnitsOnOrder)[1]', N'smallint') as [UnitsOnOrder],
a.value(N'(./ReorderLevel)[1]', N'smallint') as [ReorderLevel],
a.value(N'(./Discontinued)[1]', N'bit') as [Discontinued]
from @messagebody.nodes('/Products/row') as r(a);

$cn = new-object System.Data.SqlClient.SqlConnection(«Data Source=WS12SQL;Integrated Security=SSPI;Initial Catalog=TestDB»);
$cn.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = «SELECT * FROM dbo.Products WHERE 1 = 2»
$cmd.Connection = $cn
$da = New-Object System.Data.SqlClient.SqlDataAdapter
$da.SelectCommand = $cmd

$cn = new-object System.Data.SqlClient.SqlConnection(«Data Source=WS12SQL;Integrated Security=SSPI;Initial Catalog=TestDB»);
$cn.Open()
$bc = new-object («System.Data.SqlClient.SqlBulkCopy») $cn
$bc.DestinationTableName = «dbo.Products»
$bc.WriteToServer($dtProd)
$cn.Close()