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


Примеры. Использование 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

См. также