OPENXML (Transact-SQL)

Применимо к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

OPENXML предоставляет представление набора строк XML-документа. Так как OPENXML является поставщиком наборов строк, он может применяться в инструкциях языка Transact-SQL, в которых могут быть использованы такие поставщики наборов строк, как таблицы, представления или функция 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 является необязательным входным параметром и может принимать одно из указанных ниже значений.

SchemaDeclaration

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

• ColName

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

• ColType

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

• ColPattern

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

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

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

• MetaProperty

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

TableName

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

Замечания

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

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

Примеры

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

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

Значение аргумента flag устанавливается равным 1. Это значение свидетельствует об атрибутивной модели сопоставления. Поэтому, 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 с аргументом flags, установленным в значение 2 и указывающим на элементное сопоставление, значения аргументов CustomerID и ContactName для обоих клиентов в XML-документе возвращаются как NULL, потому что в XML-документе отсутствуют элементы с именами CustomerID или ContactName.

Результирующий набор:

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.

Хотя сопоставление с использованием элементов задано в аргументе flags, сопоставление, заданное в аргументе 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;

См. также

• Примеры. Использование OPENXML