Příklady: Použití OPENXML

platí pro:SQL Server Azure SQL Database Azure SQL Managed Instance SQL databáze v Microsoft Fabric

Příklady v tomto článku ukazují, jak se OPENXML používá k vytvoření zobrazení sady řádků dokumentu XML. Informace o syntaxi OPENXML naleznete v tématu OPENXML (Transact-SQL). Příklady ukazují všechny aspekty OPENXML, ale nezadávají metaproperty v OPENXML. Další informace o tom, jak zadat metaproperties v OPENXML naleznete v tématu Určení metaproperties v OPENXML.

Examples

Při načítání dat se rowpattern používá k identifikaci uzlů v dokumentu XML, které určují řádky. Kromě toho je rowpattern vyjádřen v jazyce vzoru XPath, který se používá v implementaci MSXML XPath. Pokud například vzor končí v elementu nebo atributu, vytvoří se řádek pro každý prvek nebo uzel atributu, který je vybrán pomocí rowpattern.

Hodnota příznaků poskytuje výchozí mapování. Pokud není v objektu SchemaDeclaration zadán žádný ColPattern, předpokládá se mapování zadané v příznakech. Hodnota příznaků se ignoruje, pokud je v parametru SchemaDeclaration zadán ColPattern. Zadaný ColPattern určuje mapování, na atributy orientovaný nebo na elementy orientovaný, a také chování při práci s přetečením a nespotřebovanými daty.

A. Spuštění příkazu SELECT pomocí OPENXML

Dokument XML v tomto příkladu <Customer>se skládá z , <Order>a <OrderDetail> elementů. Příkaz OPENXML načte informace o zákaznících v sadě řádků se dvěma sloupci, ID zákazníka a ContactName z dokumentu XML.

Nejprve se volá uložená procedura sp_xml_preparedocument pro získání popisovače dokumentu. Tento popisovač dokumentu se předá Open XML.

Příkaz OPENXML znázorňuje následující:

rowpattern (/ROOT/Customer) identifikuje <Customer> uzly, které se mají zpracovat.
Hodnota parametru flags je nastavena na hodnotu 1 a označuje mapování zaměřené na atributy. V důsledku toho se atributy XML mapují na sloupce v sadě řádků definované ve SchemaDeclaration.
V SchemaDeclaration v klauzuli WITH zadané hodnoty ColName odpovídají odpovídajícím názvům atributů XML. Proto parametr ColPattern není zadaný ve SchemaDeclaration.

Příkaz SELECT pak načte všechny sloupce v sadě řádků, kterou poskytuje 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;

Toto je výsledek:

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

<Customer> Vzhledem k tomu, že prvky nemají žádné dílčí prvky, pokud je stejný příkaz SELECT proveden s příznaky nastavenými na 2 označující mapování zaměřené na element, hodnoty CustomerID a ContactName pro oba zákazníky se vrátí jako NULL.

@xmlDocument může být také typu XML nebo typu (n)varchar(max).

Pokud <CustomerID> a <ContactName> v dokumentu XML jsou dílčí prvky, mapování zaměřené na element načte hodnoty.

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;

Toto je výsledek:

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

Popisovač dokumentu vrácený sp_xml_preparedocument je platný během dávky, nikoli relace.

B. Určení sloupce ColPattern pro mapování mezi sloupci sady řádků a atributy a elementy XML

Tento příklad ukazuje, jak je v volitelném parametru ColPattern zadán vzor XPath, který poskytuje mapování mezi sloupci sady řádků a atributy a elementy XML.

Dokument XML v tomto příkladu <Customer>se skládá z , <Order>a <OrderDetail> elementů. Příkaz OPENXML načte informace o zákaznících a objednávkách jako sadu řádků (CustomerID, OrderDate, ProdID a Qty) z dokumentu XML.

Nejprve se volá uložená procedura sp_xml_preparedocument pro získání popisovače dokumentu. Tento popisovač dokumentu se předá Open XML.

Příkaz OPENXML znázorňuje následující:

rowpattern (/ROOT/Customer/Order/OrderDetail) identifikuje <OrderDetail> uzly, které se mají zpracovat.

Pro ilustraci je hodnota parametru flags nastavena na hodnotu 2 a označuje mapování zaměřené na elementy. Mapování zadané v colPattern však toto mapování přepíše. To znamená, že vzor XPath zadaný v ColPattern mapuje sloupce v sadě řádků na atributy. Výsledkem je mapování zaměřené na atributy.

V SchemaDeclaration, v klauzuli WITH jsou parametry ColPattern, ColName a ColType> také specifikovány. Volitelný colPattern je zadaný vzor XPath a označuje následující:

Sloupce OrderID, CustomerID a OrderDate v množině řádků odpovídají atributům nadřazených uzlů identifikovaných pomocí rowpattern a rowpattern identifikuje <OrderDetail> uzly. Proto se sloupce CustomerID a OrderDate mapují na atributy CustomerID a OrderDate elementu <Order> .
Sloupce ProdID a Qty v sadě řádků se mapují na atributy ProductID a Quantity uzlů identifikovaných v rowpatternu.

Příkaz SELECT pak načte všechny sloupce v sadě řádků, kterou poskytuje 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;

Toto je výsledek:

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

Vzor XPath zadaný jako ColPattern lze také zadat pro mapování elementů XML na sloupce sady řádků. Výsledkem je mapování zaměřené na elementy. V následujícím příkladu je dokument <CustomerID> XML a <OrderDate> jsou dílčími prvky elementu <Orders> . Protože ColPattern přepíše mapování zadané v parametru flags , parametr flags není zadaný v 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. Kombinování mapování zaměřeného na atributy a na elementy

V tomto příkladu je parametr flags nastaven na hodnotu 3 a označuje, že se použije mapování zaměřené na atributy i na elementy. V tomto případě se nejprve použije mapování zaměřené na atributy a mapování zaměřené na element se použije pro všechny sloupce, se kterými se dosud nezabídá.

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;

Toto je výsledek.

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

Pro ID zákazníka se použije mapování zaměřené na atribut. V elementu není žádný atribut <Customer>. Proto se použije mapování zaměřené na elementy.

D. Zadejte funkci XPath text() jako colPattern.

Dokument XML v tomto příkladu se skládá z prvků <Customer> a <Order>. Příkaz OPENXML načte sadu řádků, která je tvořena atributem oid z <Order> elementu, ID nadřazeného uzlu identifikovaného rowpatternem a řetězcem hodnoty listu obsahu elementu.

Nejprve je volána uložená procedura sp_xml_preparedocument pro získání popisovače dokumentu. Tento popisovač dokumentu se předá openXML.

Příkaz OPENXML znázorňuje následující:

rowpattern (/root/Customer/Order) identifikuje <Order> uzly, které se mají zpracovat.
Hodnota parametru flags je nastavena na hodnotu 1 a označuje mapování zaměřené na atributy. V důsledku toho se atributy XML mapují na sloupce sady řádků definované ve SchemaDeclaration.
V SchemaDeclaration v klauzuli WITH se názvy sloupců v řádkové sadě oid a amount shodují s odpovídajícími názvy atributů XML. Proto není zadaný parametr ColPattern . Pro sloupec komentáře v sadě řádků je funkce text()XPath zadána jako ColPattern. Tím se přepíše mapování zaměřené na atributy zadané v příznakech a sloupec obsahuje řetězec hodnoty listu obsahu prvku.

Příkaz SELECT pak načte všechny sloupce v sadě řádků, kterou poskytuje 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;

Toto je výsledek:

oid   amount        comment
----- -----------   -----------------------------
O1    3.5           NULL
O2    13.4          Customer was very satisfied
O3    100.0         Happy Customer.
O4    10000.0       NULL

E. Určete TableName v klauzuli WITH

Tento příklad určuje TableName v klauzuli WITH místo SchemaDeclaration. To je užitečné, pokud máte tabulku, která má požadovanou strukturu a nejsou vyžadovány žádné vzory sloupců, parametr ColPattern .

Dokument XML v tomto příkladu se skládá z prvků <Customer> a <Order>. Příkaz OPENXML načte informace o pořadí v sadě řádků se třemi sloupci (oid, datum a částka) z dokumentu XML.

Nejprve se volá uložená procedura sp_xml_preparedocument pro získání popisovače dokumentu. Tento popisovač dokumentu je předáván OPENXML.

Příkaz OPENXML znázorňuje následující:

rowpattern (/root/Customer/Order) identifikuje <Order> uzly, které se mají zpracovat.
V klauzuli WITH není žádné SchemaDeclaration. Místo toho je zadán název tabulky. Schéma tabulky se proto používá jako schéma sady řádků.
Hodnota parametru flags je nastavena na hodnotu 1 a označuje mapování zaměřené na atributy. Atributy prvků identifikovaných pomocí rowpattern proto mapují na sloupce datové sady se stejným názvem.

Příkaz SELECT pak načte všechny sloupce v sadě řádků, kterou poskytuje 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;

Toto je výsledek:

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. Získejte výsledek ve formátu tabulky hran

V tomto příkladu není klauzule WITH zadána v příkazu OPENXML. Výsledkem je, že sada řádků generovaná jazykem OPENXML má formát tabulky edge. Příkaz SELECT vrátí všechny sloupce v hraniční tabulce.

Ukázkový dokument XML v příkladu se skládá z <Customer>, <Order>a <OrderDetail> elementů.

Nejprve se volá uložená procedura sp_xml_preparedocument pro získání popisovače dokumentu. Tento popisovač dokumentu se předá Open XML.

Příkaz OPENXML znázorňuje následující:

rowpattern (/ROOT/Customer) identifikuje <Customer> uzly, které se mají zpracovat.
Klauzule WITH není k dispozici. OpenXML proto vrátí sadu řádků ve formátu tabulky edge.

Příkaz SELECT pak načte všechny sloupce v hraniční tabulce.

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;

Výsledek se vrátí jako hraniční tabulka. Chcete-li získat informace, můžete psát dotazy na tabulku hran. Například:

Následující dotaz vrátí počet uzlů zákazníka v dokumentu. Vzhledem k tomu, že klauzule WITH není zadaná, vrátí OPENXML hraniční tabulku. Příkaz SELECT se dotazuje na hraniční tabulku.
```
SELECT count(*)
FROM OPENXML(@docHandle, '/')
WHERE localname = 'Customer';
```

Následující dotaz vrátí místní názvy uzlů XML typu elementu.

SELECT distinct localname
FROM OPENXML(@docHandle, '/')
WHERE nodetype = 1
ORDER BY localname;

G. Specifikace řadového vzoru končícího atributem

Dokument XML v tomto příkladu <Customer>se skládá z , <Order>a <OrderDetail> elementů. Příkaz OPENXML načte informace o podrobnostech objednávky v sadě řádků se třemi sloupci (IDproduktu, množství a ID objednávky) z dokumentu XML.

Nejprve se zavolá sp_xml_preparedocument pro získání popisovače dokumentu. Tento popisovač dokumentu se předá Open XML.

Příkaz OPENXML znázorňuje následující:

rowpattern (/ROOT/Customer/Order/OrderDetail/@ProductID) končí atributem XML, IDproduktu. Ve výsledné sadě řádků se vytvoří řádek pro každý uzel atributu vybraný v dokumentu XML.
V tomto příkladu není zadaný parametr flagů. Místo toho jsou mapování určena parametrem ColPattern .

V SchemaDeclaration v klauzuli WITH je colPattern také zadán s parametry ColName a ColType . Volitelný colPattern je vzor XPath určený k označení následujících:

Vzor XPath (.) zadaný jako ColPattern pro sloupec ProdID v sadě řádků identifikuje kontextový uzel, aktuální uzel. Podle zadaného rowpatternu se jedná o atribut ProductID elementu <OrderDetail> .
ColPattern, ../@Quantity, zadaný pro sloupec Qty v sadě řádků identifikuje atribut Quantity nadřazeného uzlu kontextového uzlu <ProductID>.
Podobně ColPattern, ../../@OrderID, zadaný pro sloupec OID v sadě řádků identifikuje atribut OrderID rodičovského uzlu, <Order>, nadřazeného uzlu kontextového uzlu. Nadřazený uzel je <OrderDetail>a kontextový uzel je <ProductID>.

Příkaz SELECT pak načte všechny sloupce v sadě řádků, kterou poskytuje 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;

Toto je výsledek:

ProdID      Qty         OID
----------- ----------- -------
11          12          10248
42          10          10248
72          3           10283

H. Zadání dokumentu XML s více textovými uzly

Pokud máte v dokumentu XML více textových uzlů, vrátí příkaz SELECT s colPatternemtext() pouze první textový uzel, nikoli všechny. Například:

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;

Příkaz SELECT vrátí jako výsledek T , nikoli TaU.

I. Zadání datového typu XML v klauzuli WITH

V klauzuli WITH musí vzor sloupce, který je namapován na sloupec datového typu XML , bez ohledu na to, zda je zadán nebo nezatypován, vracet prázdnou sekvenci nebo posloupnost prvků, pokyny ke zpracování, textové uzly a komentáře. Data se přetypová na datový typ XML .

V následujícím příkladu obsahuje deklarace schématu tabulky v klauzuli WITH sloupce typu 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;

Konkrétně předáváte proměnnou typu XML (@x) funkci sp_xml_preparedocument().

Toto je výsledek:

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>

Všimněte si následujícího výsledku:

Pro sloupec lname typu varchar(30) se jeho hodnota načte z odpovídajícího <lname> prvku.
Pro sloupec xmlname typu xml se vrátí stejný element name jako jeho hodnota.
Příznak je nastavený na hodnotu 10. Hodnota 10 znamená 2 + 8, kde 2 označuje mapování zaměřené na prvek a 8 označuje, že do sloupce OverFlow definovaného v klauzuli WITH by se měla přidat pouze nekonsumovaná data XML. Pokud nastavíte příznak na hodnotu 2, celý dokument XML se zkopíruje do sloupce OverFlow, který je zadaný v klauzuli WITH.
Pokud je sloupec v klauzuli WITH typově určený jako XML a instance XML neodpovídá schématu, vrátí se chyba.

J. Načtení jednotlivých hodnot z atributů s více hodnotami

Dokument XML může mít atributy, které jsou vícehodnotové. Atribut IDREFS může být například vícehodnotový. V dokumentu XML jsou hodnoty vícehodnotových atributů zadány jako řetězec s hodnotami oddělenými mezerou. V následujícím XML dokumentu je atribut attends prvku <Student> a atribut attendedBy prvku <Class> vícehodnotný. Načtení jednotlivých hodnot z atributu XML s více hodnotami a uložení každé hodnoty do samostatného řádku v databázi vyžaduje další práci. Tento příklad ukazuje proces.

Tento ukázkový dokument XML se skládá z následujících prvků:

<Student>

id (ID studenta), jméno a účastní atributy. Atribut attends je atribut s více hodnotami.
<Třída>

ID (ID třídy), název a attendedBy atribut. Atribut attendedBy je atribut s více hodnotami.

Atribut attends ve <Student> a atribut attendedBy v <Class> představují relaci m:n mezi tabulkami Student a Class. Student může mít mnoho předmětů a třída může mít mnoho studentů.

Předpokládejme, že chcete tento dokument nahrnout a uložit do databáze, jak je znázorněno v následujícím příkladu:

<Student> Uložte data v tabulce Students.
<Class> Uložte data v tabulce Courses.
Uložte data relace m:n mezi studentem a třídou v tabulce CourseAttendence. K extrakci hodnot je potřeba více práce. Pokud chcete načíst tyto informace a uložit je do tabulky, použijte tyto uložené procedury:
- Insert_Idrefs_Values
  
  Vloží hodnoty ID kurzu a ID studenta do tabulky CourseAttendence.
- Extract_idrefs_values
  
  Extrahuje ID jednotlivých studentů z každého <kurzového prvku>. K načtení těchto hodnot se používá hraniční tabulka.

Tady jsou kroky:

-- 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. Načíst binární data z dat kódovaných base64 v XML

Binární data jsou často zahrnuta do XML pomocí kódování base64. Když rozložíte tento XML soubor s použitím OPENXML, obdržíte data zakódovaná v base64. Tento příklad ukazuje, jak můžete převést zakódovaná data base64 zpět na binární.

Vytvořte tabulku s ukázkovými binárními daty.
Použijte dotaz FOR XML a možnost BINARY BASE64 k vytvoření XML, který má binární data kódovaná jako base64.
Zpracujte XML pomocí OPENXML. Data vrácená jazykem OPENXML budou zakódovaná daty base64. Potom zavolejte .value funkci a převeďte ji zpět na binární.

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

Toto je výsledek. Vrácená binární data jsou původní binární data v tabulce T.

Col1        BinaryCol
----------- ---------------------
1           0x1234567890

Viz také

Váš názor

Byla tato stránka užitečná?

Last updated on 2025-11-18

Sdílet prostřednictvím

Příklady: Použití OPENXML

Examples

A. Spuštění příkazu SELECT pomocí OPENXML

B. Určení sloupce ColPattern pro mapování mezi sloupci sady řádků a atributy a elementy XML

C. Kombinování mapování zaměřeného na atributy a na elementy

D. Zadejte funkci XPath text() jako colPattern.

E. Určete TableName v klauzuli WITH

F. Získejte výsledek ve formátu tabulky hran

G. Specifikace řadového vzoru končícího atributem

H. Zadání dokumentu XML s více textovými uzly

I. Zadání datového typu XML v klauzuli WITH

J. Načtení jednotlivých hodnot z atributů s více hodnotami

K. Načíst binární data z dat kódovaných base64 v XML

Viz také

Váš názor

Další materiály