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