Artykuł
07/06/2010

Using OPENXML

W przykładach w tym temacie pokazują, w jaki sposób OPENXML jest używany do utworzenia widoku zestawu zestaw wierszy z dokumentu XML.Aby uzyskać informacje na temat składni OPENXML zobacz OPENXML (Transact-SQL). W przykładach wyświetlać wszystkie aspekty OPENXML, ale nie określono metaproperties OPENXML.Aby uzyskać więcej informacji na temat sposobu określania metaproperties w OPENXML zobacz Określanie Metaproperties w OPENXML.

Przykłady

Pobierania danych, rowpattern jest używany do identyfikowania węzłów w dokumencie XML, które określają wierszy.Ponadto rowpattern jest wyrażona w język wzorca wyrażenie XPath, który jest używany w celu wykonania MSXML XPath.Na przykład jeśli deseń jest zakończony elementu lub atrybutu, wiersz jest tworzony dla każdego węzła element lub atrybut jest wybierany przez rowpattern.

The flags value provides default mapping.Jeśli nie ColPattern określona w SchemaDeclaration, mapowanie, określone w flagi przyjęto, że.The flags value is ignored if ColPattern is specified in SchemaDeclaration.Określony ColPattern Określa mapowanie, zorientowany na atrybut lub element zorientowany, a także zachowanie przepełnienie i nieużywanej danych.

A.Wykonywanie prostych instrukcję SELECT z OPENXML

Dokument XML w tym przykładzie składa się z <Customer>, <Order>, a <OrderDetail> elementy. Instrukcja OPENXML pobiera informacje o kliencie w zestawu zestaw wierszy z dwóch kolumn Identyfikator klienta and Nazwa kontaktu, z dokumentu XML.

Pierwszy, sp_xml_preparedocument przechowywana procedura nosi nazwę uzyskiwania dojścia do dokumentu.Uchwyt ten dokument jest przekazywana do OPENXML.

Instrukcja OPENXML ilustruje następujące czynności:

rowpattern (/ ROOT/Customer) identyfikuje węzłów <Customer> do przetworzenia.
The flags parameter value is zestaw to 1 and indicates atrybut-centric mapping.W rezultacie mapowania atrybutów XML do kolumn w zestawie zestaw wierszy zdefiniowanych w SchemaDeclaration.
W SchemaDeclaration, w klauzula WITH, określony ColName wartości pasują do siebie odpowiadających im nazw atrybut XML.Dlatego też ColPattern parametr nie zostanie określony w SchemaDeclaration.

Instrukcja SELECT, następnie pobiera wszystkie kolumny w przewidzianym OPENXML zestawu zestaw wierszy.

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

Oto wynik:

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

Ponieważ <Customer> elementy nie ma wymaganych wszelkie podelementy tej samej instrukcja SELECT jest wykonywane z flagi zestaw to 2 , aby wskazać, zorientowany na element Mapowanie wartości Identyfikator klienta and Nazwa kontaktu dla obu klientów są zwracane w postaci wartości NULL.

Mogą również być xmlDocument @ XML Typ 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

Oto wynik:

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

Należy zauważyć, że dojście dokument zwrócony przez sp_xml_preparedocument jest ważne przez czas trwania partia i nie sesja.

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

W tym przykładzie pokazano, jak deseń wyrażenie XPath jest określona w opcjonalnej ColPattern parametr ustanowienie 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>, a <OrderDetail> elementy. Instrukcja OPENXML pobiera klienta i informacje o zamówieniach jako (zestaw wierszyIdentyfikator klienta, DataZamówienia., IdentyfikatorProduktu, and Ilość) z dokumentu XML.

Pierwszy, sp_xml_preparedocument przechowywana procedura nosi nazwę uzyskiwania dojścia do dokumentu.Uchwyt ten dokument jest przekazywana do OPENXML.

Instrukcja OPENXML ilustruje następujące czynności:

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

Na ilustracji flagi wartość parametru jest zestaw do 2 i wskazuje mapowania zorientowane na elemencie.Jednak mapowania określonych w ColPattern zastępuje to mapowanie.Oznacza to określony wzorzec wyrażenie XPath w ColPattern mapuje atrybutów kolumny w zestawie zestaw wierszy.To wyniki zorientowany atrybut mapowania.

W SchemaDeclaration, w klauzula WITH, ColPattern również zostanie użytyColName and ColType parametry.Opcjonalny ColPattern jest określony wzorzec XPath i wskazuje następujące:

The OrderID, CustomerID, and OrderDate columns in the zestaw wierszy map to the attributes of the parent of the nodes identified by rowpattern, and rowpattern identifies the <OrderDetail> nodes.Dlatego też Identyfikator klienta and DataZamówienia. Mapa kolumn Identyfikator klienta and DataZamówienia. atrybuty <Order> elementu.
The ProdID and Qty columns in the zestaw wierszy map to the ProductID and Quantity attributes of the nodes identified in rowpattern.

Instrukcja SELECT, następnie pobiera wszystkie kolumny w przewidzianym OPENXML zestawu zestaw wierszy.

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

Oto wynik:

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

Określone jako deseń XPath ColPattern można również określić mapować elementy XML do kolumn zestawu zestaw wierszy.Wynikiem tego mapowania zorientowane na elemencie.W poniższym przykładzie dokument XML <CustomerID> i <OrderDate> są podelementy z <Orders> element. Ponieważ ColPattern mapowanie, określone w są zastępowaneflagi parametrflagi parametr nie zostanie 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 mapowania

W tym przykładzie flagi ustawiono parametr 3 i przedstawia zostanie zastosowane zorientowane na element i zorientowany atrybut mapowania.W tym przypadek najpierw stosowany jest zorientowane na atrybut mapowania, a następnie zorientowane na element mapowanie jest stosowana dla wszystkich kolumn, które nie zostały jeszcze 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 zorientowane na atrybut jest stosowana dla Identyfikator klienta.Brak nie Nazwa kontaktu atrybut <Customer> elementu.Dlatego mapowanie zorientowane na element jest stosowana.

D.Określając wyrażenie XPath funkcja text() jako ColPattern

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

Pierwszy, sp_xml_preparedocument przechowywana procedura nosi nazwę uzyskiwania dojścia do dokumentu.Uchwyt ten dokument jest przekazywana do OPENXML.

Instrukcja OPENXML ilustruje następujące czynności:

rowpattern (/ główny/odbiorcy/zamówienia) identyfikuje węzłów <Order> do przetworzenia.
The flags parameter value is zestaw to 1 and indicates atrybut-centric mapping.W wyniku mapowania atrybutów XML do zdefiniowane w kolumnach zestaw wierszy SchemaDeclaration.
W SchemaDeclaration w klauzula WITH, Identyfikator obiektu and Kwota nazw kolumn zestawu zestaw wierszy odpowiada odpowiadających im nazw atrybut XML.Dlatego też ColPattern parametr nie zostanie określony.Dla komentarz Kolumna zestawu zestaw wierszy, funkcja wyrażenie XPath, **Text()**określony jako ,ColPattern.Zastępuje to zorientowany atrybut mapowania, określone w flagi, a kolumna zawiera ciąg wartości typu liść zawartości elementu.

Instrukcja SELECT, następnie pobiera wszystkie kolumny w przewidzianym OPENXML zestawu zestaw wierszy.

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

Oto wynik:

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 TableName w klauzula WITH

Określa, w tym przykładzie TableName w klauzula WITH zamiast SchemaDeclaration.Jest to przydatne w przypadku tabela, która ma strukturę, która ma a nie deseni kolumna ColPattern parametr, są wymagane.

Dokument XML w tym przykładzie składa się z <Customer> i <Order> elementy. Instrukcja OPENXML pobiera informacje o zamówieniach w (trzy kolumna zestawu zestaw wierszy.Identyfikator obiektu, Data, and Kwota) z dokumentu XML.

Pierwszy, sp_xml_preparedocument przechowywana procedura nosi nazwę uzyskiwania dojścia do dokumentu.Uchwyt ten dokument jest przekazywana do OPENXML.

Instrukcja OPENXML ilustruje następujące czynności:

rowpattern (/ główny/odbiorcy/zamówienia) identyfikuje węzłów <Order> do przetworzenia.
Brak nie SchemaDeclaration w klauzula WITH.Zamiast tego jest określona nazwa tabela.Dlatego schematu tabela służy jako schemat zestawu zestaw wierszy.
The flags parameter value is zestaw to 1 and indicates atrybut-centric mapping.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 w przewidzianym OPENXML zestawu zestaw wierszy.

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

Oto wynik:

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ślone w instrukcja OPENXML.W rezultacie zestaw wierszy wygenerowanych przez OPENXML ma format krawędzi tabela.Instrukcja SELECT zwraca wszystkie kolumny w tabela krawędzi.

Przykładowy dokument XML w tym przykładzie składa się z <Customer>, <Order>, a <OrderDetail> elementy.

Pierwszy, sp_xml_preparedocument przechowywana procedura nosi nazwę uzyskiwania dojścia do dokumentu.Uchwyt ten dokument jest przekazywana do OPENXML.

Instrukcja OPENXML ilustruje następujące czynności:

rowpattern (/ ROOT/Customer) identyfikuje węzłów <Customer> do przetworzenia.
Klauzula WITH nie jest wymagane.Dlatego też 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

Wynik jest zwracany w tabela krawędzi.Można zapisywać kwerendy tabela krawędzi w celu uzyskania informacji.Na przykład:

Następująca kwerenda zwraca liczbę Odbiorcy węzły w dokumencie.Klauzula WITH nie jest określony, OPENXML zwraca tablicę krawędzi.Instrukcja SELECT kwerendy tabela krawędzi.
```
SELECT count(*)
FROM OPENXML(@docHandle, '/')
WHERE localname = 'Customer'
```

Następująca kwerenda zwraca lokalnych nazw węzłów XML typu elementu.

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

G.Określanie rowpattern kończące się znakiem atrybut

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

Pierwszy, sp_xml_preparedocument nazywa się uzyskać dojścia do dokumentu.Uchwyt ten dokument jest przekazywana do OPENXML.

Instrukcja OPENXML ilustruje następujące czynności:

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 wybranego dokumentu XML.
W tym przykładzie flagi parametr nie zostanie określony.Zamiast tego mapowania są określane przez ColPattern parametr.

W SchemaDeclaration w klauzula WITH, ColPattern również zostanie użytyColName i ColType Parametry. Opcjonalny ColPattern jest wzorcem wyrażenie XPath, aby wskazać następujące:

Wyrażenie XPath (wzorca**.** ) określony jako ColPattern for the IdentyfikatorProduktu kolumna w zestawie zestaw wierszy identyfikuje węzeł kontekstu, bieżącego węzła.Według rowpattern określony jestIDProduktu atrybut <OrderDetail> elementu.
The ColPattern, ../@Quantity, specified for the Qty kolumna in the zestaw wierszy identifies the Quantity atrybut of the parent, <OrderDetail>, node of the context node, <ProductID>.
Podobnie ColPattern, ../../@OrderID, określony dla IDENTYFIKATOR OBIEKTU kolumn w zestawie zestaw wierszy identyfikuje Identyfikator zamówienia atrybut z obiektu nadrzędnego, <Order>, węzła nadrzędnego węzła kontekstu.Węzeł nadrzędny <OrderDetail>, a węzeł kontekstu jest <ProductID>.

Instrukcja SELECT, następnie pobiera wszystkie kolumny w przewidzianym OPENXML zestawu zestaw wierszy.

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

Oto wynik:

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

H.Określanie dokumentu XML zawierający wiele węzłów tekstu

Jeśli masz wiele węzłów tekstu w XML dokumentu, instrukcję SELECT z ColPattern, Text(), zwraca tylko pierwszą tekst węzła, 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 TaU.

I.Określanie typu danych xml w klauzula WITH

W klauzula WITH, wzór kolumn, które jest mapowane do XML kolumna o typie danych, niezależnie od tego, czy pismo czy bez typu, musi zwracać sekwencję puste lub sekwencji elementów przetwarzania instrukcji, węzły tekstu i komentarze.Rzutować dane XML typ danych.

W poniższym przykładzie zawiera deklaracja schematu tabela w klauzula WITH XML typu kolumny.

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, którego przekazywanie XML typ zmiennej (@ x) sp_xml_preparedocument() funkcja.

Oto wynik:

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>

Należy uwzględnić następujące z wyników:

Dla lname kolumnavarchar(30) typu, jego wartość jest pobierana z odpowiedniego elementu <lname> .
Dla xmlname kolumnaXML typu, tego samego elementu nazwy są zwracane jako jego wartość.
Flaga jest zestaw do 10.10 Oznacza 2 + 8, gdzie 2-wskazuje, zorientowany na element mapowania i 8-wskazuje, że do Kolumna przepełnienia, określonych w klauzula WITH należy dodać tylko nieużywanej danych XML.Jeżeli flaga jest ustawiona na 2, cały dokument XML jest kopiowany do Kolumna przepełnienia, określone w klauzula WITH.
przypadek kolumn w klauzula WITH jest wpisywany kolumna XML i wystąpienie XML nie potwierdzi w schemacie, zwracany jest błąd.

J.Pobiera pojedyncze wartości z atrybuty wielowartościowe

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 znaków, przy użyciu wartości oddzielone spacją.W następującym dokumencie XML uczestniczyatrybut elementu > < studentów i attendedByatrybut Class > < są wielowartościowych. Pobiera pojedyncze wartości z wielowartościowego atrybut XML i przechowywania każdej wartości w oddzielnym wierszu w bazie danych wymaga dodatkowej pracy.W tym przykładzie pokazano proces.

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

<Dla studentów>

The id (student ID), name, and attends attributes.The attends atrybut is a multivalued atrybut.
<Klasa>

The id (class ID), name, and attendedBy attributes.The attendedBy atrybut is a multivalued atrybut.

The attends atrybut in <Student> and the attendedBy atrybut in <Class> represent a m:n relationship between the Student and Class tables. Student może zająć wiele klas i klasa może mieć wiele studentów.

Załóżmy, że chcesz Podrasowane ten dokument i zapisać go w bazie danych, jak pokazano w poniższym:

Zapisz <Dla studentów> dane w tabela Studenci.
Zapisz <Klasa> dane w tabela kursy.
Zapisz m:n związek danych, między, studentów i klasy, w tabela CourseAttendence.wyodrębnić wartości wymaga dodatkowej pracy.Aby uzyskać te informacje i umieszczenie go w tabela, należy użyć tych procedur przechowywanych:
- Insert_Idrefs_Values
  
  Wstawia wartości Identyfikator kursu i ID studenta w tabela CourseAttendence.
- Extract_idrefs_values
  
  Wyodrębnia z każdej student poszczególnych identyfikatorów <Kurs> element. W celu pobrania tych wartości jest używany do krawędzi tabela.

Poniżej przedstawiono kroki:

-- 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.Pobiera plik binarny z base64 zakodowane dane w formacie XML

Dane binarne często znajduje się w języku XML przy użyciu kodowania base64.Za pomocą OPENXML Podrasowane się tym XML, otrzymujesz dane zakodowane w formacie base64.W tym przykładzie pokazuje, jak pisać funkcja środowiska CLR konwertować zakodowany base64 danych binarnych.

Wykonaj następujące kroki, aby utworzyć funkcja środowiska CLR przykładowe:

Poniższy kod C# konwertuje zakodowany base64 danych binarnych.Zapisz jego kod do pliku, base64.cs w folderze c:\tymczasowy.

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

Skompiluj kod i utworzyć bibliotekę, base64.dll.
```
csc /target:library base64.cs 
```
Za pomocą CREATE MONTAŻOWYCH (języka Transact-SQL), Utwórz moduł zarządzanej aplikacji.Należy zastąpić nazwą komputera "komputer_użytkownika".
```
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, aby włączyć środowisko CLR:
```
sp_configure 'clr enabled', 1
reconfigure with override
go
```
Tworzenie funkcja środowiska CLR, która pobiera dane base64 zakodowane 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 środowiska CLR można sprawdzić teraz, wykonując następujące czynności:

Tworzenie tabela z danymi binarnymi próbki.
Za pomocą kwerendy FOR XML i opcji BINARY BASE64 konstruowania XML, który zawiera dane binarne zakodowane w formacie base64.
Podrasowane XML przy użyciu OPENXML.Dane zwrócone przez OPENXML będą dane zakodowane w formacie base64.Następnie należy wywołać funkcja środowiska CLR, aby przekonwertować ją 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 wynik.Dane binarne, zwracana jest oryginalna dane binarne w tabela T.

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

Using OPENXML

Przykłady

A.Wykonywanie prostych instrukcję SELECT z OPENXML

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

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

D.Określając wyrażenie XPath funkcja text() jako ColPattern

E.Określanie TableName w klauzula WITH

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

G.Określanie rowpattern kończące się znakiem atrybut

H.Określanie dokumentu XML zawierający wiele węzłów tekstu

I.Określanie typu danych xml w klauzula WITH

J.Pobiera pojedyncze wartości z atrybuty wielowartościowe

J.Pobiera plik binarny z base64 zakodowane dane w formacie XML

See Also

Reference

Other Resources

Dodatkowe zasoby