Поделиться через


OPENXML (Transact-SQL)

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

    Байтовое значение Описание

    0

    По умолчанию используется атрибутивная модель сопоставления.

    1

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

    2

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

    8

    Может быть совмещено (логическое OR) с XML_ATTRIBUTES или XML_ELEMENTS. В смысле получения, этот флаг указывает, что используемые данные не должны копироваться в свойство переполнения @mp:xmltext.

  • 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-документа (имена элементов/атрибутов, иерархия документа, пространства имен, и т. д.) в одной таблице.

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

Имя столбца Тип данных Описание

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, пространство имен отсутствует.

datatype

nvarchar

Настоящий тип данных элемента или строки атрибута, иначе равен NULL. Тип данных вычисляется на основе встроенного DTD или встроенной схемы

prev

bigint

Идентификатор XML предыдущего элемента этого же уровня. Принимает значение NULL, если нет прямого предыдущего одноуровневого элемента.

text

ntext

Содержит значение атрибута или содержимое элемента в текстовой форме (или равен NULL, если запись краевой таблицы не требует значения).

Примеры

A. Использование простой инструкции 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
DECLARE @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, потому что элементы <Customers> не содержат вложенных элементов.

Ниже приводится результирующий набор.

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

Б. Указание 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
DECLARE @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">
   <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

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

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

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

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

DECLARE @idoc int
DECLARE @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

Справка и поддержка

Получение помощи по SQL Server 2005