OPENXML (Transact-SQL)OPENXML (Transact-SQL)

ОБЛАСТЬ ПРИМЕНЕНИЯ:даSQL Server (начиная с 2008)даБаза данных SQL AzureдаХранилище данных SQL AzureнетParallel Data WarehouseAPPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

OPENXML предоставляет представление набора строк XML-документа.OPENXML provides a rowset view over an XML document. Так как OPENXML является поставщиком наборов строк, он может применяться в инструкциях языка Transact-SQLTransact-SQL, в которых могут быть использованы такие поставщики наборов строк, как таблицы, представления или функция OPENROWSET.Because OPENXML is a rowset provider, OPENXML can be used in Transact-SQLTransact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear.

Значок ссылки на статью Синтаксические обозначения в Transact-SQLArticle link icon Transact-SQL Syntax Conventions

СинтаксисSyntax

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

АргументыArguments

idocidoc
Дескриптор документа внутреннего представления XML-документа.Is the document handle of the internal representation of an XML document. Внутреннее представление XML-документа создается путем вызова процедуры sp_xml_preparedocument.The internal representation of an XML document is created by calling sp_xml_preparedocument.

rowpatternrowpattern
Шаблон XPath, используемый для указания узлов, которые будут обработаны в качестве строк.Is the XPath pattern used to identify the nodes to be processed as rows. Узлы поступают из XML-документа, дескриптор которого передается в параметре idoc.The nodes come from the XML document whose handle is passed in the idoc parameter.

flagsflags
Указывает на сопоставление, используемое между XML-данными и реляционным набором строк, а также на порядок заполнения переполненного столбца.Indicates the mapping used between the XML data and the relational rowset, and how the spill-over column is filled. Аргумент flags является необязательным входным параметром и может принимать одно из указанных ниже значений.flags is an optional input parameter, and can be one of the following values.

Байтовое значениеByte value ОписаниеDescription
00 По умолчанию используется атрибутивная модель сопоставления.Defaults to attribute-centric mapping.
11 Использовать атрибутивную модель сопоставления.Use the attribute-centric mapping. Может быть совмещено с XML_ELEMENTS.Can be combined with XML_ELEMENTS. В этом случае сначала применяется атрибутивная модель сопоставления.In this case, attribute-centric mapping is applied first. Затем для всех остальных столбцов применяется элементная модель сопоставления.Next, element-centric mapping is applied for any remaining columns.
22 Использовать сопоставление с использованием элементов.Use the element-centric mapping. Может быть совмещено с XML_ATTRIBUTES.Can be combined with XML_ATTRIBUTES. В этом случае сначала применяется атрибутивная модель сопоставления.In this case, attribute-centric mapping is applied first. Затем для всех остальных столбцов применяется элементная модель сопоставления.Next, element-centric mapping is applied for any remaining columns.
88 Может быть совмещено (логическое OR) с XML_ATTRIBUTES или XML_ELEMENTS.Can be combined (logical OR) with XML_ATTRIBUTES or XML_ELEMENTS. В контексте получения этот флаг указывает, что используемые данные не должны копироваться в свойство переполнения @mp:xmltext.In the context of retrieval, this flag indicates that the consumed data shouldn't be copied to the overflow property @mp:xmltext.

SchemaDeclarationSchemaDeclaration
Определение схемы формы: ColNameColType [ColPattern | MetaProperty] [,ColNameColType [ColPattern | MetaProperty]...]Is the schema definition of the form: ColNameColType [ColPattern | MetaProperty] [,ColNameColType [ColPattern | MetaProperty]...]

ColNameColName
Название столбца в наборе строк.Is the column name in the rowset.

ColTypeColType
Тип данных SQL ServerSQL Server столбца в наборе строк.Is the SQL ServerSQL Server data type of the column in the rowset. Если типы столбцов отличаются от базовых типов данных xml атрибута, происходит приведение типов.If the column types differ from the underlying xml data type of the attribute, type coercion occurs.

ColPatternColPattern
Необязательный общий шаблон XPath, который описывает, как узлы XML должны быть сопоставлены столбцам.Is an optional, general XPath pattern that describes how the XML nodes should be mapped to the columns. Если аргумент ColPattern не указан, применяется сопоставление по умолчанию (атрибутивная или элементная модель сопоставления, как указано в аргументе flags).If ColPattern isn't specified, the default mapping (attribute-centric or element-centric mapping as specified by flags) takes place.

Шаблон XPath, заданный как ColPattern, используется для указания специального порядка сопоставления (для атрибутивной и элементной моделей сопоставления), которое переопределяет или расширяет сопоставление по умолчанию, указанное в аргументе flags.The XPath pattern specified as ColPattern is used to specify the special nature of the mapping (for attribute-centric and element-centric mapping) that overwrites or enhances the default mapping indicated by flags.

Общий шаблон XPath, заданный как аргумент ColPattern, также поддерживает метасвойства.The general XPath pattern specified as ColPattern also supports the metaproperties.

MetaPropertyMetaProperty
Одно из метасвойств, предоставляемых OPENXML.Is one of the metaproperties provided by OPENXML. Если задано свойство MetaProperty, столбец содержит сведения, предоставленные метасвойством.If MetaProperty is specified, the column contains information provided by the metaproperty. Метасвойства позволяют извлекать сведения (такие как относительное положение и сведения о пространстве имен) об узлах XML.The metaproperties allow you to extract information (such as relative position and namespace information) about XML nodes. По сравнению с текстовым представлением эти метасвойства позволяют увидеть больше сведений.These metaproperties provide more information than is visible in the textual representation.

TableNameTableName
Имя таблицы, которое может быть указано (вместо аргумента SchemaDeclaration), если таблица с необходимой схемой уже существует и не требует никаких шаблонов столбцов.Is the table name that can be given (instead of SchemaDeclaration) if a table with the desired schema already exists and no column patterns are required.

RemarksRemarks

Предложение WITH предоставляет формат набора строк (и дополнительные сведения о сопоставлении, если необходимо), используя либо аргумент SchemaDeclaration, либо указывая существующее значение аргумента TableName.The WITH clause provides a rowset format (and additional mapping information as required) by using either SchemaDeclaration or specifying an existing TableName. Если необязательное предложение WITH не задано, результаты возвращаются в формате граничной таблицы.If the optional WITH clause isn't specified, the results are returned in an edge table format. Краевые таблицы представляют собой структуру мелкогранулированного XML-документа (имена элементов/атрибутов, иерархия документа, пространства имен, и т. д.) в одной таблице.Edge tables represent the fine-grained XML document structure (such as element/attribute names, the document hierarchy, the namespaces, PIs, and son on) in a single table.

В представленной ниже таблице описывается структура граничной таблицы.The following table describes the structure of the edge table.

Имя столбцаColumn name Тип данныхData type ОписаниеDescription
идентификаторid bigintbigint Уникальный идентификатор узла документов.Is the unique ID of the document node.

Корневой элемент имеет значение 0.The root element has an ID value 0. Отрицательные значения идентификаторов зарезервированы.The negative ID values are reserved.
parentidparentid bigintbigint Идентифицирует родителя узла.Identifies the parent of the node. Родитель, идентифицируемый данным идентификатором, не обязательно является родительским элементом, это зависит от значения NodeType узла, родитель которого идентифицируется этим идентификатором.The parent identified by this ID isn't necessarily the parent element, but it depends on the NodeType of the node whose parent is identified by this ID. Например, если узел является текстовым, его родительский узел может быть атрибутным узлом.For example, if the node is a text node, the parent of it may be an attribute node.

Если узел находится на верхнем уровне XML-документа, то его столбец ParentID принимает значение NULL.If the node is at the top level in the XML document, its ParentID is NULL.
nodetypenodetype intint Идентифицирует тип узла.Identifies the node type. Целое число, которое соответствует нумерации типов узлов XML DOM.Is an integer that corresponds to the XML DOM node type numbering.

Типы узлов бывают:The node types are:

1 = Элементный узел1 = Element node

2 = Атрибутный узел2 = Attribute node

3 = Текстовый узел3 = Text node
localnamelocalname nvarcharnvarchar Задает локальное имя элемента или атрибута.Gives the local name of the element or attribute. Принимает значение NULL, если у объекта DOM нет имени.Is NULL if the DOM object doesn't have a name.
prefixprefix nvarcharnvarchar Префикс пространства имен для имени узла.Is the namespace prefix of the node name.
namespaceurinamespaceuri nvarcharnvarchar URI пространства имен для имени узла.Is the namespace URI of the node. Если значение равно NULL, пространство имен отсутствует.If the value is NULL, no namespace is present.
datatypedatatype nvarcharnvarchar Настоящий тип данных элемента или строки атрибута, иначе равен NULL.Is the actual data type of the element or attribute row, otherwise is NULL. Тип данных вычисляется на основе встроенного DTD или встроенной схемыThe data type is inferred from the inline DTD or from the inline schema.
prevprev bigintbigint Идентификатор XML предыдущего элемента этого же уровня.Is the XML ID of the previous sibling element. Принимает значение NULL, если нет прямого предыдущего одноуровневого элемента.Is NULL if there is no direct previous sibling.
texttext ntextntext Содержит значение атрибута или содержимое элемента в текстовой форме (или равен NULL, если запись граничной таблицы не требует значения).Contains the attribute value or the element content in text form (or is NULL if the edge table entry doesn't require a value).

ПримерыExamples

A.A. Использование простой инструкции SELECT с OPENXMLUsing a simple SELECT statement with OPENXML

В следующем примере создается внутреннее представление образа XML с помощью процедуры sp_xml_preparedocument.The following example creates an internal representation of the XML image by using sp_xml_preparedocument. Инструкция SELECT, которая использует поставщик набора строк OPENXML, выполняется для внутреннего представления XML-документа.A SELECT statement that uses an OPENXML rowset provider is then executed against the internal representation of the XML document.

Значение аргумента flag устанавливается равным 1.The flag value is set to 1. Это значение свидетельствует об атрибутивной модели сопоставления.This value indicates attribute-centric mapping. Поэтому, XML-атрибуты сопоставляются столбцам набора строк.Therefore, the XML attributes map to the columns in the rowset. Аргумент rowpattern, заданный как /ROOT/Customer, указывает, какие узлы <Customers> должны обрабатываться.The rowpattern specified as /ROOT/Customer identifies the <Customers> nodes to be processed.

Необязательный аргумент ColPattern (шаблон столбцов) не задан, так как имя столбца совпадает с именами XML-атрибутов.The optional ColPattern (column pattern) parameter isn't specified because the column name matches the XML attribute names.

Поставщик набора строк OPENXML создает набор строк с двумя столбцами (CustomerID и ContactName), из которых инструкция SELECT извлекает необходимые столбцы (в данном случае, все столбцы).The OPENXML rowset provider creates a two-column rowset (CustomerID and ContactName) from which the SELECT statement retrieves the necessary columns (in this case, all the columns).

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));  
  

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

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

Если выполняется та же инструкция SELECT с аргументом flags, установленным в значение 2 и указывающим на элементное сопоставление, значения аргументов CustomerID и ContactName для обоих клиентов в XML-документе возвращаются как NULL, потому что в XML-документе отсутствуют элементы с именами CustomerID или ContactName.If the same SELECT statement is executed with flags set to 2, indicating element-centric mapping, the values of CustomerID and ContactName for both of the customers in the XML document are returned as NULL, because there aren't any elements named CustomerID or ContactName in the XML document.

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

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

Б.B. Указание ColPattern для сопоставления столбцов XML-атрибутамSpecifying ColPattern for mapping between columns and the XML attributes

Следующий запрос возвращает идентификатор клиента, дату заказа, идентификатор продукта и количественные атрибуты XML-документа.The following query returns customer ID, order date, product ID, and quantity attributes from the XML document. Аргумент rowpattern идентифицирует элементы <OrderDetails>.The rowpattern identifies the <OrderDetails> elements. Аргументы ProductID и Quantity являются атрибутами элемента <OrderDetails>.ProductID and Quantity are the attributes of the <OrderDetails> element. Однако OrderID, CustomerID и OrderDate являются атрибутами родительского элемента (<Orders>).However, OrderID, CustomerID, and OrderDate are the attributes of the parent element (<Orders>).

Дополнительный атрибут ColPattern указывается для следующих сопоставлений:The optional ColPattern is specified for the following mappings:

  • OrderID, CustomerID и OrderDate в наборе строк сопоставляются с атрибутами родительского узла узлов, заданных в аргументе rowpattern в XML-документе.The OrderID, CustomerID, and OrderDate in the rowset map to the attributes of the parent of the nodes identified by rowpattern in the XML document.

  • Столбец ProdID в наборе строк сопоставляется с атрибутом ProductID, а столбец Qty в наборе строк сопоставляется с атрибутом Quantity узлов, заданных в аргументе rowpattern.The ProdID column in the rowset maps to the ProductID attribute, and the Qty column in the rowset maps to the Quantity attribute of the nodes identified in rowpattern.

Хотя сопоставление с использованием элементов задано в аргументе flags, сопоставление, заданное в аргументе ColPattern, переопределяет это сопоставление.Although the element-centric mapping is specified by the flags parameter, the mapping specified in ColPattern overwrites this mapping.

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');  
  

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

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. Получение результатов в формате краевой таблицыObtaining results in an edge table format

Образец XML-документа в следующем примере состоит из элементов <Customers>, <Orders> и <Order_0020_Details>.The sample XML document in the following example consists of <Customers>, <Orders>, and <Order_0020_Details> elements. Сначала вызывается хранимая процедура sp_xml_preparedocument, чтобы получить дескриптор документа.First, sp_xml_preparedocument is called to obtain a document handle. Дескриптор документа передается OPENXML.This document handle is passed to OPENXML.

В инструкции OPENXML rowpattern (/ROOT/Customers) задает узлы <Customers> для обработки.In the OPENXML statement, the rowpattern (/ROOT/Customers) identifies the <Customers> nodes to process. Так как предложение WITH не предоставлено, OPENXML возвращает набор строк в формате граничной таблицы.Because the WITH clause isn't provided, OPENXML returns the rowset in an edge table format.

Наконец, инструкция SELECT извлекает все столбцы в граничной таблице.Finally the SELECT statement retrieves all the columns in the edge table.

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;  
  

См. также:See Also

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