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 ) ]
Примечание.
Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
idoc
Дескриптор документа внутреннего представления XML-документа. Внутреннее представление XML-документа создается путем вызова sp_xml_preparedocument
.
rowpattern
Шаблон XPath, используемый для идентификации узлов, обрабатываемых как строки. Узлы поступают из XML-документа, дескриптор которого передается в параметре idoc.
flags
Указывает на сопоставление, используемое между XML-данными и реляционным набором строк, а также на порядок заполнения переполненного столбца. Аргумент flags является необязательным входным параметром и может принимать одно из указанных ниже значений.
Байтовое значение | Description |
---|---|
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-документа (например, имена элементов и атрибутов, иерархию документов, пространства имен, 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, пространство имен отсутствует. |
datatype | nvarchar | Фактический тип данных элемента или строки атрибута, в противном случае — NULL. Тип данных вычисляется на основе встроенного DTD или встроенной схемы |
prev | bigint | XML-идентификатор предыдущего элемента-брата. Принимает значение NULL, если нет прямого предыдущего одноуровневого элемента. |
text | ntext | Содержит значение атрибута или содержимое элемента в текстовой форме (или равен NULL, если запись граничной таблицы не требует значения). |
Примеры
А. Использование базовой инструкции 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
.
В инструкции OPENXML
rowpattern (/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;
См. также
Обратная связь
https://aka.ms/ContentUserFeedback.
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделеОтправить и просмотреть отзыв по