Примеры. Использование OPENXML
Область применения:
SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure
В примерах этой статьи показано, как openXML используется для создания представления набора строк XML-документа. Сведения о синтаксисе OPENXML см. в разделе OPENXML (Transact-SQL). В примерах показаны все аспекты OPENXML, но не указывайте метапродажи в OPENXML. Дополнительные сведения о том, как использовать метасвойства в OPENXML, см. в статье Определение метасвойств в инструкции OPENXML.
Примеры
При получении данных строка используется для идентификации узлов в XML-документе, определяющих строки. Кроме того, шаблон rowpattern выражен на языке шаблонов XPath, который используется в реализации языка XPath в MSXML. Например, если шаблон заканчивается элементом или атрибутом, то строка создается для каждого узла элемента или атрибута, который выбран шаблоном rowpattern.
Значение параметра flags предоставляет сопоставление по умолчанию. Если параметр ColPattern не задан в элементе SchemaDeclaration, то предполагается сопоставление, указанное в параметре flags . Значение параметра flags игнорируется, если параметр ColPattern определен в элементе SchemaDeclaration. Задание параметра ColPattern определяет атрибутивное или элементное сопоставление, а также характер обработки переполнения и невостребованных данных.
А. Выполнение инструкции SELECT с помощью OPENXML
XML-документ в этом примере состоит из <Customer>элементов и <Order><OrderDetail> элементов. Инструкция OPENXML получает из XML-документа сведения о заказчике в наборе строк из двух столбцов — CustomerID и ContactName.
Сначала вызывается хранимая процедура sp_xml_preparedocument , чтобы получить дескриптор документа. Дескриптор документа передается инструкции OPENXML.
Инструкция OPENXML иллюстрирует следующее:
rowpattern (/ROOT/Customer) определяет
<Customer>узлы для обработки.параметр flags имеет значение 1 , которое указывает на сопоставление с использованием атрибутивной модели; В результате XML-атрибуты сопоставляются со столбцами в наборе строк, определенном в элементе SchemaDeclaration;
в элементе SchemaDeclarationпредложения WITH заданные значения параметра ColName совпадают с соответствующими именами XML-атрибутов. Поэтому параметр ColPattern не указан в SchemaDeclaration.
Затем инструкция SELECT извлекает все столбцы из набора строк, предоставленного инструкцией OPENXML:
DECLARE @DocHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<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 Gonzalez">
<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 @DocHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@DocHandle, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20));
EXEC sp_xml_removedocument @DocHandle;
Результат:
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzalez
<Customer> Так как элементы не имеют подэлементов, если одна и та же инструкция SELECT выполняется с флагами, установленными для 2, чтобы указать сопоставление с элементом, значения CustomerID и ContactName для обоих клиентов возвращаются как NULL.
Аргумент @xmlDocument может также иметь тип xml или (n)varchar(max).
Если <CustomerID> и <ContactName> в XML-документе являются подэлементами, сопоставление, ориентированное на элемент, извлекает значения.
DECLARE @XmlDocumentHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer>
<CustomerID>VINET</CustomerID>
<ContactName>Paul Henriot</ContactName>
<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</CustomerID>
<ContactName>Carlos Gonzalez</ContactName>
<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 @XmlDocumentHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer',2)
WITH (CustomerID varchar(10),
ContactName varchar(20));
EXEC sp_xml_removedocument @XmlDocumentHandle;
Результат:
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzalez
Дескриптор документа, возвращаемый sp_xml_preparedocument , действителен во время пакета, а не сеанса.
B. Указание ColPattern для сопоставления между столбцами набора строк и атрибутами XML и элементами
Данный пример показывает, как задается шаблон XPath в необязательном параметре ColPattern для сопоставления столбцов набора строк с XML-атрибутами и элементами.
XML-документ в этом примере состоит из <Customer>элементов и <Order><OrderDetail> элементов. Инструкция OPENXML получает из XML-документа сведения о заказчике и заказе в виде набора строк (CustomerID, OrderDate, ProdIDи Qty).
Сначала вызывается хранимая процедура sp_xml_preparedocument , чтобы получить дескриптор документа. Дескриптор документа передается инструкции OPENXML.
Инструкция OPENXML иллюстрирует следующее:
- rowpattern (/ROOT/Customer/Order/OrderDetail) определяет
<OrderDetail>узлы для обработки.
В примере параметр flags имеет значение 2 , которое указывает на сопоставление с использованием элементов. Однако сопоставление, указанное в параметре ColPattern , перекрывает данное сопоставление. То есть шаблон XPath, заданный в параметре ColPattern , сопоставляет столбцы набора строк с атрибутами. Результатом является атрибутивное сопоставление.
В элементе SchemaDeclarationпредложения WITH параметр ColPattern также задается параметрами ColName и ColType . Необязательный параметр ColPattern является заданным шаблоном XPath и указывает следующее:
Столбцы OrderID, CustomerID и OrderDate в наборе строк сопоставляются
<OrderDetail>с атрибутами родительских узлов, определяемых rowpattern, и rowpattern идентифицирует узлы. Поэтому столбцы CustomerID и OrderDate сопоставляются с атрибутами<Order>CustomerID и OrderDate элемента.столбцы ProdID и Qty в наборе строк сопоставляются с атрибутами ProductID и Quantity узлов, заданных в шаблоне rowpattern.
Затем инструкция SELECT извлекает все столбцы из набора строк, предоставленного инструкцией OPENXML:
DECLARE @XmlDocumentHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<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 Gonzalez">
<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 @XmlDocumentHandle OUTPUT, @XmlDocument;
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity');
EXEC sp_xml_removedocument @XmlDocumentHandle;
Результат:
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
Шаблон XPath, заданный как параметр ColPattern , может также быть указан для сопоставления XML-элементов со столбцами набора строк. Результатом является сопоставление с использованием атрибутов. В следующем примере XML-документ <CustomerID> и <OrderDate> являются подэлементами <Orders> элемента. Так как ColPattern перезаписывает сопоставление, указанное в параметре флагов, параметр флагов не указан в OPENXML.
DECLARE @docHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order EmployeeID="5" >
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<OrderDate>1996-07-04T00:00:00</OrderDate>
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzalez">
<Order EmployeeID="3" >
<OrderID>10283</OrderID>
<CustomerID>LILAS</CustomerID>
<OrderDate>1996-08-16T00:00:00</OrderDate>
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID varchar(10) '../CustomerID',
OrderDate datetime '../OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity');
EXEC sp_xml_removedocument @docHandle;
C. Объединение сопоставления атрибутов, ориентированных на атрибуты и элементно-ориентированное сопоставление
В этом примере параметр flags имеет значение 3 и указывает на применение как атрибутивного, так и элементного сопоставления. В этом случае сначала применяется атрибутивное сопоставление, а затем элементное сопоставление для всех столбцов, которые еще не обработаны:
DECLARE @docHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument =N'<ROOT>
<Customer CustomerID="VINET" >
<ContactName>Paul Henriot</ContactName>
<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 Gonzalez</ContactName>
<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 @docHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer',3)
WITH (CustomerID varchar(10),
ContactName varchar(20));
EXEC sp_xml_removedocument @docHandle;
Результат:
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzalez
Атрибутивное сопоставление применяется к столбцу CustomerID. В элементе нет атрибута <Customer> ContactName. поэтому применяется элементное сопоставление.
D. Укажите функцию XPath в качестве ColPattern
XML-документ в этом примере состоит из <Customer> элементов и <Order> элементов. Инструкция OPENXML извлекает набор строк, состоящий из атрибута oid из <Order> элемента, идентификатор родительского элемента узла, идентифицированного строкой rowpattern, и строкой конечного значения содержимого элемента.
Сначала вызывается хранимая процедура sp_xml_preparedocument , чтобы получить дескриптор документа. Дескриптор документа передается инструкции OPENXML.
Инструкция OPENXML иллюстрирует следующее:
rowpattern (/root/Customer/Order) определяет
<Order>узлы для обработки.параметр flags имеет значение 1 , которое указывает на сопоставление с использованием атрибутивной модели; В результате XML-атрибуты сопоставляются со столбцами в наборе строк, определенном в элементе SchemaDeclaration;
в элементе SchemaDeclaration предложения WITH имена столбцов oid и amount в наборе строк совпадают с соответствующими именами XML-атрибутов. Поэтому параметр ColPattern не указан. Для столбца комментариев в наборе строк функция
text()XPath указывается как ColPattern. Это перезаписывает сопоставление с использованием атрибутивной модели, заданное в параметре flags, и столбец содержит строку конечных значений содержимого элемента.
Затем инструкция SELECT извлекает все столбцы из набора строк, предоставленного инструкцией OPENXML:
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
--sample XML document
SET @xmlDocument =N'<root>
<Customer cid= "C1" name="Janine" city="Issaquah">
<Order oid="O1" date="1/20/1996" amount="3.5" />
<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
</Order>
</Customer>
<Customer cid="C2" name="Ursula" city="Oelde" >
<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
white red">
<Urgency>Important</Urgency>
Happy Customer.
</Order>
<Order oid="O4" date="1/20/1996" amount="10000"/>
</Customer>
</root>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/root/Customer/Order', 1)
WITH (oid char(5),
amount float,
comment ntext 'text()');
EXEC sp_xml_removedocument @docHandle;
Результат:
oid amount comment
----- ----------- -----------------------------
O1 3.5 NULL
O2 13.4 Customer was very satisfied
O3 100.0 Happy Customer.
O4 10000.0 NULL
Е. Указание TableName в предложении WITH
Этот пример задает элемент TableName в предложении WITH вместо элемента SchemaDeclaration. Это полезно, если таблица имеет нужную структуру и не требуются шаблоны столбцов (параметр ColPattern ).
XML-документ в этом примере состоит из <Customer> элементов и <Order> элементов. Инструкция OPENXML возвращает сведения о заказе в наборе строк из трех столбцов (oid, dateи amount), полученные из XML-документа.
Сначала вызывается хранимая процедура sp_xml_preparedocument , чтобы получить дескриптор документа. Дескриптор документа передается инструкции OPENXML.
Инструкция OPENXML иллюстрирует следующее:
rowpattern (/root/Customer/Order) определяет
<Order>узлы для обработки.В предложении WITH нет схемы . вместо него задано имя таблицы; поэтому схема таблицы используется в качестве схемы набора строк;
параметр flags имеет значение 1 , которое указывает на сопоставление с использованием атрибутивной модели; поэтому атрибуты элементов, заданные шаблоном rowpattern, сопоставляются со столбцами набора строк с таким же именем.
Затем инструкция SELECT извлекает все столбцы из набора строк, предоставленного инструкцией OPENXML:
-- Create a test table. This table schema is used by OPENXML as the
-- rowset schema.
CREATE TABLE T1(oid char(5), date datetime, amount float);
GO
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
-- Sample XML document
SET @xmlDocument =N'<root>
<Customer cid= "C1" name="Janine" city="Issaquah">
<Order oid="O1" date="1/20/1996" amount="3.5" />
<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very
satisfied</Order>
</Customer>
<Customer cid="C2" name="Ursula" city="Oelde" >
<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
white red">
<Urgency>Important</Urgency>
</Order>
<Order oid="O4" date="1/20/1996" amount="10000"/>
</Customer>
</root>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/root/Customer/Order', 1)
WITH T1;
EXEC sp_xml_removedocument @docHandle;
Результат:
oid date amount
----- --------------------------- ----------
O1 1996-01-20 00:00:00.000 3.5
O2 1997-04-30 00:00:00.000 13.4
O3 1999-07-14 00:00:00.000 100.0
O4 1996-01-20 00:00:00.000 10000.0
F. Получение результата в формате пограничной таблицы
В этом примере предложение WITH не указано в инструкции OPENXML. В результате набор строк, сформированный инструкцией OPENXML, имеет формат краевой таблицы. Инструкция SELECT возвращает все столбцы в краевой таблице.
Пример XML-документа в примере состоит из <Customer>элементов и <Order><OrderDetail> элементов.
Сначала вызывается хранимая процедура sp_xml_preparedocument , чтобы получить дескриптор документа. Дескриптор документа передается инструкции OPENXML.
Инструкция OPENXML иллюстрирует следующее:
rowpattern (/ROOT/Customer) определяет
<Customer>узлы для обработки.Предложение WITH не предоставляется. поэтому инструкция OPENXML возвращает набор строк в формате краевой таблицы.
Затем инструкция SELECT возвращает все столбцы в краевой таблице.
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
SET @xmlDocument = N'<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 Gonzalez">
<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 @docHandle OUTPUT, @xmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer');
EXEC sp_xml_removedocument @docHandle;
Результат возвращается в виде краевой таблицы. Можно написать запрос к краевой таблице для получения данных. Например:
следующий запрос возвращает количество узлов Customer в документе. Так как предложение WITH не указано, OPENXML возвращает граничную таблицу. Инструкция SELECT запрашивает краевую таблицу:
SELECT count(*) FROM OPENXML(@docHandle, '/') WHERE localname = 'Customer';следующий запрос возвращает локальные имена XML-узлов типа элементов:
SELECT distinct localname FROM OPENXML(@docHandle, '/') WHERE nodetype = 1 ORDER BY localname;
G. Указание строкиpattern, заканчивающегося атрибутом
XML-документ в этом примере состоит из <Customer>элементов и <Order><OrderDetail> элементов. Инструкция OPENXML возвращает сведения о заказе в наборе строк из трех столбцов (ProductID, Quantityи OrderID) из XML-документа.
Сначала вызывается хранимая процедура sp_xml_preparedocument , чтобы получить дескриптор документа. Дескриптор документа передается инструкции OPENXML.
Инструкция OPENXML иллюстрирует следующее:
шаблонrowpattern (/ROOT/Customer/Order/OrderDetail/@ProductID) заканчивается XML-атрибутом — ProductID. В результирующем наборе строк для каждого выбранного в XML-документе узла атрибута создается строка;
В этом примере параметр флагов не указан. вместо него для указания сопоставлений используется параметр ColPattern .
В элементе SchemaDeclaration предложения WITH параметр ColPattern также задан с параметрами ColName и ColType . Необязательный параметр ColPattern является заданным шаблоном XPath и указывает следующее:
шаблон XPath (.), указанный в виде параметра ColPattern для столбца ProdID в наборе строк, определяет контекстный узел — текущий узел. Как указано в строкеpattern, это атрибут
<OrderDetail>ProductID элемента.ColPattern, .. /@Quantity, указанный для столбца Qty в наборе строк, определяет атрибут Quantity родительского узла,
<OrderDetail>узел узла контекстного узла, <ProductID>.Аналогичным образом, ColPattern, .. /.. /@OrderID, указанный для столбца OID в наборе строк,
<Order>определяет атрибут OrderID родительского узла, родительского узла узла контекста. Родительский узел —<OrderDetail>и узел контекста<ProductID>.
Затем инструкция SELECT извлекает все столбцы из набора строк, предоставленного инструкцией OPENXML:
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
--Sample XML document
SET @xmlDocument =N'<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 Gonzalez">
<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 @docHandle OUTPUT, @xmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail/@ProductID')
WITH ( ProdID int '.',
Qty int '../@Quantity',
OID int '../../@OrderID');
EXEC sp_xml_removedocument @docHandle;
Результат:
ProdID Qty OID
----------- ----------- -------
11 12 10248
42 10 10248
72 3 10283
H. Указание XML-документа с несколькими текстовыми узлами
Если в XML-документе есть несколько текстовых узлов, инструкция SELECT с colPattern, text()возвращает только первый текстовый узел, а не все из них. Например:
DECLARE @h int;
EXEC sp_xml_preparedocument @h OUTPUT,
N'<root xmlns:a="urn:1">
<a:Elem abar="asdf">
T<a>a</a>U
</a:Elem>
</root>',
'<ns xmlns:b="urn:1" />';
SELECT * FROM openxml(@h, '/root/b:Elem')
WITH (Col1 varchar(20) 'text()');
EXEC sp_xml_removedocument @h;
Инструкция SELECT возвращает в качестве результата таблицу T , а не TaU.
I. Укажите тип данных XML в предложении WITH
В предложении WITH шаблон столбца, который сопоставлен со столбцом типа данных xml , типизированным или нетипизированным, должен вернуть либо пустую последовательность, либо последовательность элементов, инструкций обработки, текстовых узлов и комментариев. Данные приводятся к типу xml .
В следующем примере объявление схемы таблицы в предложении WITH включает столбцы типа xml :
DECLARE @h int;
DECLARE @x xml;
set @x = '<Root>
<row id="1"><lname>Duffy</lname>
<Address>
<Street>111 Maple</Street>
<City>Seattle</City>
</Address>
</row>
<row id="2"><lname>Wang</lname>
<Address>
<Street>222 Pine</Street>
<City>Bothell</City>
</Address>
</row>
</Root>';
EXEC sp_xml_preparedocument @h output, @x;
SELECT *
FROM OPENXML (@h, '/Root/row', 10)
WITH (id int '@id',
lname varchar(30),
xmlname xml 'lname',
OverFlow xml '@mp:xmltext');
EXEC sp_xml_removedocument @h;
В частности, вы передаете переменную типа XML (@x) функции sp_xml_preparedocument().
Результат:
id lname xmlname OverFlow
--- ------- ------------------------------ -------------------------------
1 Duffy <lname>Duffy</lname> <row><Address>
<Street>111 Maple</Street>
<City>Seattle</City>
</Address></row>
2 Wang <lname>Wang</lname> <row><Address>
<Street>222 Pine</Street>
<City>Bothell</City>
</Address></row>
Обратите внимание на следующие факты:
Для столбца lname типа varchar(30) его значение извлекается из соответствующего
<lname>элемента.для столбца xmlname типа xml в качестве значения возвращается элемент с таким же именем;
флаг принимает значение 10, означающее 2 + 8, где 2 указывает на элементное сопоставление, а 8 — на то, что к столбцу OverFlow, заданному в предложении WITH, должны быть добавлены только невостребованные XML-данные. Если флаг устанавливается в значение 2, то в столбец OverFlow, заданный в предложении WITH, копируется весь XML-документ;
Если столбец в предложении WITH является типизированным XML-столбцом, а экземпляр XML не подтверждает схему, возвращается ошибка.
J. Получение отдельных значений из многозначных атрибутов
XML-документ может иметь многозначные атрибуты. Например, атрибут IDREFS может быть многозначным. В XML-документе значения многозначных атрибутов задаются в виде строки со значениями, разделенными пробелом. В следующем XML-документе атрибуты участников <элемента Student> и атрибута <attendBy класса> многозначны. Получение отдельных значений из многозначного XML-атрибута и хранение каждого значения в отдельной строке в базе данных требует дополнительной работы. Данный пример иллюстрирует процесс.
Данный образец XML-документа состоит из следующих элементов:
<Студент>
Атрибуты id (идентификатор студента), nameи attends . Атрибут attends является многозначным атрибутом.
<Class>
Атрибуты id (идентификатор класса), nameи attendedBy . Атрибут attendedBy является многозначным атрибутом.
Атрибут участников в Student> и атрибут attendBy в <<классе> представляют связь m:n между таблицами учащихся и классов. Студент может посещать множество классов, а класс может иметь множество студентов.
Предположим, что нужно взять часть этого документа и сохранить ее в базе данных, как показано ниже.
Сохраните данные
<Student>в таблице Students.Сохраните данные
<Class>в таблице Courses.Сохраните данные связи m:n между таблицами Student и Class в таблице CourseAttendence. Для извлечения значений требуется дополнительная работа. Для получения этих сведений и их сохранения в таблице используйте следующие хранимые процедуры:
Insert_Idrefs_Values
Вставляет значения идентификатора курса и идентификатора студента в таблицу CourseAttendence.
Extract_idrefs_values
Извлекает идентификаторы отдельных учащихся из каждого <элемента Course> . Краевая таблица используется для получения этих значений.
Ниже приведены шаги.
-- Create these tables:
DROP TABLE CourseAttendance;
DROP TABLE Students;
DROP TABLE Courses;
GO
CREATE TABLE Students(
id varchar(5) primary key,
name varchar(30)
);
GO
CREATE TABLE Courses(
id varchar(5) primary key,
name varchar(30),
taughtBy varchar(5)
);
GO
CREATE TABLE CourseAttendance(
id varchar(5) references Courses(id),
attendedBy varchar(5) references Students(id),
constraint CourseAttendance_PK primary key (id, attendedBy)
);
GO
-- Create these stored procedures:
DROP PROCEDURE f_idrefs;
GO
CREATE PROCEDURE f_idrefs
@t varchar(500),
@idtab varchar(50),
@id varchar(5)
AS
DECLARE @sp int;
DECLARE @att varchar(5);
SET @sp = 0;
WHILE (LEN(@t) > 0)
BEGIN
SET @sp = CHARINDEX(' ', @t+ ' ');
SET @att = LEFT(@t, @sp-1);
EXEC('INSERT INTO '+@idtab+' VALUES ('''+@id+''', '''+@att+''')');
SET @t = SUBSTRING(@t+ ' ', @sp+1, LEN(@t)+1-@sp);
END;
GO
DROP PROCEDURE fill_idrefs
GO
CREATE PROCEDURE fill_idrefs
@xmldoc int,
@xpath varchar(100),
@from varchar(50),
@to varchar(50),
@idtable varchar(100)
AS
DECLARE @t varchar(500);
DECLARE @id varchar(5);
/* Temporary Edge table */
SELECT *
INTO #TempEdge
FROM OPENXML(@xmldoc, @xpath);
DECLARE fillidrefs_cursor CURSOR FOR
SELECT CAST(iv.text AS nvarchar(200)) AS id,
CAST(av.text AS nvarchar(4000)) AS refs
FROM #TempEdge c, #TempEdge i,
#TempEdge iv, #TempEdge a, #TempEdge av
WHERE c.id = i.parentid
AND UPPER(i.localname) = UPPER(@from)
AND i.id = iv.parentid
AND c.id = a.parentid
AND UPPER(a.localname) = UPPER(@to)
AND a.id = av.parentid;
OPEN fillidrefs_cursor
FETCH NEXT FROM fillidrefs_cursor INTO @id, @t;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
execute f_idrefs @t, @idtable, @id;
END
FETCH NEXT FROM fillidrefs_cursor INTO @id, @t
END;
CLOSE fillidrefs_cursor;
DEALLOCATE fillidrefs_cursor;
Go
-- This is the sample document that is shredded and the data is stored in the preceding tables.
DECLARE @h int;
EXECUTE sp_xml_preparedocument @h OUTPUT, N'<Data>
<Student id = "s1" name = "Student1" attends = "c1 c3 c6" />
<Student id = "s2" name = "Student2" attends = "c2 c4" />
<Student id = "s3" name = "Student3" attends = "c2 c4 c6" />
<Student id = "s4" name = "Student4" attends = "c1 c3 c5" />
<Student id = "s5" name = "Student5" attends = "c1 c3 c5 c6" />
<Student id = "s6" name = "Student6" />
<Class id = "c1" name = "Intro to Programming"
attendedBy = "s1 s4 s5" />
<Class id = "c2" name = "Databases"
attendedBy = "s2 s3" />
<Class id = "c3" name = "Operating Systems"
attendedBy = "s1 s4 s5" />
<Class id = "c4" name = "Networks" attendedBy = "s2 s3" />
<Class id = "c5" name = "Algorithms and Graphs"
attendedBy = "s4 s5"/>
<Class id = "c6" name = "Power and Pragmatism"
attendedBy = "s1 s3 s5" />
</Data>';
INSERT INTO Students SELECT * FROM OPENXML(@h, '//Student') WITH Students
INSERT INTO Courses SELECT * FROM OPENXML(@h, '//Class') WITH Courses
/* Using the edge table */
EXECUTE fill_idrefs @h, '//Class', 'id', 'attendedby', 'CourseAttendance';
SELECT * FROM Students;
SELECT * FROM Courses;
SELECT * FROM CourseAttendance;
EXECUTE sp_xml_removedocument @h;
K. Получение двоичного файла из данных в кодировке Base64 в XML
Двоичные данные часто включаются в XML с использованием метода кодировки base64. Если взять часть этого XML с помощью инструкции OPENXML, то будут получены данные, закодированные методом base64. Этот пример показывает, как можно преобразовать данные в кодировке Base 64 обратно в двоичные.
создайте таблицу с образцами двоичных данных;
используйте запрос FOR XML и параметр BINARY BASE64 для формирования XML, который содержит двоичные данные, закодированные методом base64;
возьмите часть XML с помощью инструкции OPENXML. Данные, возвращенные инструкцией OPENXML, будут данными, закодированными методом base64. Затем вызовите функцию, чтобы преобразовать ее обратно в двоичный
.valueфайл.
CREATE TABLE T (Col1 int primary key, Col2 varbinary(100));
GO
-- Insert sample binary data
INSERT T VALUES(1, 0x1234567890);
GO
-- Create test XML document that has base64 encoded binary data (use FOR XML query and specify BINARY BASE64 option)
SELECT * FROM T
FOR XML AUTO, BINARY BASE64;
GO
-- result
-- <T Col1="1" Col2="EjRWeJA="/>
-- Now shredd the sample XML using OPENXML.
-- Call the .value function to convert
-- the base64 encoded data returned by OPENXML to binary.
DECLARE @h int;
EXEC sp_xml_preparedocument @h OUTPUT, '<T Col1="1" Col2="EjRWeJA="/>';
SELECT Col1,
CAST('<binary>' + Col2 + '</binary>' AS XML).value('.', 'varbinary(max)') AS BinaryCol
FROM openxml(@h, '/T')
WITH (Col1 integer, Col2 varchar(max)) ;
EXEC sp_xml_removedocument @h;
GO
Результат. возвращенные двоичные данные являются исходными двоичными данными таблицы T:
Col1 BinaryCol
----------- ---------------------
1 0x1234567890