Makale
07/26/2011

openxml kullanma

Bu konudaki örneklerde openxml bir satır kümesi xml belgesinin görünümünü oluşturmak için nasıl kullanıldığını gösterir.openxml sözdizimi hakkında bilgi için bkz: openxml (Transact-sql).Örnekler, openxml tüm yönlerini göstermek ancak metaproperties openxml belirtin.Metaproperties openxml belirtme hakkında daha fazla bilgi için bkz: Metaproperties openxml belirtme.

Örnekler

Veri alma, rowpattern xml belgesindeki satırları belirlemek düğümleri tanımlamak için kullanılır.Ayrıca, rowpattern msxml XPath uygulamasında kullanılan XPath desen dili ifade edilir.Desen bir öðe ya da öznitelik sona ererse, örneğin, bir satır olarak seçili her öðe ya da öznitelik düğümü için oluşturulan rowpattern.

The flags value provides default mapping.Yok, ColPattern belirtilen SchemaDeclaration, belirtilen eşleme flags kabul edilir.The flags value is ignored if ColPattern is specified in SchemaDeclaration.Belirtilen ColPattern öğesi merkezli, ya da öznitelik merkezli eşleme ve ayrıca taşma ve unconsumed veri davranışını belirler.

A.openxml ile basit bir deyim yürütme

Bu örnek xml belgesinde oluşur <Customer>, <Order>, ve <OrderDetail> öğeleri.openxml deyim iki - müşteri bilgilerini alırsütun satır kümesi, CustomerID ve KişiAdı, xml belgesindeki.

İlk olarak, sp_xml_preparedocument saklı yordam, bir belge tanıtıcı elde etmek için verilir.Bu belge tutamacı için openxml geçirilir.

openxml deyim aşağıda gösterilmiştir:

rowpattern tanımlar (/ root/müşteri) <Customer> düğümlerini işlemek için.
The flags parameter value is set to 1 and indicates attribute-centric mapping.Sonuç olarak, xml özniteliği tanımlanmış satır kümesi içindeki sütunları eşleştirmek SchemaDeclaration.
De SchemaDeclaration, WITH yan tümce tümce tümce, belirtilen ColName değerlerle aynı karşılık gelen xml öznitelik adları.Bu nedenle, ColPattern parametresi belirtilen değil SchemaDeclaration.

deyim içindeki tüm sütunları alır satır kümesi openxml tarafından sağlanan.

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

Bu sonucu verir:

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

Çünkü <Customer> öğeleri ile aynı deyim yürütüldüğünde, tüm alt öğeleri yoktur bayrakları küme için 2 öğesi merkezli eşleme değerleri belirtmek için CustomerID ve KişiAdı için müşterileri boş olarak döndürülür.

@ XmlDocument olarak da olabilir xml türü veya (n)varchar(max) türü.

If <CustomerID> and <ContactName> in the XML document are subelements, the element-centric mapping retrieves the values.

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

Bu sonucu verir:

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

Not belge tanıtıcı tarafından döndürülen sp_xml_preparedocument toplu iş iş ve oturum süresince geçerli olur.

B.Satır kümesi sütunları ve xml öznitelikleri ve öğeleri arasında eşleştirme ColPattern belirtme

Nasıl XPath desen isteğe bağlı belirtilen bu örnek gösterir ColPattern satır kümesi sütunları ve xml öznitelikleri ve öğeleri arasındaki eşleşme sağlamak için parametre.

Bu örnek xml belgesinde oluşur <Customer>, <Order>, ve <OrderDetail> öğeleri.openxml deyim bir satır kümesi olarak müşteri ve sipariş bilgileri alır (CustomerID, SiparişTarihi, ProdID, ve Miktar) xml belgesindeki.

İlk olarak, sp_xml_preparedocument saklı yordam, bir belge tanıtıcı elde etmek için verilir.Bu belge tutamacı için openxml geçirilir.

openxml deyim aşağıda gösterilmiştir:

rowpattern (/ kök/Müşteri/sipariş/OrderDetail) tanımlayan <OrderDetail> düğümlerini işlemek için.

Resimde için flags parametre değeri küme 2 ve öğe merkezli eşleme gösterir.Eşleme ancak belirtilen ColPattern bu eşleştirme üzerine yazar.Diğer bir deyişle, belirtilen XPath desen ColPattern satır kümesi sütunlar öznitelikleri için eşleştirir.Bu sonuçlar , öznitelik merkezli eşleme.

De SchemaDeclaration, WITH yan tümce tümce tümcesinde ColPattern ile de belirtilen ColName ve ColType parametreleri.İsteğe bağlı ColPattern olduğu belirtilen XPath desen ve aşağıdakileri gösterir:

The OrderID, CustomerID, and OrderDate columns in the rowset map to the attributes of the parent of the nodes identified by rowpattern, and rowpattern identifies the <OrderDetail> nodes.Bu nedenle, CustomerID ve SiparişTarihi sütunları eşleştirmek için CustomerID ve SiparişTarihi özniteliklerini <Order> öğesi.
The ProdID and Qty columns in the rowset map to the ProductID and Quantity attributes of the nodes identified in rowpattern.

deyim içindeki tüm sütunları alır satır kümesi openxml tarafından sağlanan.

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

Bu sonucu verir:

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

Olarak belirtilen XPath desen ColPattern satır kümesi sütun xml öğeleri eşlemek için de belirtilebilir.Bu sonuçlar , öğe merkezli eşleme.Aşağıdaki örnekte, xml belgesini <CustomerID> ve <OrderDate> , alt öğeleri olan <Orders> öğesi.Çünkü ColPattern belirtilen eşleme üzerine yazar flags parametresi, flags parametresi openxml belirtilmemiş.

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.öznitelik merkezli ve öğe merkezli eşleme birleştirme

Bu örnekte, flags parametresi küme 3 ve öznitelik merkezli ve öğe merkezli eşleme uygulanacağını gösterir.Bu durum, öznitelik merkezli eşleme ilk olarak uygulanır ve öğe merkezli eşleme için tüm sütunları henüz çözüldüğünü sonra uygulanır.

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

Sonuç budur

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

Öznitelik merkezli eşleme için uygulanan CustomerID.Yok yok KişiAdı içindeki öznitelik <Customer> öğesi.Bu nedenle, öğe merkezli eşleme uygulanması gerekir.

D.Text() işlev XPath ColPattern belirtme

Bu örnek xml belgesinde oluşur <Customer> ve <Order> öğeleri.openxml deyim oluşan bir satır kümesi alır oid gelen öznitelik <Order> öğe tarafından tanımlanan düğümün üst Kimliğini rowpatternve yaprak değerini dize öğesinin içeriği.

İlk olarak, sp_xml_preparedocument saklı yordam, bir belge tanıtıcı elde etmek için verilir.Bu belge tutamacı için openxml geçirilir.

openxml deyim aşağıda gösterilmiştir:

rowpattern (/ kök/Müşteri/sipariş) tanımlayan <Order> düğümlerini işlemek için.
The flags parameter value is set to 1 and indicates attribute-centric mapping.Sonuç olarak, xml özniteliği tanımlanmış satır kümesi sütunları eşleştirmek SchemaDeclaration.
De SchemaDeclaration WITH yan tümce tümce tümcesinde oid ve Tutar satır kümesi sütun adlarını karşılık gelen xml öznitelik adlarının eşleşmesi.Bu nedenle, ColPattern parametresi belirtilmemiş.İçin Yorum XPath işlev satır kümesi sütun text(), belirtilen ColPattern.Bu belirtilen öznitelik merkezli eşleme üzerine yazar flags, ve yaprak değer sütunu içeren dize öğe içeriği.

deyim içindeki tüm sütunları alır satır kümesi openxml tarafından sağlanan.

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

Bu sonucu verir:

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

E.TableName WITH yan tümce tümce tümcesinde belirtme

Bu örnek belirtir TableName WITH yan tümce tümce tümcesinde yerine SchemaDeclaration.Hiçbir sütun desenleri ve istediğiniz yapıyı sahip bir tablo varsa yararlıdır ColPattern parametresi gereklidir.

Bu örnek xml belgesinde oluşur <Customer> ve <Order> öğeleri.openxml deyim üç sütun satır kümesi sipariş bilgileri alır (oid, Tarih, ve Tutar) xml belgesindeki.

İlk olarak, sp_xml_preparedocument saklı yordam, bir belge tanıtıcı elde etmek için verilir.Bu belge tutamacı için openxml geçirilir.

openxml deyim aşağıda gösterilmiştir:

rowpattern (/ kök/Müşteri/sipariş) tanımlayan <Order> düğümlerini işlemek için.
Yok yok SchemaDeclaration WITH yan tümce tümce tümcesinde.Bunun yerine, bir tablo adı belirtilir.Bu nedenle, tablo şema satır kümesi şeması olarak kullanılır.
The flags parameter value is set to 1 and indicates attribute-centric mapping.Bu nedenle, öðelerin özniteliklerine de tarafından tanımlanan, rowpattern, harita için satır kümesi sütun aynı ada sahip.

deyim içindeki tüm sütunları alır satır kümesi openxml tarafından sağlanan.

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

Bu sonucu verir:

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.Kenar tablo biçiminde bir sonuç elde etme

Bu örnekte, WITH yan tümce tümce tümce openxml deyim içinde belirtilir.Sonuç olarak, bir kenar tablo biçiminde openxml tarafından üretilen satır kümesi vardır.deyim içinde kenar tüm sütunları döndürür tablo.

Örnek xml belgesini örnekte oluşur <Customer>, <Order>, ve <OrderDetail> öğeleri.

İlk olarak, sp_xml_preparedocument saklı yordam, bir belge tanıtıcı elde etmek için verilir.Bu belge tutamacı için openxml geçirilir.

openxml deyim aşağıda gösterilmiştir:

rowpattern tanımlar (/ root/müşteri) <Customer> düğümlerini işlemek için.
WITH yan tümce tümce tümce sağlanır.Bu nedenle, openxml satır kümesi bir kenar tablo biçiminde döndürür.

deyim yer alan tüm sütun kenarını alır tablo.

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

Sonuç bir kenar tablo olarak döndürülür.Kenar tablo bilgilerini elde etmek için sorgular yazabilirsiniz.Örneğin:

Aşağıdaki sorgu sayısını verir Müşteri belgesindeki düğümlere.WITH yan tümce tümce tümce belirtildiğinden, openxml kenar tablo döndürür.Kenar tablo deyim sorgular.
```
SELECT count(*)
FROM OPENXML(@docHandle, '/')
WHERE localname = 'Customer'
```

Aşağıdaki sorgu xml öğesi türü düğümlerinin yerel adlarını döndürür.

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

G.Bir öznitelik ile biten rowpattern belirtme

Bu örnek xml belgesinde oluşur <Customer>, <Order>, ve <OrderDetail> öğeleri.openxml deyim üç sütun satır kümesi sipariş ayrıntıları hakkında bilgi alır (ProductID, Miktar, ve SiparişNo) xml belgesindeki.

İlk olarak, sp_xml_preparedocument bir belge tanıtıcı elde etmek için verilir.Bu belge tutamacı için openxml geçirilir.

openxml deyim aşağıda gösterilmiştir:

rowpattern (/ ROOT/Customer/Order/OrderDetail/@ProductID) bir xml öznitelik ile sona ProductID.Sonuç kümesi içinde seçili xml belgesindeki her öznitelik düğümü için satır kümesi kümesi oluşturulur.
Bu örnekte, flags parametresi belirtilmemiş.Bunun yerine, eşlemeleri tarafından belirtilen ColPattern parametresi.

De SchemaDeclaration WITH yan tümce tümce tümcesinde ColPattern ile de belirtilen ColName ve ColType parametreleri.İsteğe bağlı ColPattern olan aşağıdakileri belirtmek için belirtilen XPath deseni:

XPath deseni (.) olarak belirtilen ColPattern için ProdID satır kümesi sütun içerik düğümü, geçerli düğümü tanımlar.Göre rowpattern olduğu belirtilen ProductID öznitelik <OrderDetail> öğesi.
The ColPattern, ../@Quantity, specified for the Qty column in the rowset identifies the Quantity attribute of the parent, <OrderDetail>, node of the context node, <ProductID>.
Benzer şekilde, ColPattern, .../../@OrderID, belirtilen için OID sütununda satır kümesi tanımlayan SiparişNo üst öznitelik <Order>, içerik düðümünün üst düğümün.Üst düğüm <OrderDetail>, bağlam düğümdür ve <ProductID>.

deyim içindeki tüm sütunları alır satır kümesi openxml tarafından sağlanan.

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

Bu sonucu verir:

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

H.Birden çok metin düğümleri içeren bir xml belgesi belirtme

Birden çok metin düðümleri xml varsa, belge, bir deyim ile bir ColPattern, text(), bunların tümünün yerine yalnızca ilk metin düğümü döndürür.Örneğin:

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

deyim döndürür t sonuç olarak ve değil TaU.

Ö.WITH yan tümce tümce tümcesinde xml veri türü belirtme

WITH yan tümce tümce tümcesindeki için eşleşen bir sütun deseni xml veri türü sütununda, yazılı mı, türlenmemiş gerekir dönmek boş bir sıra ya da bir dizi öğelerini, iþleme yönergeleri, metin düğümleri ve yorumlar.Veri için artığını bir xml veri türü.

Aşağıdaki örnekte, WITH yan tümce tümce tümcesindeki tablo şema bildirim içeren xml sütunları yazın.

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

Özellikle, ilettiğiniz bir xml türü değişkeni (@ x) sp_xml_preparedocument() işlev.

Bu sonucu verir:

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>

Sonuç aşağıdakilere dikkat edin:

İçin SAdı sütun , varchar(30) türü değeri karşılık gelen gelen alınır <lname> öğesi.
İçin xmlname sütun , xml türü, aynı adı öğe değeri döndürülür.
Bayrak, 10'a küme.10 Anlamına gelir 2 + 8, burada 2 öğe merkezli eşleme gösterir ve 8 unconsumed xml verilerini yalnızca WITH yan tümce tümce tümcesinde tanımlanan taşma sütun eklenmesi gerektiğini belirtir.Varsa, küme bayrak WITH yan tümce tümce tümcesinde belirtilen taşma sütun için tüm xml belge 2'ye kopyalanır.
De durum WITH yan tümce tümce tümcesinde sütunu yazılı xml sütun, xml örnek için şemayı onaylamazsa, bir hata döndürdü.

J.Tek tek değerleri çok değerli özniteliklerini alma

Bir xml belge birden çok değerli öznitelikler olabilir.Örneğin, IDREFS öznitelik çok değerli olabilir.Bir xml belgesinde, birden çok değerli öznitelik değerleri boşlukla ayrılmış değerler içeren bir dize olarak belirtilir.Aşağıdaki xml belgesinde, attends öznitelik <Öğrenci> öğesi ve attendedBy öznitelik <sınıfı> çok değerli olduğunu.Birden çok değerli bir xml öznitelik tek tek değerleri alma ve her değer ayrı bir satırda veritabanında depolamak ek çalışma gerektirir.Bu örnek işlemi gösterilmektedir.

Bu örnek xml belgesi aşağıdaki öğelerden oluşur:

<Öğrenci>

The id (student ID), name, and attends attributes.The attends attribute is a multivalued attribute.
<Sınıf>

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

The attends attribute in <Student> and the attendedBy attribute in <Class> represent a m:n relationship between the Student and Class tables.Bir öğrenci çoğu sınıfları alabilir ve bir sınıf çok sayıda Öğrenci olabilir.

Bu belge shred ve aşağıdaki gösterildiği gibi veritabanına kaydetmek istediğinizi varsayalım:

Kaydet <Öğrenci> veri Öğrenciler tablo.
Kaydet <sınıfı> kursları verileri tablo.
Kaydet ortası Öğrenci sınıfta CourseAttendence arasındaki ilişki veri tablo.Ek çalışma değerleri ayıklamak için gereklidir.Bu bilgileri almak ve bunun içinde saklamak için tablo, bu saklı yordamları kullanın:
- Insert_Idrefs_Values
  
  Tabii kimliği ve Öğrenci kimliği değerleri ekler CourseAttendence tablo.
- Extract_idrefs_values
  
  Her birinden ayrı ayrı Öğrenci kimlikleri ayıklar <kursu> öğesi.Kenar tablo bu değerleri almak için kullanılır.

Adımlar şunlardır:

-- 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.xml verileri kodlanmış ikili base64 alınıyor

İkili veri base64 kodlaması kullanılarak xml içinde sık sık yer alır.openxml kullanarak bu xml shred, base64 olarak kodlanmış veri alıyorsunuz.Bu örnek göstermek nasıl başa ikili base64 olarak kodlanmış verileri dönüştürmek için bir CLR işlev yazabilirsiniz.

Örnek clr işlev oluşturmak için şu adımları izleyin:

Aşağıdaki C# kodu base64 olarak kodlanmış veri ikiliye dönüştürür.Kod, c:\temp klasöründeki base64.cs dosyasına kaydedin.

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

Kodu derleyip bir kitaplık oluşturma base64.dll.
```
csc /target:library base64.cs 
```
Kullanarak (Transact-sql) birleştirme oluşturmak, bir yönetilen uygulamayı modül oluşturun."İnclude" değiştirmek gerekecektir. Bilgisayarınızın adı ile.
```
drop assembly asbly_base64
go
create assembly asbly_base64 from '\\your_computer\c$\temp\base64.dll'
go
```
clr desteği etkinleştirilmiş yoksa yapabilirsiniz yürütmek clr etkinleştirmek için aşağıdaki deyimleri:
```
sp_configure 'clr enabled', 1
reconfigure with override
go
```

Base64 olarak kodlanmış veri giriş olarak alır ve ikili veri döndüren clr işlev oluşturun.Çağırarak bunu işlev yönetilen uygulama modülü.

create function fn_getBinaryFromBase64String( @s nvarchar(max) )
  returns varbinary(max)
as external name asbly_base64.Sample.ConvertBase64ToBinary
go

Şimdi aşağıdakileri yaparak CLR işlev test edebilirsiniz:

Örnek ikili verilerle bir tablo oluşturun.
for xml sorgu ve ikili BASE64 seçeneği base64 kodlanmış ikili verilere sahip xml oluşturmak için kullanın.
openxml kullanarak xml shred.openxml tarafından döndürülen veri base64 olarak kodlanmış veri olacaktır.Daha sonra çağrı CLR işlev ikiliye geri dönüştürmek için.

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

Sonuç budur.Döndürülen ikili verileri özgün ikili veride tablo t.

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

Ayrıca bkz.

Başvuru

sp_xml_preparedocument (Transact-sql)

sp_xml_removedocument (Transact-sql)

openxml (Transact-sql)

openxml kullanarak xml sorgulama

Aracılığıyla paylaş

openxml kullanma

Örnekler

A.openxml ile basit bir deyim yürütme

B.Satır kümesi sütunları ve xml öznitelikleri ve öğeleri arasında eşleştirme ColPattern belirtme

C.öznitelik merkezli ve öğe merkezli eşleme birleştirme

D.Text() işlev XPath ColPattern belirtme

E.TableName WITH yan tümce tümce tümcesinde belirtme

F.Kenar tablo biçiminde bir sonuç elde etme

G.Bir öznitelik ile biten rowpattern belirtme

H.Birden çok metin düğümleri içeren bir xml belgesi belirtme

Ö.WITH yan tümce tümce tümcesinde xml veri türü belirtme

J.Tek tek değerleri çok değerli özniteliklerini alma

J.xml verileri kodlanmış ikili base64 alınıyor

Ayrıca bkz.

Başvuru

Ek kaynaklar