OPENXML (SQL Server)

OPENXML, ключевое слово Transact-SQL, предоставляет набор строк над XML-документами в памяти, похожими на таблицу или представление. OPENXML позволяет получить доступ к XML-данным, как будто это реляционный набор строк. Это делается путем предоставления представления набора строк внутреннего представления XML-документа. Записи в наборе строк можно хранить в таблицах базы данных.

OPENXML можно использовать в инструкциях SELECT и SELECT INTO там, где поставщики строк, представление или OPENROWSET могут появляться в качестве источника. Сведения о синтаксисе OPENXML см. в разделе OPENXML (Transact-SQL).

Чтобы написать запросы к XML-документу с помощью OPENXML, необходимо сначала вызвать sp_xml_preparedocument. Этот процесс анализирует XML-документ и возвращает дескриптор разобранного документа, который готов к использованию. Проанализированный документ — это представление дерева документной объектной модели (DOM) различных узлов XML-документа. Дескриптор документа передается в OPENXML. Затем OPENXML предоставляет представление набора строк документа на основе параметров, переданных в него.

Внутреннее представление XML-документа должно быть удалено из памяти путем вызова системной хранимой процедуры sp_xml_removedocument для освобождения памяти.

Следующий рисунок иллюстрирует процесс.

Обратите внимание, что для понимания OPENXML требуется знакомство с запросами XPath и понимание XML. Дополнительные сведения о поддержке XPath в SQL Server см. в статье "Использование запросов XPath в SQLXML 4.0".

Пример

В следующем примере показано использование OPENXML в операторе INSERT и операторе SELECT. Пример XML-документа содержит <Customers> и <Orders> элементы.

Во-первых, хранимая sp_xml_preparedocument процедура анализирует XML-документ. Проанализированный документ — это дерево представления узлов (элементов, атрибутов, текста и комментариев) в XML-документе. OPENXML затем ссылается на этот XML-документ и предоставляет представление набора строк для всех или частей этого XML-документа. Инструкция INSERT с использованием OPENXML может вставлять данные из такого набора строк в таблицу базы данных. Несколько OPENXML вызовов можно использовать для предоставления представления набора строк различных частей XML-документа и их обработки, например путем вставки их в разные таблицы. Этот процесс также называется разбиение XML на таблицы.

В следующем примере XML-документ измельчается так, что элементы <Customers> сохраняются в таблице Customers, а элементы <Orders> сохраняются в таблице Orders с использованием двух инструкций INSERT. В примере также показана SELECT, которая с помощью OPENXML извлекает CustomerID и OrderDate из XML-документа. Последним шагом процесса является вызов sp_xml_removedocument. Это делается для освобождения памяти, выделенной для хранения внутреннего представления дерева XML, созданного на этапе синтаксического анализа.

-- Create tables for later population using OPENXML.
CREATE TABLE Customers (CustomerID varchar(20) primary key,
                ContactName varchar(20), 
                CompanyName varchar(20));
GO
CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime);
GO
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(max); -- or xml type
SET @xmlDocument = N'<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>';
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers 
SELECT * 
FROM OPENXML(@docHandle, N'/ROOT/Customers') 
  WITH Customers;
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders 
SELECT * 
FROM OPENXML(@docHandle, N'//Orders') 
  WITH Orders;
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers/Orders') WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime);
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @docHandle; 

На следующем рисунке показано синтаксовое дерево XML предыдущего XML-документа, созданного с помощью sp_xml_preparedocument.

Параметры OPENXML

К параметрам OPENXML относятся следующие параметры:

• Дескриптор XML-документа (idoc)

• Выражение XPath, определяющее узлы, которые необходимо сопоставить с строками (rowpattern)

• Описание создаваемого набора строк

• Сопоставление между столбцами набора строк и узлами XML

Дескриптор XML-документов (idoc)

Дескриптор документа возвращается с помощью хранимой процедуры sp_xml_preparedocument.

Выражение XPath для идентификации узлов для обработки (rowpattern)

Выражение XPath, указанное как rowpattern , определяет набор узлов в XML-документе. Каждый узел, идентифицируемый строкой rowpattern , соответствует одной строке в наборе строк, созданном OPENXML.

Узлы, определяемые выражением XPath, могут быть любым XML-узлом в XML-документе. Если rowpattern идентифицирует набор элементов в XML-документе, для каждого узла элемента определена одна строка в наборе строк. Например, если rowpattern заканчивается атрибутом, для каждого узла атрибута создается строка, выбранная строкой rowpattern.

Описание набора строк для создания

Схема набора строк используется OPENXML для создания результирующего набора строк. При указании схемы набора строк можно использовать следующие параметры.

Использование формата таблицы Edge

Для указания схемы набора строк следует использовать формат таблицы рёбер. Не используйте клаузу WITH.

При этом OPENXML возвращает набор строк в формате граничной таблицы. Это называется граничной таблицей, так как каждый край в дереве синтаксического XML-документа сопоставляется со строкой в наборе строк.

Граничные таблицы представляют в пределах одной таблицы детализированную структуру XML-документа. Эта структура включает имена элементов и атрибутов, иерархию документов, пространства имен и инструкции по обработке. Формат краевой таблицы позволяет получить дополнительные сведения, которые не предоставляются через метасвойства. Дополнительные сведения о метасвойствах см. в разделе "Указание метасвойств" в OPENXML.

Дополнительные сведения, предоставляемые граничной таблицей, позволяют хранить и запрашивать тип данных элемента и атрибута, а также тип узла, а также хранить и запрашивать сведения о структуре XML-документа. С помощью этой дополнительной информации также можно создать собственную систему управления XML-документами.

С помощью пограничной таблицы можно создавать хранимые процедуры, которые принимают XML-документы как входные данные больших двоичных объектов (BLOB), создавать граничную таблицу, а затем извлекать и анализировать документ на более подробном уровне. Этот подробный уровень может включать поиск иерархии документов, имен элементов и атрибутов, пространств имен и инструкций по обработке.

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

В ситуациях, когда для проверки XML-документа можно использовать средство синтаксического анализа XML, вместо этого можно использовать граничную таблицу, чтобы получить те же сведения.

В следующей таблице описывается структура пограничной таблицы.

Использование предложения WITH для указания существующей таблицы

Предложение WITH можно использовать для указания имени существующей таблицы. Для этого просто укажите существующее имя таблицы, схема которой может использоваться OPENXML для создания набора строк.

Использование предложения WITH для указания схемы

Предложение WITH можно использовать для указания полной схемы. При указании схемы набора строк необходимо указать имена столбцов, их типы данных и их сопоставление с XML-документом.

Шаблон столбца можно указать с помощью параметра ColPattern в schemaDeclaration. Указанный шаблон столбца используется для сопоставления столбца набора строк с XML-узлом, который идентифицирован rowpattern, и также используется для определения типа сопоставления.

Если ColPattern не указан для столбца, столбец набора строк сопоставляется с XML-узлом с тем же именем, в зависимости от сопоставления, указанного параметром flags. Однако если ColPattern указан в рамках спецификации схемы в предложении WITH, он перезаписывает сопоставление, указанное в параметре flags.

Сопоставление между столбцами набора строк и узлами XML

В инструкции OPENXML можно при необходимости указать тип сопоставления столбцов набора строк и узлов XML, таких как ориентированные на атрибуты или ориентированные на элементы, которые определяются шаблоном строк. Эти сведения используются в преобразовании между узлами XML и столбцами набора строк.

Сопоставление можно указать двумя способами, и можно указать оба способа:

• С помощью параметра флагов

  Сопоставление, указанное параметром флаги, предполагает соответствие имен, при котором узлы XML соответствуют одноимённым столбцам в наборе строк.

• С помощью параметра ColPattern

  ColPattern, выражение XPath, указывается как часть SchemaDeclaration в предложении WITH. Сопоставление, указанное в ColPattern , перезаписывает сопоставление, указанное параметром флагов .

  ColPattern можно использовать для указания типа отображения, такого как ориентированное на атрибуты или ориентированное на элементы, которое перезаписывает или улучшает отображение по умолчанию, указанное флагами.

  ColPattern указывается в следующих обстоятельствах:

  • Имя столбца в наборе строк отличается от имени элемента или атрибута, с которым сопоставляется. В этом случае ColPattern используется для идентификации XML-элемента и имени атрибута, с которым сопоставляется столбец набора строк.

  • Вы хотите сопоставить атрибут метасвойства со столбцом. В этом случае ColPattern используется для идентификации метапространства, с которым сопоставляется столбец набора строк. Дополнительные сведения об использовании метасвойств см. в статье "Указание метасвойств" в OPENXML.

Флаги и параметры ColPattern являются необязательными. Если сопоставление не задано, предполагается сопоставление, ориентированное на атрибуты. Атрибутоцентрическое сопоставление является значением по умолчанию параметра flags.

Аттрибут-центрическое сопоставление

Установка параметра флагов в OPENXML равным 1 (XML_ATTRIBUTES) указывает на атрибутивное сопоставление. Если флаги содержат XML_ АТРИБУТЫ , предоставленный набор строк предоставляет или использует строки, в которых каждый XML-элемент представлен в виде строки. Атрибуты XML сопоставляются с атрибутами, определенными в schemaDeclaration или предоставленными табличным именем предложения WITH на основе соответствия имени. Сопоставление имен означает, что XML-атрибуты определенного имени хранятся в столбце в наборе строк с тем же именем.

Если имя столбца отличается от имени атрибута, с которым оно сопоставляется, необходимо указать ColPattern .

Если атрибут XML имеет квалификатор пространства имен, имя столбца в наборе строк также должно иметь квалификатор.

Центрированное на элементах сопоставление

Задание параметра flags в OPENXML значением 2 (XML_ELEMENTS) указывает на элементно-ориентированное сопоставление. Он аналогичен атрибутно-ориентированному сопоставлению, за исключением следующих различий:

• Пример сопоставления, в котором столбец соответствует XML-элементу с таким же именем, выбирает простые подэлементы, если не указан шаблон на уровне столбца. В процессе извлечения, если подэлемент является сложным, так как он содержит дополнительные подэлементы, столбец имеет значение NULL. Затем значения атрибутов подэлементов игнорируются.

• Для нескольких подэлементов с одинаковым именем возвращается первый узел.

См. также

sp_xml_preparedocument (Transact-SQL)sp_xml_removedocument (Transact-SQL)OPENXML (Transact-SQL)XML-данные (SQL Server)