Örnekler: OPENXML kullanma

Şunlar için geçerlidir:SQL Server Azure SQL Veritabanı Azure SQL Yönetilen Örneği Microsoft Fabric'te SQL veritabanı

Bu makaledeki örneklerde, XML belgesinin satır kümesi görünümünü oluşturmak için OPENXML'nin nasıl kullanıldığı gösterilmektedir. OPENXML söz dizimi hakkında bilgi için bkz. OPENXML (Transact-SQL). Örnekler OPENXML'nin tüm yönlerini gösterir, ancak OPENXML'de meta özellikler belirtmez. OPENXML'de meta özellikleri belirtme hakkında daha fazla bilgi için bkz. OPENXML'de MetaÖzellikler Belirtme.

Örnekler

Veriler alınırken, XML belgesindeki satırları belirleyen düğümleri tanımlamak için rowpattern kullanılır. Ayrıca , satır deseni MSXML XPath uygulamasında kullanılan XPath desen dilinde ifade edilir. Örneğin, desen bir öğe veya öznitelikle bitiyorsa, rowpattern tarafından seçilen her öğe veya öznitelik düğümü için bir satır oluşturulur.

Flags değeri varsayılan eşlemeyi sağlar. SchemaDeclaration içinde hiçbir ColPattern belirtilmezse, bayraklarda belirtilen eşleme varsayılır. Flags değeri, SchemaDeclaration içinde ColPattern belirtilmişse yoksayılır. Belirtilen ColPattern, eşlemeyi, öznitelik merkezli veya öğe merkezli ve ayrıca taşma ve kullanılmamış verilerle ilgilenme davranışını belirler.

A. OPENXML ile SELECT deyimi yürütme

Bu örnekteki XML belgesi , <Customer>ve <Order> öğelerinden <OrderDetail>oluşur. OPENXML deyimi, müşteri bilgilerini XML belgesinden CustomerID ve ContactName adlı iki sütunlu bir satır kümesinde alır.

İlk olarak, sp_xml_preparedocument saklı yordamı belge tanıtıcısını almak için çağrılır. Bu belge tanıtıcısı OPENXML'ye geçirilir.

OPENXML deyimi aşağıdakileri gösterir:

rowpattern (/ROOT/Customer), işlenmek üzere <Customer> düğümleri tanımlar.
flags parametre değeri 1 olarak ayarlanır ve öznitelik merkezli eşlemeyi gösterir. Sonuç olarak, XML öznitelikleri SchemaDeclaration'da tanımlanan satır kümesindeki sütunlarla eşler.
SchemaDeclaration'da, WITH yan tümcesinde, belirtilen ColName değerleri ilgili XML öznitelik adlarıyla eşleşmektedir. Bu nedenle, ColPattern parametresi SchemaDeclaration içinde belirtilmez.

SELECT deyimi daha sonra OPENXML tarafından sağlanan satır kümesindeki tüm sütunları alır.

DECLARE @DocHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
          OrderDate="1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzalez">
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
          OrderDate="1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@DocHandle, '/ROOT/Customer',1)
      WITH (CustomerID  varchar(10),
            ContactName varchar(20));
EXEC sp_xml_removedocument @DocHandle;

Sonuç şu şekildedir:

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

<Customer> Öğelerin alt öğesi olmadığından, öğe merkezli eşlemeyi göstermek için aynı SELECT deyimi 2olarak ayarlanmış bayraklarla yürütülürse, her iki müşterinin CustomerID ve ContactName değerleri NULL olarak döndürülür.

@xmlDocument xml türünde veya (n)varchar(max) türünde de olabilir.

XML belgesinde <CustomerID> ve <ContactName> alt öğelerse, öğe merkezli eşleme değerleri alır.

DECLARE @XmlDocumentHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer>
   <CustomerID>VINET</CustomerID>
   <ContactName>Paul Henriot</ContactName>
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer>
   <CustomerID>LILAS</CustomerID>
   <ContactName>Carlos Gonzalez</ContactName>
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT    *
FROM      OPENXML (@XmlDocumentHandle, '/ROOT/Customer',2)
           WITH (CustomerID  varchar(10),
                 ContactName varchar(20));
EXEC sp_xml_removedocument @XmlDocumentHandle;

Sonuç şu şekildedir:

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

sp_xml_preparedocument tarafından döndürülen belge tanıtıcısı, oturum sırasında değil toplu işlem sırasında geçerlidir.

B. Satır kümesi sütunları ile XML öznitelikleri ve öğeleri arasında eşleme için ColPattern belirtin

Bu örnek, satır kümesi sütunları ile XML öznitelikleri ve öğeleri arasında eşleme sağlamak için isteğe bağlı ColPattern parametresinde XPath deseninin nasıl belirtildiğini gösterir.

Bu örnekteki XML belgesi , <Customer>ve <Order> öğelerinden <OrderDetail>oluşur. OPENXML deyimi, müşteri ve sipariş bilgilerini XML belgesinden satır kümesi (CustomerID, OrderDate, ProdID ve Qty) olarak alır.

İlk olarak, sp_xml_preparedocument saklı yordamı belge tanıtıcısını almak için çağrılır. Bu belge tanıtıcısı OPENXML'ye geçirilir.

OPENXML deyimi aşağıdakileri gösterir:

rowpattern (/ROOT/Customer/Order/OrderDetail), işlenmek üzere <OrderDetail> düğümleri tanımlar.

Çizim için flags parametre değeri 2 olarak ayarlanır ve öğe merkezli eşlemeyi gösterir. Ancak, ColPattern'da belirtilen eşleme bu eşlemeyi geçersiz kılar. Yani, ColPattern'da belirtilen XPath deseni satır kümesindeki sütunları özniteliklerle eşler. Bu, öznitelik merkezli eşlemeyle sonuç alır.

SchemaDeclaration'da, WITH yan tümcesi içerisinde ColPattern, ColName ve ColType parametreleriyle belirtilir. İsteğe bağlı ColPattern , belirtilen XPath desenidir ve aşağıdakileri gösterir:

Satır kümesindeki OrderID, CustomerID ve OrderDate sütunları, rowpattern tarafından tanımlanan düğümlerin üst öğesinin öznitelikleriyle eşler ve rowpattern düğümleri <OrderDetail> tanımlar. Bu nedenle, CustomerID ve OrderDate sütunları öğesinin CustomerID ve OrderDate öznitelikleriyle eşler <Order> .
Satır kümesindeki ProdID ve Qty sütunları, satır deseninde tanımlanan düğümlerin ProductID ve Quantity öznitelikleriyle eşlenir.

SELECT deyimi daha sonra OPENXML tarafından sağlanan satır kümesindeki tüm sütunları alır.

DECLARE @XmlDocumentHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
           OrderDate="1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzalez">
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
           OrderDate="1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument;
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID     int         '../@OrderID',
      CustomerID  varchar(10) '../@CustomerID',
      OrderDate   datetime    '../@OrderDate',
      ProdID      int         '@ProductID',
      Qty         int         '@Quantity');
EXEC sp_xml_removedocument @XmlDocumentHandle;

Sonuç şu şekildedir:

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

Xml öğelerini satır kümesi sütunlarına eşlemek için ColPattern olarak belirtilen XPath deseni de belirtilebilir. Bu, öğe merkezli eşlemeye neden olur. Aşağıdaki örnekte, XML belgesi <CustomerID> ve <OrderDate> öğesinin <Orders> alt öğeleridir. ColPattern, flags parametresinde belirtilen eşlemenin üzerine yazdığı için flags parametresi OPENXML'de belirtilmez.

DECLARE @docHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order EmployeeID="5" >
      <OrderID>10248</OrderID>
      <CustomerID>VINET</CustomerID>
      <OrderDate>1996-07-04T00:00:00</OrderDate>
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzalez">
   <Order  EmployeeID="3" >
      <OrderID>10283</OrderID>
      <CustomerID>LILAS</CustomerID>
      <OrderDate>1996-08-16T00:00:00</OrderDate>
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID  varchar(10)   '../CustomerID',
      OrderDate   datetime      '../OrderDate',
      ProdID      int           '@ProductID',
      Qty         int           '@Quantity');
EXEC sp_xml_removedocument @docHandle;

C. Öznitelik merkezli ve öğe odaklı eşlemeyi birleştirme

Bu örnekte flags parametresi 3 olarak ayarlanmıştır ve hem öznitelik merkezli hem de öğe merkezli eşlemenin uygulanacağını gösterir. Bu durumda, önce öznitelik merkezli eşleme uygulanır ve ardından henüz ele alınmamış tüm sütunlar için öğe merkezli eşleme 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 Gonzalez</ContactName>
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
          OrderDate="1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument;

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer',3)
      WITH (CustomerID  varchar(10),
            ContactName varchar(20));
EXEC sp_xml_removedocument @docHandle;

Sonuç budur

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

Öznitelik merkezli eşleme CustomerID için uygulanır. öğesinde <Customer> özniteliği yok. Bu nedenle, öğe merkezli eşleme uygulanır.

D. text() XPath işlevini ColPattern olarak belirtin

Bu örnekteki XML belgesi, <Customer> ve <Order> öğelerinden oluşur. OPENXML deyimi öğesinden oid özniteliğinden, <Order> tarafından tanımlanan düğümün üst öğesinin kimliğinden ve öğe içeriğinin yaprak-değer dizesinden oluşan bir satır kümesi alır.

İlk olarak, sp_xml_preparedocument saklı yordamı belge tanıtıcısını almak için çağrılır. Bu belge tanıtıcısı OPENXML'ye geçirilir.

OPENXML deyimi aşağıdakileri gösterir:

rowpattern (/root/Customer/Order), işlenmek üzere <Order> düğümleri tanımlar.
flags parametre değeri 1 olarak ayarlanır ve öznitelik merkezli eşlemeyi gösterir. Sonuç olarak, XML öznitelikleri SchemaDeclaration içinde tanımlanan satır kümesindeki sütunlarla eşleşir.
WITH yan tümcesindeki SchemaDeclaration'da, oid ve amount satır kümesi sütun adları ilgili XML öznitelik adlarıyla eşleşir. Bu nedenle , ColPattern parametresi belirtilmez. Satır kümesindeki açıklama sütunu için, XPath işlevi text()ColPattern olarak belirtilir. Bu, bayraklarda belirtilen öznitelik merkezli eşlemenin üzerine yazar ve sütun öğe içeriğinin yaprak-değer dizesini içerir.

SELECT deyimi daha sonra OPENXML tarafından sağlanan satır kümesindeki tüm sütunları alır.

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;

Sonuç şu şekildedir:

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

E. WITH yan tümcesinde TableName belirtin

Bu örnek, SchemaDeclaration yerine WITH yan tümcesinde TableName değerini belirtir. İstediğiniz yapıya sahip bir tablonuz varsa ve sütun deseni ( ColPattern parametresi) gerekli değilse bu yararlı olur.

Bu örnekteki XML belgesi, <Customer> ve <Order> öğelerinden oluşur. OPENXML deyimi, SIPARIŞ bilgilerini XML belgesinden üç sütunlu satır kümesinde (oid, tarih ve tutar) alır.

İlk olarak, sp_xml_preparedocument saklı yordamı belge tanıtıcısını almak için çağrılır. Bu belge tanıtıcısı OPENXML'ye geçirilir.

OPENXML deyimi aşağıdakileri gösterir:

rowpattern (/root/Customer/Order), işlenmek üzere <Order> düğümleri tanımlar.
WITH yan tümcesinde SchemaDeclaration yoktur. Bunun yerine, bir tablo adı belirtilir. Bu nedenle, tablo şeması satır kümesi şeması olarak kullanılır.
flags parametre değeri 1 olarak ayarlanır ve öznitelik merkezli eşlemeyi gösterir. Bu nedenle, rowpattern tarafından tanımlanan öğelerin öznitelikleri, aynı ada sahip satır kümesi sütunlarıyla eşleşir.

SELECT deyimi daha sonra OPENXML tarafından sağlanan satır kümesindeki tüm sütunları alır.

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

Sonuç şu şekildedir:

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. Sonucu kenar tablosu biçiminde alma

Bu örnekte, WITH yan tümcesi OPENXML deyiminde belirtilmemiş. Sonuç olarak, OPENXML tarafından oluşturulan satır kümesinin kenar tablo biçimi vardır. SELECT deyimi, kenar tablosundaki tüm sütunları döndürür.

Örnekteki örnek XML belgesi , <Customer>ve <Order> öğelerinden <OrderDetail>oluşur.

İlk olarak, sp_xml_preparedocument saklı yordamı belge tanıtıcısını almak için çağrılır. Bu belge tanıtıcısı OPENXML'ye geçirilir.

OPENXML deyimi aşağıdakileri gösterir:

rowpattern (/ROOT/Customer), işlenmek üzere <Customer> düğümleri tanımlar.
WITH ifadesi sağlanmadı. Bu nedenle, OPENXML satır kümesini bir kenar tablosu biçiminde döndürür.

SELECT deyimi daha sonra kenar tablosundaki tüm sütunları alır.

DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
SET @xmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order CustomerID="VINET" EmployeeID="5" OrderDate=
           "1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzalez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate=
           "1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer');

EXEC sp_xml_removedocument @docHandle;

Sonuç bir kenar tablosu olarak döndürülür. Bilgi edinmek için uç tabloya sorgu yazabilirsiniz. Örneğin:

Aşağıdaki sorgu, belgedeki Müşteri düğümlerinin sayısını döndürür. WITH yan tümcesi belirtilmediğinden, OPENXML bir kenar tablosu döndürür. SELECT deyimi uç tablosunu sorgular.
```
SELECT count(*)
FROM OPENXML(@docHandle, '/')
WHERE localname = 'Customer';
```

Aşağıdaki sorgu, öğe türündeki XML düğümlerinin yerel adlarını döndürür.

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

G. Öznitelikle biten bir satır desenini belirtme

Bu örnekteki XML belgesi , <Customer>ve <Order> öğelerinden <OrderDetail>oluşur. OPENXML deyimi, XML belgesinden üç sütunlu satır kümesindeki (ProductID, Quantity ve OrderID) sipariş ayrıntıları hakkındaki bilgileri alır.

İlk olarak, belge tanıtıcısını almak için sp_xml_preparedocument çağrılır. Bu belge tanıtıcısı OPENXML'ye geçirilir.

OPENXML deyimi aşağıdakileri gösterir:

rowpattern (/ROOT/Customer/Order/OrderDetail/@ProductID) bir XML özniteliği olan ProductID ile biter. Sonuçta elde edilen satır kümesinde, XML belgesinde seçilen her öznitelik düğümü için bir satır oluşturulur.
Bu örnekte flags parametresi belirtilmemiştir. Bunun yerine, eşlemeler ColPattern parametresi tarafından belirtilir.

WITH yan tümcesindeki SchemaDeclaration içinde ColPattern da ColName ve ColType parametreleriyle belirtilir. İsteğe bağlı ColPattern , aşağıdakileri belirtmek için belirtilen XPath desenidir:

Satır kümesindeki ProdID sütunu için ColPattern olarak belirtilen XPath deseni (.) bağlam düğümünü (geçerli düğüm) tanımlar. Belirtilen satır desenine göre, öğesinin <OrderDetail> özniteliğidir.
ColPattern, ../@Quantity, satır kümesindeki Qty sütunu için belirtilmiş olan, bağlam düğümünün ProductID<OrderDetail> olan ebeveyn düğümünün < özniteliğini tanımlar.
Benzer şekilde, satır kümesindeki OID sütunu için belirtilmiş ColPattern, ../../@OrderID, bağlam düğümünün üst düğümünün ebeveyni olan OrderID özniteliğini tanımlar. <Order> Üst düğüm <OrderDetail>'dir, ve bağlam düğümü <ProductID>'dir.

SELECT deyimi daha sonra OPENXML tarafından sağlanan satır kümesindeki tüm sütunları alır.

DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
--Sample XML document
SET @xmlDocument =N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5" OrderDate=
           "1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzalez">
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" OrderDate=
           "1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail/@ProductID')
       WITH ( ProdID  int '.',
              Qty     int '../@Quantity',
              OID     int '../../@OrderID');
EXEC sp_xml_removedocument @docHandle;

Sonuç şu şekildedir:

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

H. Birden çok metin düğümü olan bir XML belgesi belirtme

XML belgesinde birden çok metin düğümleriniz varsa, ColPattern, text()içeren bir SELECT deyimi, tümü yerine yalnızca ilk metin düğümünü 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;

SELECT deyimi sonuç olarak T döndürür, TaU'ya döndürmez.

I. WITH yan tümcesinde XML veri türünü belirtin

WITH yan tümcesinde, xml veri türü sütununa eşlenen bir sütun deseni (yazılmış veya yazılmamış) boş bir dizi veya öğe dizisi, işleme yönergeleri, metin düğümleri ve açıklamalar döndürmelidir. Veriler bir xml veri türüne yayınlanır.

Aşağıdaki örnekte, WITH yan tümcesindeki tablo şeması bildirimi xml türü sütunları içerir.

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, sp_xml_preparedocument() işlevine bir xml türü değişkeni (@x) geçiriyorsunuz.

Sonuç şu şekildedir:

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çtan aşağıdakilere dikkat edin:

varchar(30) türünün lname sütunu için değeri ilgili <lname> öğeden alınır.
Xml türündeki xmlname sütunu için, değeriyle aynı ad öğesi döndürülür.
Bayrak 10 olarak ayarlanır. 10, 2 + 8 anlamına gelir; burada 2 öğe merkezli eşlemeyi, 8 ise WITH yan tümcesinde tanımlanan OverFlow sütununa yalnızca tutarsız XML verilerinin eklenmesi gerektiğini belirtir. Bayrağı 2 olarak ayarlarsanız, XML belgesinin tamamı WITH yan tümcesinde belirtilen OverFlow sütununa kopyalanır.
WITH yan tümcesindeki sütunun türü alınmış bir XML sütunu olması ve XML örneğinin şemayı onaylamaması durumunda bir hata döndürülür.

J. Birden çok değerli özniteliklerden tek tek değerleri alma

XML belgesinde birden çok değerli öznitelikler olabilir. Örneğin , IDREFS özniteliği birden çok değerli olabilir. XML belgesinde, birden çok değerli öznitelik değerleri, değerleri boşlukla ayrılmış bir dize olarak belirtilir. Aşağıdaki XML belgesinde, Student< öğesinin > özniteliği ve Sınıf< öğesinin > özniteliği çok değerli bir özellik taşır. Birden çok değerli XML özniteliğinden tek tek değerleri almak ve her değeri veritabanında ayrı bir satırda depolamak fazladan çalışma gerektirir. Bu örnekte işlemi gösterilmektedir.

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

<Öğrenci>

Kimlik (öğrenci kimliği), ad ve katılım öznitelikleri. attends özniteliği çok değerli bir özniteliktir.
<Sınıf>

Id (sınıf kimliği), ad ve attendedBy öznitelikleri. attendedBy özniteliği çok değerli bir özniteliktir.

Student içindeki <> özniteliği ve Sınıftaki <> özniteliği, Student ve Class tabloları arasındaki m:n ilişkisini temsil eder. Bir öğrenci birçok ders alabilir ve bir sınıfta çok sayıda öğrenci olabilir.

Bu belgeyi parçalamak ve aşağıda gösterildiği gibi veritabanında kaydetmek istediğinizi varsayalım:

<Student> Verileri Öğrenciler tablosuna kaydedin.
<Class> Verileri Kurslar tablosuna kaydedin.
Öğrenci ile Sınıf arasındaki m:n ilişki verilerini CourseAttendence tablosuna kaydedin. Değerleri ayıklamak için daha fazla çalışma gerekir. Bu bilgileri almak ve tabloda depolamak için şu saklı yordamları kullanın:
- Insert_Idrefs_Values
  
  CourseAttendence tablosuna ders kimliği ve öğrenci kimliği değerlerini ekler.
- Extract_idrefs_values
  
  Her <Kurs> öğesinden tek tek öğrenci kimliklerini ayıklar. Bu değerleri almak için bir kenar tablosu kullanılır.

Adımlar aşağıdaki gibidir:

-- Create these tables:
DROP TABLE CourseAttendance;
DROP TABLE Students;
DROP TABLE Courses;
GO
CREATE TABLE Students(
                id   varchar(5) primary key,
                name varchar(30)
                );
GO
CREATE TABLE Courses(
               id       varchar(5) primary key,
               name     varchar(30),
               taughtBy varchar(5)
);
GO
CREATE TABLE CourseAttendance(
             id         varchar(5) references Courses(id),
             attendedBy varchar(5) references Students(id),
             constraint CourseAttendance_PK primary key (id, attendedBy)
);
GO
-- Create these stored procedures:
DROP PROCEDURE f_idrefs;
GO
CREATE PROCEDURE f_idrefs
    @t      varchar(500),
    @idtab  varchar(50),
    @id     varchar(5)
AS
DECLARE @sp int;
DECLARE @att varchar(5);
SET @sp = 0;
WHILE (LEN(@t) > 0)
BEGIN
    SET @sp = CHARINDEX(' ', @t+ ' ');
    SET @att = LEFT(@t, @sp-1);
    EXEC('INSERT INTO '+@idtab+' VALUES ('''+@id+''', '''+@att+''')');
    SET @t = SUBSTRING(@t+ ' ', @sp+1, LEN(@t)+1-@sp);
END;
GO

DROP PROCEDURE fill_idrefs
GO
CREATE PROCEDURE fill_idrefs
    @xmldoc     int,
    @xpath      varchar(100),
    @from       varchar(50),
    @to         varchar(50),
    @idtable    varchar(100)
AS
DECLARE @t varchar(500);
DECLARE @id varchar(5);

/* Temporary Edge table */
SELECT *
INTO #TempEdge
FROM OPENXML(@xmldoc, @xpath);

DECLARE fillidrefs_cursor CURSOR FOR
    SELECT CAST(iv.text AS nvarchar(200)) AS id,
           CAST(av.text AS nvarchar(4000)) AS refs
    FROM   #TempEdge c, #TempEdge i,
           #TempEdge iv, #TempEdge a, #TempEdge av
    WHERE  c.id = i.parentid
    AND    UPPER(i.localname) = UPPER(@from)
    AND    i.id = iv.parentid
    AND    c.id = a.parentid
    AND    UPPER(a.localname) = UPPER(@to)
    AND    a.id = av.parentid;

OPEN fillidrefs_cursor
FETCH NEXT FROM fillidrefs_cursor INTO @id, @t;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
        execute f_idrefs @t, @idtable, @id;
    END
    FETCH NEXT FROM fillidrefs_cursor INTO @id, @t
END;
CLOSE fillidrefs_cursor;
DEALLOCATE fillidrefs_cursor;
Go
-- This is the sample document that is shredded and the data is stored in the preceding tables.
DECLARE @h int;
EXECUTE sp_xml_preparedocument @h OUTPUT, N'<Data>
  <Student id = "s1" name = "Student1"  attends = "c1 c3 c6"  />
  <Student id = "s2" name = "Student2"  attends = "c2 c4" />
  <Student id = "s3" name = "Student3"  attends = "c2 c4 c6" />
  <Student id = "s4" name = "Student4"  attends = "c1 c3 c5" />
  <Student id = "s5" name = "Student5"  attends = "c1 c3 c5 c6" />
  <Student id = "s6" name = "Student6" />

  <Class id = "c1" name = "Intro to Programming"
         attendedBy = "s1 s4 s5" />
  <Class id = "c2" name = "Databases"
         attendedBy = "s2 s3" />
  <Class id = "c3" name = "Operating Systems"
         attendedBy = "s1 s4 s5" />
  <Class id = "c4" name = "Networks" attendedBy = "s2 s3" />
  <Class id = "c5" name = "Algorithms and Graphs"
         attendedBy =  "s4 s5"/>
  <Class id = "c6" name = "Power and Pragmatism"
         attendedBy = "s1 s3 s5" />
</Data>';

INSERT INTO Students SELECT * FROM OPENXML(@h, '//Student') WITH Students

INSERT INTO Courses SELECT * FROM OPENXML(@h, '//Class') WITH Courses
/* Using the edge table */
EXECUTE fill_idrefs @h, '//Class', 'id', 'attendedby', 'CourseAttendance';

SELECT * FROM Students;
SELECT * FROM Courses;
SELECT * FROM CourseAttendance;

EXECUTE sp_xml_removedocument @h;

K. XML'de base64 kodlanmış veriden ikili veriyi alma

İkili veriler genellikle base64 kodlaması kullanılarak XML'e eklenir. OPENXML kullanarak bu XML'yi parçaladığınızda base64 ile kodlanmış verileri alırsınız. Bu örnekte base64 kodlanmış verileri ikiliye nasıl dönüştürebileceğiniz gösterilmektedir.

Örnek ikili verileri olan bir tablo oluşturun.
İkili verilerin base64 olarak kodlanmış olduğu XML oluşturmak için FOR XML sorgusu ve BINARY BASE64 seçeneğini kullanın.
OPENXML kullanarak XML'yi parçalama. OPENXML tarafından döndürülen veriler base64 ile kodlanmış veriler olacaktır. Ardından, geriye ikiliye dönüştürmek için .value işlevini çağırın.

CREATE TABLE T (Col1 int primary key, Col2 varbinary(100));
GO
-- Insert sample binary data
INSERT T VALUES(1, 0x1234567890);
GO
-- Create test XML document that has base64 encoded binary data (use FOR XML query and specify BINARY BASE64 option)
SELECT * FROM T
FOR XML AUTO, BINARY BASE64;
GO
-- result
-- <T Col1="1" Col2="EjRWeJA="/>

-- Now shredd the sample XML using OPENXML.
-- Call the .value function to convert
-- the base64 encoded data returned by OPENXML to binary.
DECLARE @h int;
EXEC sp_xml_preparedocument @h OUTPUT, '<T Col1="1" Col2="EjRWeJA="/>';
SELECT Col1,
CAST('<binary>' + Col2 + '</binary>' AS XML).value('.', 'varbinary(max)') AS BinaryCol
FROM openxml(@h, '/T')
WITH (Col1 integer, Col2 varchar(max)) ;
EXEC sp_xml_removedocument @h;
GO

Sonuç budur. Döndürülen ikili veriler, T tablosundaki özgün ikili verilerdir.

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

Ayrıca bakınız

Geri Bildirim

Bu sayfayı yararlı buldunuz mu?

Last updated on 2025-11-18

Aracılığıyla paylaş

Örnekler: OPENXML kullanma

Örnekler

A. OPENXML ile SELECT deyimi yürütme

B. Satır kümesi sütunları ile XML öznitelikleri ve öğeleri arasında eşleme için ColPattern belirtin

C. Öznitelik merkezli ve öğe odaklı eşlemeyi birleştirme

D. text() XPath işlevini ColPattern olarak belirtin

E. WITH yan tümcesinde TableName belirtin

F. Sonucu kenar tablosu biçiminde alma

G. Öznitelikle biten bir satır desenini belirtme

H. Birden çok metin düğümü olan bir XML belgesi belirtme

I. WITH yan tümcesinde XML veri türünü belirtin

J. Birden çok değerli özniteliklerden tek tek değerleri alma

K. XML'de base64 kodlanmış veriden ikili veriyi alma

Ayrıca bakınız

Geri Bildirim

Ek kaynaklar