XML в MS SQL Server 2000 и технологиях доступа к данным - XML Bulk Load

Большие объемы данных неэффективно "заливать" в базу чередой последовательных insert'ов. Для этого применяются средства массовой загрузки. Аналогом bcp / BULK INSERT при работе с XML-файлами выступает XML Bulk Load. Он не грузит весь документ целиком в память, а прочитывает его по отдельным узлам, понимая на основе аннотированной схемы, когда запись заканчивается и ее можно отправить SQL Server'у для вставки. Использование аннотированных схем позволяет осуществлять наполнение одновременно нескольких связанных таблиц. XML Bulk Load не есть утилита с графическим интерфейсом, а всего лишь СОМ-компонент, устанавливаемый SQLXML веб-релизами. Массовая закачка XML-документа осуществляется программным путем - достаточно написать VB-скрипт из нескольких строчек. Я надеюсь, что эта идея вас не пугает, потому что если вы дочитали до этого места, значит, вы, скорее, разработчики, нежели пользователи или администраторы. Для работы из-под .NET Framework необходимо импортировать библиотеку типов Microsoft SQLXML Bulkload 3.0 Type Library (...\Program Files\Common Files\System\Ole DB\xblkld3.dll).

Объектная модель XML Bulk Load тривиальна. Она состоит из единственного объекта - SQLXMLBulkLoad с единственным методом Execute, принимающим два параметра: аннотированную схему и сам XML-документ. Аннотированная схема, как всегда, задает правила разноски XML- содержания: в какое поле какой таблицы положить тот или иной элемент или атрибут. Собственно, все.

Вот XML, который требуется перенести в БД:

<Книги_по_XML>
    <Книга Название="The Guru's Guide to SQL Server Stored Procedures, 
	XML, and HTML" ISBN="0201700468" Страниц="576">
        <Порядковый_номер>1</Порядковый_номер>
        <Издательство>Wesley Professional</Издательство>
        <Цена_на_Амазоне>34.99</Цена_на_Амазоне>
        <Дата_выхода>2001-12-21</Дата_выхода>
        <Авторы>
            <Автор Имя="Ken" Фамилия="Henderson" />
            <Автор Имя="Ron" Фамилия="Soukup" />
        </Авторы>
    </Книга>
    <Книга Название="Programming Microsoft SQL Server 2000 With XML 
	(Pro-Developer)" ISBN="0735613699" Страниц="400">
        <Порядковый_номер>2</Порядковый_номер>
        <Издательство>Microsoft Press</Издательство>
        <Цена_на_Амазоне>41.99</Цена_на_Амазоне>
        <Дата_выхода>2001-06-01</Дата_выхода>
        <Авторы>
            <Автор Имя="Graeme" Фамилия="Malcolm" />
        </Авторы>
    </Книга>
...
</Книги_по_XML>

Вот аннотированная схема, которую я для него определил (схема должна находиться в отдельном файле, размещение ее в самом XML-документе не допускается).

<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
xmlns:ms="urn:schemas-microsoft-com:mapping-schema">
	<xs:annotation>
		<xs:appinfo>
			<ms:relationship name="Книга_Авторы" parent="Book" 
			parent-key="BookID" child="Author" child-key="BookID" />
		</xs:appinfo>
	</xs:annotation>
	<xs:element name="Книга" ms:relation="Book">
		<xs:complexType>
			<xs:sequence>
				<xs:element name="Порядковый_номер" 
				ms:field="BookID" ms:datatype="int" />
				<xs:element name="Издательство" 
				ms:field="Publishing" ms:datatype="varchar(50)" />
				<xs:element name="Цена_на_Амазоне" 
				ms:field="Price" ms:datatype="numeric(6,2)" />
				<xs:element name="Дата_выхода" 
				ms:field="IssueDate" ms:datatype="smalldatetime" />
				<xs:element name="Авторы" 
				maxOccurs="1" ms:is-constant="1">
					<xs:complexType>
						<xs:sequence>
							<xs:element name="Автор" minOccurs="0" 
							maxOccurs="unbounded" ms:relation="Author" 
							ms:relationship="Книга_Авторы">
								<xs:complexType>
									<xs:attribute name="Имя" 
									ms:field="FirstName" 
									ms:datatype="nvarchar(50)" />
									<xs:attribute name="Фамилия" 
									ms:field="LastName" 
									ms:datatype="nvarchar(50)" />
								</xs:complexType>
							</xs:element>
						</xs:sequence>
					</xs:complexType>
				</xs:element>
			</xs:sequence>
			<xs:attribute name="Название" ms:field="Title" 
			ms:datatype="nvarchar(200)" />
			<xs:attribute name="ISBN" ms:field="ISBN" 
			ms:datatype="char(10)" />
			<xs:attribute name="Страниц"
			ms:field="NumPages" ms:datatype="smallint" />
		</xs:complexType>
	</xs:element>
</xs:schema>

И вот скрипт, который осуществляет загрузку:

class BulkExample
{
	static void BulkLoad_File_SQLXML()
	{
		SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class bl = 
		new SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();
		bl.ConnectionString = "Provider=SQLOLEDB;...";
		bl.SchemaGen = true;
		bl.SGDropTables = true;
		bl.KeepNulls = true;
		bl.Transaction = true;
		bl.ErrorLogFile = "..\\BulkCopy\\XMLDocForBulkLoad.err";
		bl.Execute("..\\BulkCopy\\XMLDocForBulkLoad.xsd", 
		"..\\BulkCopy\\XMLDocForBulkLoad.xml");
	}

	[STAThread]
	static void Main()
	{
		BulkLoad_File_SQLXML();
	}
}

Следует обратить внимание на атрибут [STAThread], поскольку компонент пока работает только в однопоточном режиме. Свойство SchemaGen, определяет, должны ли таблицы под загрузку создаваться или вставка идет в уже имеющиеся. Чтобы просто создать структуры и не переносить при этом сами данные, нужно еще поставить BulkLoad = false. Имена таблиц и полей, их типы и прочие метаданные определяются аннотированной схемой (ms:relation, ms:field, ms:datatype).

Если таблицы уже существуют, то SGDropTables = true заставляет их пересоздаться. Удаление таблиц происходит в том порядке, в котором они упоминаются в XML-файле и может приводить к конфликту с ограничениями primary key/foreign key.
Рассмотренный в п.11 ms:inverse при этом не помогает. В том случае, если таблица есть и для поля определено значение по умолчанию, оно будет использоваться, когда в XML-файле соответствующий элемент или атрибут пропущен, но если поставить KeepNulls = true, то значение по умолчанию будет проигнорировано и в поле будет поставлен Null. Аналогично действует свойство KeepIdentity. Если вставка идет в поле типа identity, то false заставляет игнорировать значения для этого поля в XMLном файле и использовать автоинкремент, определенный для него на сервере. CheckConstraints определяет, будут ли проверяться constraints (primary key/foreign key и пр.) при загрузке данных. Свойство Transaction заставляет все действия по загрузке проходить в масштабе единой транзакции, так что ежели что случится, то все будет откачено. Его нельзя использовать при загрузке BLOBов. ForceTableLock устанавливает табличную блокировку на таблицы, задействованные в ходе bulk load. Если в аннотированной схеме поле помечено атрибутом dt:type="id" (xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"), то свойство SGUseID = true приведет к тому, что при создании таблицы на него будет создано ограничение primary key.

Если в схеме в <xs:element name<font color=blue>="Книга" ms:relation="Book"> поставить атрибут, ms:key-fields="BookID", то между таблицами Book и Author будет создано ограничение primary key/foreign key по полю BookID. Независимо от этого указание в схеме отношения между таблицами <ms:relationship name="Книга_Авторы" parent="Book" parent-key="BookID" child="Author" child-key="BookID" /> обеспечивает во время массовой загрузки автоматическое заполнение поля BookID в дочерней таблице Author значениями BookID из родительской записи таблицы Book, как происходит в нашем случае:

Методу Execute можно передавать не только название XML-документа, но и ADODB.Stream:

...

StreamReader sr = File.OpenText("..\\BulkCopy\\XMLDocForBulkLoad.xml");
ADODB.StreamClass sc = new ADODB.StreamClass();
sc.Charset = "UTF-8"; 
sc.Open(System.Type.Missing, ADODB.ConnectModeEnum.adModeUnknown, 
ADODB.StreamOpenOptionsEnum.adOpenStreamUnspecified, "", "");
sc.WriteText(sr.ReadToEnd(), ADODB.StreamWriteEnum.stWriteChar);
sc.Position = 0;
bl.Execute("..\\BulkCopy\\XMLDocForBulkLoad.xsd", sc);
sr.Close(); sc.Close();

что позволяет загружать в БД динамически сгенерированный внутри кода XML без необходимости его промежуточного сохранения. Для загрузки XML-фрагмента (набора элементов без корневого, не являющегося каноническим well-formed документом) необходимо использовать свойство XMLFragment.