OPENXML (Transact-SQL)

  • Artikel
  • 6 menit untuk membaca

Berlaku untuk:Database SQL Server Azure SQL Azure SQL Managed Instance

OPENXML menyediakan tampilan set baris melalui dokumen XML. Karena OPENXML adalah penyedia set baris, OPENXML dapat digunakan dalam pernyataan Transact-SQL di mana penyedia set baris seperti tabel, tampilan, atau fungsi OPENROWSET dapat muncul.

Konvensi sintaks transact-SQL

Sintaks

OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] )   
[ WITH ( SchemaDeclaration | TableName ) ]

Catatan

Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 dan yang lebih lama, lihat Dokumentasi versi sebelumnya.

Argumen

idoc
Adalah handel dokumen dari representasi internal dokumen XML. Representasi internal dokumen XML dibuat dengan memanggil sp_xml_preparedocument.

rowpattern
Apakah pola JalurX digunakan untuk mengidentifikasi simpul yang akan diproses sebagai baris. Simpul berasal dari dokumen XML yang handelnya diteruskan dalam parameter idoc .

Bendera
Menunjukkan pemetaan yang digunakan antara data XML dan himpunan baris relasional, dan bagaimana kolom tumpahan diisi. bendera adalah parameter input opsional, dan dapat menjadi salah satu nilai berikut.

Nilai byte Deskripsi
0 Default ke pemetaan atribut-sentris .
1 Gunakan pemetaan yang ber sentris atribut . Dapat dikombinasikan dengan XML_ELEMENTS. Dalam hal ini, pemetaan atribut-sentris diterapkan terlebih dahulu. Selanjutnya, pemetaan elemen-sentris diterapkan untuk kolom yang tersisa.
2 Gunakan pemetaan elemen-sentris . Dapat dikombinasikan dengan XML_ATTRIBUTES. Dalam hal ini, pemetaan atribut-sentris diterapkan terlebih dahulu. Selanjutnya, pemetaan elemen-sentris diterapkan untuk kolom yang tersisa.
8 Dapat digabungkan (logis OR) dengan XML_ATTRIBUTES atau XML_ELEMENTS. Dalam konteks pengambilan, bendera ini menunjukkan bahwa data yang digunakan tidak boleh disalin ke properti luapan @mp:xmltext.

SchemaDeclaration
Adalah definisi skema dari bentuk: ColNameColType [ColPattern | MetaProperty] [,ColNameColType [ColPattern | MetaProperty]...]

ColName
Adalah nama kolom dalam set baris.

ColType
Adalah jenis data SQL Server kolom dalam set baris. Jika jenis kolom berbeda dari jenis data xml yang mendasar dari atribut , pemakaian jenis terjadi.

ColPattern
Adalah pola XPath umum opsional yang menjelaskan bagaimana simpul XML harus dipetakan ke kolom. Jika ColPattern tidak ditentukan, pemetaan default (pemetaan yang berpusat pada atribut atau elemen yang berpusat seperti yang ditentukan oleh bendera) terjadi.

Pola JalurX yang ditentukan sebagai ColPattern digunakan untuk menentukan sifat khusus pemetaan (untuk pemetaan yang berpusat pada atribut dan elemen ) yang menimpa atau meningkatkan pemetaan default yang ditunjukkan oleh bendera.

Pola JalurX umum yang ditentukan sebagai ColPattern juga mendukung metaproperti.

MetaProperty
Adalah salah satu metaproperti yang disediakan oleh OPENXML. Jika MetaProperty ditentukan, kolom berisi informasi yang disediakan oleh metaproperty. Metaproperti memungkinkan Anda mengekstrak informasi (seperti posisi relatif dan informasi namespace) tentang simpul XML. Metaproperti ini memberikan lebih banyak informasi daripada yang terlihat dalam representasi tekstual.

TableName
Adalah nama tabel yang dapat diberikan (bukan SchemaDeclaration) jika tabel dengan skema yang diinginkan sudah ada dan tidak ada pola kolom yang diperlukan.

Keterangan

Klausa WITH menyediakan format set baris (dan informasi pemetaan tambahan sebagaimana diperlukan) dengan menggunakan SchemaDeclaration atau menentukan TableName yang ada. Jika klausa WITH opsional tidak ditentukan, hasilnya dikembalikan dalam format tabel edge . Tabel Edge mewakili struktur dokumen XML terperinci (seperti nama elemen/atribut, hierarki dokumen, namespace, PIs, dan son on) dalam satu tabel.

Tabel berikut ini menjelaskan struktur tabel tepi .

Nama kolom Jenis data Deskripsi
id bigint Adalah ID unik dari simpul dokumen.

Elemen root memiliki nilai ID 0. Nilai ID negatif dicadangkan.
parentid bigint Mengidentifikasi induk simpul. Induk yang diidentifikasi oleh ID ini belum tentu merupakan elemen induk, tetapi tergantung pada NodeType simpul yang induknya diidentifikasi oleh ID ini. Misalnya, jika simpul adalah simpul teks, induknya mungkin merupakan simpul atribut.

Jika simpul berada di tingkat atas dalam dokumen XML, ParentID-nya ADALAH NULL.
nodetype int Mengidentifikasi jenis node. Adalah bilangan bulat yang sesuai dengan penomoran jenis node XML DOM.

Jenis node adalah:

1 = Simpul elemen

2 = Simpul atribut

3 = Simpul teks
nama lokal nvarchar Memberikan nama lokal elemen atau atribut. Adalah NULL jika objek DOM tidak memiliki nama.
awalan nvarchar Adalah awalan namespace dari nama simpul.
namespaceuri nvarchar Adalah URI namespace dari simpul. Jika nilainya NULL, tidak ada namespace layanan yang ada.
Datatype nvarchar Adalah jenis data aktual dari elemen atau baris atribut, jika tidak adalah NULL. Jenis data disimpulkan dari DTD sebaris atau dari skema sebaris.
Prev bigint Adalah ID XML dari elemen saudara kandung sebelumnya. Apakah NULL jika tidak ada saudara kandung sebelumnya langsung.
teks ntext Berisi nilai atribut atau konten elemen dalam bentuk teks (atau NULL jika entri tabel tepi tidak memerlukan nilai).

Contoh

A. Menggunakan pernyataan SELECT sederhana dengan OPENXML

Contoh berikut membuat representasi internal gambar XML dengan menggunakan sp_xml_preparedocument. SELECT Pernyataan yang menggunakan OPENXML penyedia set baris kemudian dijalankan terhadap representasi internal dokumen XML.

Nilai bendera diatur ke 1. Nilai ini menunjukkan pemetaan atribut-sentris . Oleh karena itu, atribut XML memetakan ke kolom dalam set baris. Rowpattern yang ditentukan sebagai /ROOT/Customer mengidentifikasi simpul yang <Customers> akan diproses.

Parameter ColPattern (pola kolom) opsional tidak ditentukan karena nama kolom cocok dengan nama atribut XML.

Penyedia OPENXML set baris membuat set baris dua kolom (CustomerID dan ContactName) tempat pernyataan mengambil kolom yang SELECT diperlukan (dalam hal ini, semua kolom).

DECLARE @idoc INT, @doc VARCHAR(1000);  
SET @doc ='  
<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 @idoc OUTPUT, @doc;  
-- Execute a SELECT statement that uses the OPENXML rowset provider.  
SELECT    *  
FROM       OPENXML (@idoc, '/ROOT/Customer',1)  
            WITH (CustomerID  VARCHAR(10),  
                  ContactName VARCHAR(20));

Berikut adalah hasil yang ditetapkan.

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

Jika pernyataan yang sama SELECT dijalankan dengan bendera yang diatur ke 2, menunjukkan pemetaan elemen-sentris , nilai CustomerID dan ContactName untuk kedua pelanggan dalam dokumen XML dikembalikan sebagai NULL, karena tidak ada elemen apa pun yang bernama CustomerID atau ContactName dalam dokumen XML.

Berikut adalah tataan hasil.

CustomerID ContactName  
---------- -----------  
NULL       NULL  
NULL       NULL

B. Menentukan ColPattern untuk pemetaan antara kolom dan atribut XML

Kueri berikut mengembalikan ID pelanggan, tanggal pesanan, ID produk, dan atribut kuantitas dari dokumen XML. Rowpattern mengidentifikasi <OrderDetails> elemen. ProductID dan Quantity merupakan atribut dari <OrderDetails> elemen . Namun, OrderID, CustomerID, dan OrderDate adalah atribut dari elemen induk (<Orders>).

ColPattern opsional ditentukan untuk pemetaan berikut:

  • , OrderID, CustomerIDdan OrderDate dalam peta set baris ke atribut induk simpul yang diidentifikasi oleh rowpattern dalam dokumen XML.

  • Kolom ProdID dalam set baris memetakan ke ProductID atribut , dan Qty kolom di set baris memetakan ke Quantity atribut simpul yang diidentifikasi dalam rowpattern.

Meskipun pemetaan yang berpusat pada elemen ditentukan oleh parameter bendera , pemetaan yang ditentukan dalam ColPattern menimpa pemetaan ini.

DECLARE @idoc INT, @doc VARCHAR(1000);   
SET @doc ='  
<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">v  
   <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 @idoc OUTPUT, @doc;   
  
-- SELECT stmt using OPENXML rowset provider  
SELECT *  
FROM   OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)   
         WITH (OrderID       int         '../@OrderID',
               CustomerID  varchar(10) '../@CustomerID',
               OrderDate   datetime    '../@OrderDate',
               ProdID      int         '@ProductID',
               Qty         int         '@Quantity');

Berikut adalah hasil yang ditetapkan.

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

C. Mendapatkan hasil dalam format tabel edge

Contoh dokumen XML dalam contoh berikut terdiri dari <Customers>elemen , <Orders>, dan <Order_0020_Details> . Pertama, sp_xml_preparedocument dipanggil untuk mendapatkan handel dokumen. Handel dokumen ini diteruskan ke OPENXML.

OPENXML Dalam pernyataan , rowpattern (/ROOT/Customers) mengidentifikasi simpul yang akan diproses<Customers>. Karena klausa WITH tidak disediakan, OPENXML mengembalikan set baris dalam format tabel edge .

SELECT Akhirnya pernyataan mengambil semua kolom dalam tabel edge.

DECLARE @idoc INT, @doc VARCHAR(1000);   
SET @doc ='  
<ROOT>  
<Customers CustomerID="VINET" ContactName="Paul Henriot">  
   <Orders CustomerID="VINET" EmployeeID="5" OrderDate=  
           "1996-07-04T00:00:00">  
      <Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>  
      <Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>  
   </Orders>  
</Customers>  
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">  
   <Orders CustomerID="LILAS" EmployeeID="3" OrderDate=  
           "1996-08-16T00:00:00">  
      <Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>  
   </Orders>  
</Customers>  
</ROOT>';  
  
--Create an internal representation of the XML document.  
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;   
  
-- SELECT statement that uses the OPENXML rowset provider.  
SELECT    *  
FROM       OPENXML (@idoc, '/ROOT/Customers')   
EXEC sp_xml_removedocument @idoc;

Lihat juga

Contoh: Menggunakan OPENXML