Przy użyciu OPENXML

Przykłady w tym temacie Pokaż, jak OPENXML jest używany do tworzenia widoku zestaw wierszy dokumentu XML.Aby uzyskać informacje na temat składni OPENXML, zobacz OPENXML (Transact-SQL).Przykłady Pokaż wszystkie aspekty OPENXML, ale nie określono metaproperties w OPENXML.Aby uzyskać więcej informacji na temat określania metaproperties w OPENXML, zobacz Określanie Metaproperties w OPENXML.

Przykłady

Pobierania danych, rowpattern jest używany do identyfikowania węzłów dokumentu XML, które określają wiersze.Ponadto rowpattern wyrażone w języku deseń XPath, używany w realizacji programu MSXML XPath.Na przykład, jeśli deseń kończy się w element lub atrybut, wiersz jest tworzony dla każdego węzła element lub atrybut jest wybierany przez rowpattern.

Flagi wartość zawiera mapowanie domyślne.Jeśli nie ColPattern określonych w SchemaDeclaration, mapowanie określone w flagi zakłada, że.Flagi wartość jest ignorowana, jeśli ColPattern określonych w SchemaDeclaration.Określony ColPattern Określa mapowanie, zorientowany na atrybut lub element zorientowany, a także zachowanie w zakresie przeciwdziałania przepełnienia i niewykorzystana danych.

A.Wykonywanie prostych wybierz instrukcja z OPENXML

Dokument XML w tym przykładzie składa się z <Customer>, <Order>, i <OrderDetail> elementów.Instrukcja OPENXML pobiera informacje o kliencie w dwu -kolumna zestawu zestaw wierszy, IDklienta i przedstawiciel, z dokumentu XML.

Pierwszy, sp_xml_preparedocument procedura składowana jest wywoływana uzyskiwania dojścia do dokumentu.Uchwyt ten dokument jest przekazywana do OPENXML.

Instrukcja OPENXML ilustruje następujące operacje:

rowpattern (/ ROOT/odbiorcy) identyfikuje <Customer> węzły przetwarzania.
Flagi wartość parametru jest zestaw do 1 i wskazuje zorientowany atrybut mapowania.W wyniku mapowania atrybutów XML do kolumn w zestawie zestaw wierszy zdefiniowanych w SchemaDeclaration.
W SchemaDeclaration, w klauzula WITH, określony Nazwa kolumny wartości odpowiadają odpowiadających im nazw atrybut XML.Dlatego ColPattern parametr nie jest określony w SchemaDeclaration.

Instrukcja SELECT następnie pobiera wszystkie kolumny zestawu zestaw wierszy, dostarczonych przez 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 Gonzlez">
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" 
          OrderDate="1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @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

Jest to wynikiem:

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

Ponieważ <Customer> elementów nie ma żadnych podelementy Jeśli tej samej instrukcja SELECT jest wykonywana z flagi zestaw do 2 oznacza zorientowany na element Mapowanie wartości IDklienta i przedstawiciel dla obu klientów są zwracane jako NULL.

Mogą również być @ xmlDocument xml typu lub (n)varchar(max) typu.

Jeśli <CustomerID> i <ContactName> w dokumencie XML są podelementy, zorientowany na element Mapowanie pobiera wartości.

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 Gonzlez</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

Jest to wynikiem:

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

Należy zauważyć, że zwracane dojście dokumentu przez sp_xml_preparedocument jest prawidłowa dla czasu trwania partia i nie sesja.

B.Określanie mapowania między zestawu zestaw wierszy, kolumn i atrybutów XML i elementy ColPattern

W przykładzie pokazano, jak określono w opcjonalnej deseń XPath ColPattern parametru mapowania między zestawu zestaw wierszy, kolumn i atrybutów XML i elementy.

Dokument XML w tym przykładzie składa się z <Customer>, <Order>, i <OrderDetail> elementów.Instrukcja OPENXML pobiera informacje o nabywcy i zamówienia jako zestawu zestaw wierszy (IDklienta, DataZamówienia, IdentyfikatorProduktu, i Ilość) z dokumentu XML.

Pierwszy, sp_xml_preparedocument procedura składowana jest wywoływana uzyskiwania dojścia do dokumentu.Uchwyt ten dokument jest przekazywana do OPENXML.

Instrukcja OPENXML ilustruje następujące operacje:

rowpattern (/ ROOT/odbiorcy/zamówienia/OrderDetail) identyfikuje <OrderDetail> węzły przetwarzania.

Na ilustracji flagi wartość parametru jest zestaw do 2 i wskazuje zorientowany na element mapowanie.Jednak mapowania określone w ColPattern zastępuje to mapowanie.Oznacza to, że wzorzec XPath określone w ColPattern mapuje atrybuty kolumn w zestawie zestaw wierszy.To wyniki zorientowany atrybut mapowania.

W SchemaDeclaration, w klauzula WITH ColPattern określony jest również z Nazwa kolumny i ColType parametry.Opcjonalna ColPattern jest określony wzorzec XPath i wskazuje następujące:

IDZamówienia, IDklienta, i DataZamówienia kolumn w zestawie zestaw wierszy mapowania atrybutów nadrzędnych węzłów identyfikowane przez rowpattern, i rowpattern identyfikuje <OrderDetail> węzłów.Dlatego IDklienta i DataZamówienia mapować kolumn IDklienta i DataZamówienia atrybuty <Order> element.
IdentyfikatorProduktu i Ilość mapować kolumn w zestawie zestaw wierszy IDProduktu i Ilość atrybuty węzłów w rowpattern.

Instrukcja SELECT następnie pobiera wszystkie kolumny zestawu zestaw wierszy, dostarczonych przez 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 Gonzlez">
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" 
           OrderDate="1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @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

Jest to wynikiem:

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

Deseń XPath określony jako ColPattern można także określić mapowanie elementów XML do kolumn zestawu zestaw wierszy.To wyniki w mapowaniu zorientowany na element.W poniższym przykładzie dokument XML <CustomerID> i <OrderDate> są podelementy z <Orders> elementu.Ponieważ ColPattern zastępuje mapowanie określone w flagi parametru flagi parametr nie jest określony w 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 Gonzlez">
   <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.Łączenie zorientowany atrybut i zorientowany na element Mapowanie

W tym przykładzie flagi parametr jest zestaw do 3 i wskazuje, że zarówno zorientowany atrybut i zorientowany na element Mapowanie będą stosowane.W tym przypadek najpierw stosowany jest zorientowany atrybut mapowania, a następnie zorientowany na element mapowanie jest stosowana dla wszystkich kolumn, które jeszcze nie zostały omówione.

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 Gonzlez</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

Wynik

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

Mapowanie zorientowany atrybut jest stosowany dla IDklienta.Nie ma żadnych przedstawiciel atrybut w <Customer> element.Dlatego mapowanie zorientowany na element jest stosowany.

D.Określanie funkcja XPath text() jako ColPattern

Dokument XML w tym przykładzie składa się z <Customer> i <Order> elementów.Instrukcja OPENXML pobiera zestawu zestaw wierszy, która składa się z oid atrybut z <Order> element, identyfikator obiektu nadrzędnego węzła identyfikowane przez rowpatternoraz ciąg wartości typu liść zawartości elementu.

Pierwszy, sp_xml_preparedocument procedura składowana jest wywoływana uzyskiwania dojścia do dokumentu.Uchwyt ten dokument jest przekazywana do OPENXML.

Instrukcja OPENXML ilustruje następujące operacje:

rowpattern (/ root, odbiorcy/zamówienia) identyfikuje <Order> węzły przetwarzania.
Flagi wartość parametru jest zestaw do 1 i wskazuje zorientowany atrybut mapowania.W wyniku mapowania atrybutów XML do kolumn zestawu zestaw wierszy zdefiniowanych w SchemaDeclaration.
W SchemaDeclaration w klauzula WITH oid i kwotę zestaw wierszy kolumna nazwy zgodne z odpowiadających im nazw atrybut XML.Dlatego ColPattern nie określono parametru.Dla komentarz kolumna w zestawie zestaw wierszy funkcja XPath text(), określony jako ColPattern.Spowoduje to zastąpienie zorientowany atrybut mapowania określone w flagi, a kolumna zawiera wartości typu liść ciąg zawartości elementu.

Instrukcja SELECT następnie pobiera wszystkie kolumny zestawu zestaw wierszy, dostarczonych przez 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

Jest to wynikiem:

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

E.Określanie Nazwa_tabeli w klauzula WITH

Określa, w tym przykładzie Nazwa_tabeli w klauzula WITH zamiast SchemaDeclaration.Jest to przydatne, jeśli masz tabela, która ma odpowiednią strukturę i żadnych wzorków kolumna ColPattern parametru, są wymagane.

Dokument XML w tym przykładzie składa się z <Customer> i <Order> elementów.Instrukcja OPENXML pobiera informacje o zamówieniach w zestawie zestaw wierszy trzy kolumna (oid, Data, i kwotę) z dokumentu XML.

Pierwszy, sp_xml_preparedocument procedura składowana jest wywoływana uzyskiwania dojścia do dokumentu.Uchwyt ten dokument jest przekazywana do OPENXML.

Instrukcja OPENXML ilustruje następujące operacje:

rowpattern (/ root, odbiorcy/zamówienia) identyfikuje <Order> węzły przetwarzania.
Nie ma żadnych SchemaDeclaration w klauzula WITH.Zamiast tego jest określona nazwa tabela.Dlatego schematu tabela jest używany jako schemat zestawu zestaw wierszy.
Flagi wartość parametru jest zestaw do 1 i wskazuje zorientowany atrybut mapowania.Dlatego atrybutów elementów, identyfikowane przez rowpattern, mapy do zestawu zestaw wierszy kolumn o tej samej nazwie.

Instrukcja SELECT następnie pobiera wszystkie kolumny zestawu zestaw wierszy, dostarczonych przez 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

Jest to wynikiem:

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.Uzyskiwanie wyników w formacie tabela krawędzi

W tym przykładzie klauzula WITH nie jest określona w instrukcja OPENXML.W wyniku zestaw wierszy wygenerowanych przez OPENXML ma format tabela krawędzi.Instrukcja SELECT zwraca wszystkie kolumny w tabela krawędzi.

Przykładowy dokument XML w przykładzie składa się z <Customer>, <Order>, i <OrderDetail> elementów.

Pierwszy, sp_xml_preparedocument procedura składowana jest wywoływana uzyskiwania dojścia do dokumentu.Uchwyt ten dokument jest przekazywana do OPENXML.

Instrukcja OPENXML ilustruje następujące operacje:

rowpattern (/ ROOT/odbiorcy) identyfikuje <Customer> węzły przetwarzania.
Klauzula WITH nie jest zapewniona.Dlatego OPENXML zwraca zestaw wierszy w formacie tabela krawędzi.

Instrukcja SELECT następnie pobiera wszystkie kolumny w tabela krawędzi.

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 Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate=
           "1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer')

EXEC sp_xml_removedocument @docHandle

Wyniki są zwracane jako krawędzi tabela.Można napisać kwerend tabela krawędź, aby uzyskać informacje.Na przykład:

Następująca kwerenda zwraca liczbę klienta węzłów w dokumencie.Ponieważ nie określono wyrażenia WITH, OPENXML zwraca krawędź tabela.Instrukcja SELECT kwerendy tabela krawędzi.
```
SELECT count(*)
FROM OPENXML(@docHandle, '/')
WHERE localname = 'Customer'
```

Następująca kwerenda zwraca lokalne nazwy węzłów XML typu elementu.

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

G.Określanie kończąc atrybut rowpattern

Dokument XML w tym przykładzie składa się z <Customer>, <Order>, i <OrderDetail> elementów.Instrukcja OPENXML pobiera informacje o szczegóły zamówienia w zestawie zestaw wierszy trzy kolumna (IDProduktu, Ilość, i IDZamówienia) z dokumentu XML.

Pierwszy, sp_xml_preparedocument jest nazywany uzyskiwania dojścia do dokumentu.Uchwyt ten dokument jest przekazywana do OPENXML.

Instrukcja OPENXML ilustruje następujące operacje:

rowpattern (/ ROOT/Customer/Order/OrderDetail/@ProductID) kończy się atrybut XML IDProduktu.W wynikowym zestawie zestaw wierszy wiersz jest tworzony dla każdego węzła atrybut zaznaczone w dokumencie XML.
W tym przykładzie flagi nie określono parametru.Zamiast tego mapowania są określone przez ColPattern parametru.

W SchemaDeclaration w klauzula WITH ColPattern określony jest również z ColName i ColType Parametry.Opcjonalna ColPattern jest wzorcem wyrażenie XPath, aby wskazać następujące:

Deseń XPath (.) określona jako ColPattern dla IdentyfikatorProduktu kolumna w zestawie zestaw wierszy identyfikuje węzeł kontekstu, bieżącego węzła.Na rowpattern określony, jest IDProduktu atrybut <OrderDetail> element.
ColPattern, ./@Quantity, określonego dla Ilość identyfikuje kolumna w zestawie zestaw wierszy Ilość atrybut nadrzędny, <OrderDetail>, węzła Węzeł kontekstu <IDProduktu>.
Podobnie ColPattern, ./../@OrderID, określonego dla OID identyfikuje kolumna w zestawie zestaw wierszy IDZamówienia atrybut nadrzędny, <Order>, węzła nadrzędnego węzła.Węzeł nadrzędny jest <OrderDetail>, a węzeł kontekstu jest <ProductID>.

Instrukcja SELECT następnie pobiera wszystkie kolumny zestawu zestaw wierszy, dostarczonych przez 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 Gonzlez">
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" OrderDate=
           "1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @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

Jest to wynikiem:

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

H.Określanie dokumentu XML, który ma wiele węzłów tekstowych

Jeśli masz wiele węzłów tekstowych w XML dokumentu, zaznacz instrukcja z ColPattern, text(), zwraca tylko pierwszy węzeł tekstowy, zamiast je wszystkie.Na przykład:

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

Instrukcja SELECT zwraca t w wyniku i nie TaU.

I.Określanie typu danych xml w klauzula WITH

W klauzula WITH deseń kolumna, które jest mapowane do xml kolumna Typ danych, czy wpisana lub bez typu, musi zwrócić sekwencji puste lub sekwencja elementów przetwarzania instrukcji, węzły tekstu i komentarze.Rzutować dane xml typu danych.

W poniższym przykładzie zawiera deklaracja schematu tabela w klauzula WITH xml wpisz kolumn.

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

W szczególności są przekazywanie xml typ zmiennej (@ x), aby sp_xml_preparedocument() funkcja.

Jest to wynikiem:

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>

Uwaga następujące w wyniku:

Dla lname kolumna varchar(30) typu, jej wartość jest pobierana z odpowiednimi <lname> element.
Dla xmlname kolumna xml typu, tego samego elementu nazwy są zwracane jako jego wartość.
Flaga jest zestaw do 10.10 Oznacza 2 + 8, gdzie zorientowany na element mapowanie wskazuje 2 i 8 wskazuje, że tylko niewykorzystana danych XML powinny zostać dodane do Kolumna przepełnienia, określone w klauzula WITH.Jeśli użytkownik zestaw flagę 2, cały dokument XML jest kopiowany do Kolumna przepełnienia, określonego w klauzula WITH.
przypadek kolumn w klauzula WITH maszynowy kolumnie XML i XML wystąpienie nie potwierdzi schematu, zwracany jest błąd.

J.Pobieranie poszczególnych wartości z atrybutów wielowartościowych

Dokument XML może mieć atrybuty wielowartościowe.Na przykład IDREFS atrybut może być wielowartościowe.W dokumencie XML wartości atrybut wielowartościowego są określone jako ciąg, wartości oddzielone spacją.W następującym dokumencie XML uczestniczy w atrybut <studentów> element i attendedBy atrybut <klasy> są wielowartościowe.Pobieranie poszczególnych wartości z wielowartościowy atrybut XML i przechowywanie każdą wartość w osobnym wierszu bazy danych wymaga dodatkowej pracy.W tym przykładzie pokazano proces.

Ten przykładowy dokument XML składa się z następujących elementów:

<Studentów>

Identyfikatora (identyfikator studenta), nazwę, i uczestniczy w atrybuty.Uczestniczy w jest atrybut wielowartościowy atrybut.
<Klasa>

Identyfikatora (identyfikator klasy), nazwę, i attendedBy atrybuty.AttendedBy atrybut jest wielowartościowy atrybut.

Uczestniczy w atrybut w <studentów> i attendedBy atrybut w <klasy> reprezentują m:n relacji między tabelami, studentów i klasy.Student może trwać wiele klas i klasa może mieć wiele studentów.

Załóżmy, że chcesz shred tego dokumentu i zapisz go w bazie danych, jak pokazano w poniższym:

Zapisz <studentów> dane w tabela studentów.
Zapisz <klasy> dane w tabela kursów.
Zapisz m:n relacji danych między studentów i klasy, w tabela CourseAttendence.wyodrębnić wartości wymaga dodatkowej pracy.Aby pobrać te informacje i przechowywać go w tabela, należy użyć tych procedur przechowywanych:
- Insert_Idrefs_Values
  
  Wstawia wartości Identyfikator kursu i identyfikator studenta w tabela CourseAttendence.
- Extract_idrefs_values
  
  Wyodrębnia poszczególnych studentów identyfikatory z każdego <kursu> elementu.Krawędź tabela jest używane do pobierania tych wartości.

Następujące czynności:

-- 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

J.Pobieranie pliku binarnego z base64 encoded danych XML

Dane binarne często znajduje się w danych XML w formacie Base64.Shred się tym XML przy użyciu OPENXML, otrzymujesz dane base64 encoded.W tym przykładzie pokazuje, jak można napisać funkcja CLR konwertować base64 encoded danych binarnych.

Wykonaj następujące kroki, aby utworzyć funkcja CLR próbki:

Poniższy kod C# konwertuje base64 encoded danych binarnych.Zapisać kod w pliku base64.cs w folderze c:\temp.

using System;
 public class Sample
{
    public static byte[] ConvertBase64ToBinary(string base64String)
    {
        if (base64String == null) 
        {
            return null;
        }
        return Convert.FromBase64String(base64String);
    }
}

Skompilować kod i utworzyć bibliotekę, base64.dll.
```
csc /target:library base64.cs 
```
Za pomocą Tworzenie zestawu (języka Transact-SQL), Utwórz moduł zarządzanej aplikacji.Należy zastąpić "komputer_użytkownika" z nazwy komputera.
```
drop assembly asbly_base64
go
create assembly asbly_base64 from '\\your_computer\c$\temp\base64.dll'
go
```
Jeśli nie masz włączona obsługa środowiska CLR, można wykonać następujące instrukcje umożliwiające CLR:
```
sp_configure 'clr enabled', 1
reconfigure with override
go
```
Utwórz funkcja CLR, która pobiera dane base64 encoded jako dane wejściowe i zwraca dane binarne.W tym przez wywołanie funkcja w module zarządzanej aplikacji.
```
create function fn_getBinaryFromBase64String( @s nvarchar(max) )
  returns varbinary(max)
as external name asbly_base64.Sample.ConvertBase64ToBinary
go
```

Funkcja CLR można teraz sprawdzić, wykonując następujące czynności:

Utwórz tabela z danymi binarnymi próbki.
Za pomocą kwerendy dla XML i opcji BINARY BASE64 konstruowania XML zawierający dane binarne zakodowane w formacie base64.
Shred XML przy użyciu OPENXML.Dane zwrócone przez OPENXML będzie danych algorytmem base64.Następnie wywołać funkcja CLR, aby przekonwertować z powrotem binarny.

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 fn_ getBinaryFromBase64String 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, 
         dbo.fn_getBinaryFromBase64String(Col2) as BinaryCol
FROM     openxml(@h, '/T') 
         with (Col1 integer, Col2 nvarchar(max)) 
exec sp_xml_removedocument @h
go

Jest to wynikiem.Dane binarne, zwracany jest oryginalne dane binarne w tabela T.

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

Zobacz także

Odwołanie

sp_xml_preparedocument (języka Transact-SQL)

sp_xml_removedocument (języka Transact-SQL)

OPENXML (Transact-SQL)

Badanie XML przy użyciu OPENXML

Last updated on 2011-07-22

Udostępnij przez

Przy użyciu OPENXML

Przykłady

A.Wykonywanie prostych wybierz instrukcja z OPENXML

B.Określanie mapowania między zestawu zestaw wierszy, kolumn i atrybutów XML i elementy ColPattern

C.Łączenie zorientowany atrybut i zorientowany na element Mapowanie

D.Określanie funkcja XPath text() jako ColPattern

E.Określanie Nazwa_tabeli w klauzula WITH

F.Uzyskiwanie wyników w formacie tabela krawędzi

G.Określanie kończąc atrybut rowpattern

H.Określanie dokumentu XML, który ma wiele węzłów tekstowych

I.Określanie typu danych xml w klauzula WITH

J.Pobieranie poszczególnych wartości z atrybutów wielowartościowych

J.Pobieranie pliku binarnego z base64 encoded danych XML

Zobacz także

Odwołanie

Dodatkowe źródła