OPENXML (Transact-SQL)

  • Статья

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

OPENXML предоставляет представление набора строк по XML-документу. Так как OPENXML это поставщик набора строк, можно использовать в инструкциях Transact-SQL, OPENXML в которых могут отображаться поставщики наборов строк, такие как таблица, представление или OPENROWSET функция.

Соглашения о синтаксисе Transact-SQL

Синтаксис

OPENXML ( idoc int [ in ]
    , rowpattern nvarchar [ in ]
    , [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]

Аргументы

idoc

Дескриптор документа внутреннего представления XML-документа. Внутреннее представление XML-документа создается путем вызова sp_xml_preparedocument.

rowpattern

Шаблон XPath, используемый для идентификации узлов, обрабатываемых как строки. Узлы поступают из XML-документа, дескриптор которого передается в параметре idoc.

flags

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

Байтовое значение Description
0 По умолчанию сопоставляется attribute-centric .
1 attribute-centric Используйте сопоставление. Может использоваться вместе с XML_ELEMENTS. В этом случае attribute-centric сначала применяется сопоставление. element-centric Далее сопоставление применяется для всех оставшихся столбцов.
2 element-centric Используйте сопоставление. Может использоваться вместе с XML_ATTRIBUTES. В этом случае element-centric сначала применяется сопоставление. attribute-centric Далее сопоставление применяется для всех оставшихся столбцов.
8 Можно объединить (логический ИЛИ) с XML_ATTRIBUTES или XML_ELEMENTS. В контексте извлечения этот флаг указывает, что используемые данные не должны быть скопированы в свойство @mp:xmltextпереполнения.

SchemaDeclaration

Определение схемы формы: ColNameColType [ ColPattern MetaProperty] [ , ColNameColType [ ColPattern | | MetaProperty ] ...

  • ColName

    Имя столбца в наборе строк.

  • ColType

    Тип данных SQL Server столбца в наборе строк. Если типы столбцов отличаются от базовых типов данных xml атрибута, происходит приведение типов.

  • ColPattern

    Необязательный шаблон XPath, описывающий, как xml-узлы должны сопоставляться со столбцами. Если ColPattern не указан, выполняется сопоставление по умолчанию (attribute-centric или element-centric сопоставление, указанное флагами).

    Шаблон XPath, указанный как ColPattern , используется для указания специального характера сопоставления (для attribute-centric и element-centric сопоставления), который перезаписывает или улучшает сопоставление по умолчанию, указанное флагами.

    Общий шаблон XPath, заданный как аргумент ColPattern, также поддерживает метасвойства.

  • MetaProperty

    Одно из метапродастей, предоставляемых OPENXML. Если задано свойство MetaProperty, столбец содержит сведения, предоставленные метасвойством. Метасвойства позволяют извлекать сведения (такие как относительное положение и сведения о пространстве имен) об узлах XML. По сравнению с текстовым представлением эти метасвойства позволяют увидеть больше сведений.

TableName

Имя таблицы, которое можно указать (вместо SchemaDeclaration), если таблица с требуемой схемой уже существует, и шаблоны столбцов не требуются.

Замечания

Предложение WITH предоставляет формат набора строк (и дополнительные сведения о сопоставлении по мере необходимости) с помощью schemaDeclaration или указания существующего имени таблицы. Если необязательное WITH предложение не указано, результаты возвращаются в формате пограничной таблицы. Пограничные таблицы представляют структуру XML-документа (например, имена элементов и атрибутов, иерархию документов, пространства имен, PIs и т. д.) в одной таблице.

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

Имя столбца Тип данных Description
id bigint Уникальный идентификатор узла документа.

Корневой элемент имеет значение 0идентификатора. Отрицательные значения идентификаторов зарезервированы.
parentid bigint Идентифицирует родителя узла. Родитель, определяемый этим идентификатором, не обязательно является родительским элементом, но он зависит от nodetype узла, родительский элемент которого определяется этим идентификатором. Например, если узел является текстовым узлом, родительский элемент может быть узлом атрибута.

Если узел находится на верхнем уровне в XML-документе, он имеет значение ParentID NULL.
nodetype int Идентифицирует тип узла. Это значение представляет собой целое число, соответствующее нумерации типа узла XML DOM. Типы узлов бывают:

1 = узел элемента
2 = узел атрибута
3 = текстовый узел
localname nvarchar Задает локальное имя элемента или атрибута. NULL Если у объекта DOM нет имени.
prefix nvarchar Префикс пространства имен имени узла.
namespaceuri nvarchar Универсальный код ресурса (URI) пространства имен узла. Если значение равно NULL, пространство имен отсутствует.
data type nvarchar Фактический тип данных элемента или строки атрибута, в противном случае — NULL. Тип данных вычисляется на основе встроенного DTD или встроенной схемы
prev bigint XML-идентификатор предыдущего элемента-брата. NULL Если нет прямого предыдущего брата.
text ntext Содержит значение атрибута или содержимое элемента в текстовой форме (или NULL если для записи таблицы edge не требуется значение).

Примеры

А. Использование базовой инструкции SELECT с OPENXML

В следующем примере создается внутреннее представление образа XML с помощью процедуры sp_xml_preparedocument. Инструкция SELECT, которая использует поставщик набора строк OPENXML, выполняется для внутреннего представления XML-документа.

Значение аргумента flag устанавливается равным 1. Это значение указывает attribute-centric на сопоставление. Поэтому, XML-атрибуты сопоставляются столбцам набора строк. Аргумент rowpattern, заданный как /ROOT/Customer, указывает, какие узлы <Customers> должны обрабатываться.

Необязательный аргумент ColPattern (шаблон столбцов) не задан, так как имя столбца совпадает с именами XML-атрибутов.

Поставщик набора строк OPENXML создает набор строк с двумя столбцами (CustomerID и ContactName), из которых инструкция SELECT извлекает необходимые столбцы (в данном случае, все столбцы).

DECLARE @idoc INT, @doc VARCHAR(1000);

SET @doc = '
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML(@idoc, '/ROOT/Customer', 1) WITH (
    CustomerID VARCHAR(10),
    ContactName VARCHAR(20)
);

Вот результирующий набор.

CustomerID ContactName
---------- --------------------
VINET      Paul Henriot
LILAS      Carlos Gonzlez

Если одна и та же SELECT инструкция выполняется с флагами2, element-centric указанными в сопоставлении, значения CustomerID ContactName и для обоих клиентов в XML-документе возвращаются какNULL, так как в XML-документе нет именованных CustomerID элементов или ContactName в XML-документе.

Вот результирующий набор.

CustomerID ContactName
---------- -----------
NULL       NULL
NULL       NULL

B. Указание ColPattern для сопоставления между столбцами и XML-атрибутами

Следующий запрос возвращает идентификатор клиента, дату заказа, идентификатор продукта и количественные атрибуты XML-документа. Аргумент rowpattern идентифицирует элементы <OrderDetails>. Аргументы ProductID и Quantity являются атрибутами элемента <OrderDetails>. Однако OrderID, CustomerID и OrderDate являются атрибутами родительского элемента (<Orders>).

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

  • OrderID, CustomerID и OrderDate в наборе строк сопоставляются с атрибутами родительского узла узлов, заданных в аргументе rowpattern в XML-документе.

  • Столбец ProdID в наборе строк сопоставляется с атрибутом ProductID, а столбец Qty в наборе строк сопоставляется с атрибутом Quantity узлов, заданных в аргументе rowpattern.

element-centric Хотя сопоставление указывается параметром флагов, сопоставление, указанное в ColPattern, перезаписывает это сопоставление.

DECLARE @idoc INT, @doc VARCHAR(1000);

SET @doc = '
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
           OrderDate="1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">v
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
           OrderDate="1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML(@idoc, '/ROOT/Customer/Order/OrderDetail', 2) WITH (
    OrderID INT '../@OrderID',
    CustomerID VARCHAR(10) '../@CustomerID',
    OrderDate DATETIME '../@OrderDate',
    ProdID INT '@ProductID',
    Qty INT '@Quantity'
);

Вот результирующий набор.

OrderID CustomerID           OrderDate                 ProdID    Qty
------------------------------------------------------------------------
10248      VINET       1996-07-04 00:00:00.000   11      12
10248      VINET       1996-07-04 00:00:00.000   42      10
10283      LILAS       1996-08-16 00:00:00.000   72      3

C. Получение результатов в формате пограничной таблицы

Образец XML-документа в следующем примере состоит из элементов <Customers>, <Orders> и <Order_0020_Details>. sp_xml_preparedocument Сначала вызывается для получения дескриптора документа. Дескриптор документа передается OPENXML.

В инструкции OPENXMLrowpattern (/ROOT/Customers) задает узлы <Customers> для обработки. WITH Так как предложение не указано, OPENXML возвращает набор строк в формате пограничной таблицы.

Наконец, инструкция SELECT извлекает все столбцы в граничной таблице.

DECLARE @idoc INT, @doc VARCHAR(1000);

SET @doc = '
<ROOT>
<Customers CustomerID="VINET" ContactName="Paul Henriot">
   <Orders CustomerID="VINET" EmployeeID="5" OrderDate=
           "1996-07-04T00:00:00">
      <Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>
      <Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>
   </Orders>
</Customers>
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Orders CustomerID="LILAS" EmployeeID="3" OrderDate=
           "1996-08-16T00:00:00">
      <Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>
   </Orders>
</Customers>
</ROOT>';

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

-- SELECT statement that uses the OPENXML rowset provider.
SELECT * FROM OPENXML(@idoc, '/ROOT/Customers')

EXEC sp_xml_removedocument @idoc;

Связанный контент