OPENXML (SQL Server)

OPENXML, kata kunci Transact-SQL, menyediakan kumpulan baris atas dokumen XML yang ada di dalam memori yang mirip dengan tabel atau 'view'. OPENXML memungkinkan akses ke data XML seolah-olah itu adalah kumpulan baris relasional. Ini dilakukan dengan memberikan tampilan kumpulan baris dari representasi internal dokumen XML. Rekaman dalam set baris dapat disimpan dalam tabel database.

OPENXML dapat digunakan dalam pernyataan SELECT dan SELECT INTO di mana pun penyedia rowset, tampilan, atau OPENROWSET dapat muncul sebagai sumbernya. Untuk informasi tentang sintaks OPENXML, lihat OPENXML (Transact-SQL).

Untuk menulis kueri terhadap dokumen XML dengan menggunakan OPENXML, Anda harus terlebih dahulu memanggil sp_xml_preparedocument. Ini mengurai dokumen XML dan mengembalikan referensi ke dokumen yang telah diurai dan siap digunakan. Dokumen yang diurai adalah representasi pohon model objek dokumen (DOM) dari berbagai simpul dalam dokumen XML. Handle dokumen diteruskan ke OPENXML. OPENXML kemudian menyediakan tampilan kumpulan baris dokumen, berdasarkan parameter yang diteruskan ke dokumen tersebut.

Nota

sp_xml_preparedocument menggunakan versi pengurai MSXML yang diperbarui SQL, Msxmlsql.dll. Versi parser MSXML ini dirancang untuk mendukung SQL Server dan tetap kompatibel ke belakang dengan MSXML versi 2.6.

Representasi internal dokumen XML harus dihapus dari memori dengan memanggil prosedur tersimpan sistem sp_xml_removedocument untuk membebaskan memori.

Ilustrasi berikut menunjukkan prosesnya.

Mengurai XML dengan OPENXML

Perhatikan bahwa untuk memahami OPENXML, diperlukan pemahaman tentang KUERI XPath dan pemahaman tentang XML. Untuk informasi selengkapnya tentang dukungan XPath di SQL Server, lihat Menggunakan Kueri XPath di SQLXML 4.0.

Nota

OpenXML memungkinkan pola baris dan kolom XPath diparameterkan sebagai variabel. Parameterisasi tersebut dapat menyebabkan injeksi ekspresi XPath, jika pemrogram mengekspos parameterisasi ke pengguna luar (misalnya, jika parameter disediakan melalui prosedur tersimpan yang disebut eksternal). Untuk menghindari potensi masalah keamanan seperti itu, disarankan agar parameter XPath tidak boleh diekspos ke pemanggil eksternal.

Contoh

Contoh berikut menunjukkan penggunaan OPENXML dalam INSERT pernyataan dan SELECT pernyataan. Contoh dokumen XML berisi <Customers> elemen dan <Orders> .

Pertama, prosedur tersimpan sp_xml_preparedocument mengurai dokumen XML. Dokumen yang diurai adalah representasi pohon dari simpul (elemen, atribut, teks, dan komentar) dalam dokumen XML. OPENXML kemudian merujuk ke dokumen XML yang diurai ini dan menyediakan tampilan set baris dari semua atau bagian dokumen XML ini. Pernyataan INSERT yang menggunakan OPENXML dapat menyisipkan data dari kumpulan baris tersebut ke dalam tabel database. Beberapa OPENXML panggilan dapat digunakan untuk menyediakan tampilan kumpulan baris dari berbagai bagian dokumen XML dan memprosesnya, misalnya, dengan menyisipkannya ke dalam tabel yang berbeda. Proses ini juga disebut sebagai merobol XML ke dalam tabel.

Dalam contoh berikut, dokumen XML dihancurkan dengan cara elemen <Customers> disimpan dalam Customers tabel dan <Orders> elemen disimpan dalam Orders tabel dengan menggunakan dua INSERT pernyataan. Contoh ini juga menunjukkan SELECT pernyataan dengan OPENXML yang mengekstrak CustomerID dan OrderDate dari dokumen XML. Langkah terakhir dalam proses ini adalah memanggil sp_xml_removedocument. Ini dilakukan untuk merilis memori yang dialokasikan untuk berisi representasi pohon XML internal yang dibuat selama fase penguraian.

-- Create tables for later population using OPENXML.
CREATE TABLE Customers (CustomerID varchar(20) primary key,
                ContactName varchar(20), 
                CompanyName varchar(20));
GO
CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime);
GO
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(max); -- or xml type
SET @xmlDocument = N'<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>';
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers 
SELECT * 
FROM OPENXML(@docHandle, N'/ROOT/Customers') 
  WITH Customers;
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders 
SELECT * 
FROM OPENXML(@docHandle, N'//Orders') 
  WITH Orders;
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers/Orders') WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime);
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @docHandle;

Ilustrasi berikut ini memperlihatkan pohon XML yang diurai dari dokumen XML sebelumnya yang dibuat dengan menggunakan sp_xml_preparedocument.

Pohon XML yang diurai

Parameter OPENXML

Parameter untuk OPENXML mencakup yang berikut ini:

Penanganan dokumen XML (idoc)
Ekspresi XPath untuk mengidentifikasi simpul yang akan dipetakan ke baris (rowpattern)
Deskripsi kumpulan baris yang akan dihasilkan
Pemetaan antara kolom set baris dan simpul XML

Handle Dokumen XML (idoc)

Handle dokumen dikembalikan oleh prosedur simpan sp_xml_preparedocument.

Ekspresi XPath untuk Mengidentifikasi Node yang Akan Diproses (rowpattern)

Ekspresi XPath yang ditentukan sebagai rowpattern mengidentifikasi sekumpulan simpul dalam dokumen XML. Setiap simpul yang diidentifikasi oleh rowpattern sesuai dengan satu baris dalam set baris yang dihasilkan oleh OPENXML.

Simpul yang diidentifikasi oleh ekspresi XPath dapat menjadi simpul XML apa pun dalam dokumen XML. Jika rowpattern mengidentifikasi sekumpulan elemen dalam dokumen XML, ada satu baris dalam set baris untuk setiap node elemen yang diidentifikasi. Misalnya, jika rowpattern berakhir dalam atribut, baris dibuat untuk setiap node atribut yang dipilih oleh rowpattern.

Deskripsi Set Baris yang Akan Dihasilkan

Skema set baris digunakan oleh OPENXML untuk menghasilkan set baris yang dihasilkan. Anda dapat menggunakan opsi berikut saat menentukan skema set baris.

Menggunakan Format Tabel Edge

Anda harus menggunakan format tabel tepi untuk menentukan skema set baris. Jangan gunakan klausa WITH.

Saat Anda melakukan ini, OPENXML mengembalikan set baris dalam format tabel tepi. Ini disebut sebagai tabel tepi, karena setiap tepi dalam peta pohon dokumen XML yang diurai ke baris dalam set baris.

Tabel Edge mewakili dalam satu tabel struktur dokumen XML terperindas. Struktur ini mencakup elemen dan nama atribut, hierarki dokumen, namespace, dan instruksi pemrosesan. Format tabel edge memungkinkan Anda untuk mendapatkan informasi tambahan yang tidak diekspos melalui metaproperti. Untuk informasi selengkapnya tentang metaproperti, lihat Menentukan Metaproperti di OPENXML.

Informasi tambahan yang disediakan oleh tabel edge memungkinkan Anda menyimpan dan mengkueri tipe data elemen dan atribut, dan jenis node, serta menyimpan dan mengkueri informasi tentang struktur dokumen XML. Dengan informasi tambahan ini, mungkin juga untuk membangun sistem manajemen dokumen XML Anda sendiri.

Dengan menggunakan tabel edge, Anda dapat menulis prosedur tersimpan yang mengambil dokumen XML sebagai input objek besar biner (BLOB), menghasilkan tabel tepi, lalu mengekstrak dan menganalisis dokumen pada tingkat yang lebih rinci. Tingkat terperinci ini dapat mencakup menemukan hierarki dokumen, elemen dan nama atribut, namespace layanan, dan instruksi pemrosesan.

Tabel tepi juga dapat berfungsi sebagai format penyimpanan untuk dokumen XML saat memetakan ke format relasional lainnya tidak logis dan bidang ntext tidak memberikan informasi struktural yang cukup.

Dalam situasi di mana Anda dapat menggunakan pengurai XML untuk memeriksa dokumen XML, Anda bisa menggunakan tabel edge sebagai gantinya untuk mendapatkan informasi yang sama.

Tabel berikut ini menjelaskan struktur tabel tepi.

Nama kolom	Jenis data	Deskripsi
Id	bigint	Adalah ID unik dari simpul dokumen. Elemen akar memiliki nilai ID 0. Nilai ID negatif dicadangkan.
parentid	bigint	Mengidentifikasi induk simpul. Induk yang diidentifikasi oleh ID ini belum tentu merupakan elemen induk. Namun, ini tergantung pada jenis NodeType dari 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.
jenis node	Int	Mengidentifikasi jenis node dan merupakan bilangan bulat yang sesuai dengan penomoran jenis simpul model objek XML (DOM). Berikut ini adalah nilai yang bisa muncul di kolom ini untuk menunjukkan jenis node: 1 = Elemen node 2 = Node atribut 3 = Simpul teks 4 = simpul bagian CDATA 5 = Simpul referensi entitas 6 = Simpul entitas 7 = Memproses simpul instruksi 8 = Node komentar 9 = Simpul dokumen 10 = Node Jenis Dokumen 11 = Simpul Fragmen Dokumen 12 = Notasi node Untuk informasi selengkapnya, lihat topik "nodeType Property" di Microsoft XML (MSXML) SDK.
nama lokal	nvarchar(maks)	Memberikan nama lokal elemen atau atribut. Adalah NULL jika objek DOM tidak memiliki nama.
awalan	nvarchar(maks)	Ini adalah awalan namespace dari nama simpul.
namespaceuri	nvarchar(maks)	URI namespace dari simpul. Jika nilainya NULL, tidak ada namespace yang ada.
Datatype	nvarchar(maks)	Merupakan tipe data aktual dari elemen atau baris atribut dan jika tidak, NULL. Jenis data disimpulkan dari DTD sebaris atau dari skema sebaris.
Sebelumnya	bigint	Adalah ID XML dari elemen saudara kandung sebelumnya. Adalah NULL jika tidak ada saudara kandung sebelumnya langsung.
Teks	ntext	Berisi nilai atribut atau konten elemen dalam formulir teks. Atau NULL, jika entri tabel edge tidak memerlukan nilai.

Menggunakan Klausul WITH untuk Menentukan Tabel yang Ada

Anda dapat menggunakan klausa WITH untuk menentukan nama tabel yang sudah ada. Untuk melakukan ini, cukup tentukan nama tabel yang sudah ada yang skemanya dapat digunakan oleh OPENXML untuk menghasilkan set baris.

Menggunakan Klausul WITH untuk Menentukan Skema

Anda dapat menggunakan Klausul WITH untuk menentukan skema lengkap. Dalam menentukan skema set baris, Anda menentukan nama kolom, jenis datanya, dan pemetaannya ke dokumen XML.

Anda dapat menentukan pola kolom dengan menggunakan parameter ColPattern di SchemaDeclaration. Pola kolom yang ditentukan digunakan untuk memetakan kolom set baris ke simpul XML yang diidentifikasi oleh rowpattern dan juga digunakan untuk menentukan jenis pemetaan.

Jika ColPattern tidak ditentukan untuk kolom, kolom himpunan baris memetakan ke simpul XML dengan nama yang sama, berdasarkan pemetaan yang ditentukan oleh parameter flags. Namun, jika ColPattern ditentukan sebagai bagian dari spesifikasi skema dalam klausa WITH, ColPattern akan menggantikan pemetaan yang ditentukan dalam parameter flags.

Pemetaan Antara Kolom Kumpulan Baris dan Simpul XML

Dalam pernyataan OPENXML, Anda dapat secara opsional menentukan jenis pemetaan, seperti atribut-sentris atau elemen-sentris, antara kolom set baris dan simpul XML yang diidentifikasi oleh rowpattern. Informasi ini digunakan dalam transformasi antara simpul XML dan kolom set baris.

Anda dapat menentukan pemetaan dengan dua cara, dan Anda juga dapat menentukan keduanya:

Dengan menggunakan parameter bendera

Pemetaan yang ditentukan oleh parameter flags mengasumsikan kesesuaian nama di mana simpul XML memetakan ke kolom rowset yang sesuai dengan nama yang sama.
Dengan menggunakan parameter ColPattern

ColPattern, ekspresi XPath, ditentukan sebagai bagian dari SchemaDeclaration dalam klausa WITH. Pemetaan yang ditentukan dalam ColPattern menimpa pemetaan yang ditentukan oleh parameter flags.

ColPattern dapat digunakan untuk menentukan jenis pemetaan, seperti atribut-sentris atau elemen-sentris, yang menggantikan atau meningkatkan pemetaan default yang ditunjukkan oleh flag.

ColPattern ditentukan dalam keadaan berikut:
- Nama kolom dalam set baris data berbeda dari nama elemen atau atribut yang dipetakan. Dalam hal ini, ColPattern digunakan untuk mengidentifikasi elemen XML dan nama atribut tempat kolom kumpulan baris dipetakan.
- Anda ingin memetakan atribut metaproperty ke kolom. Dalam hal ini, ColPattern digunakan untuk mengidentifikasi metaproperti tempat kolom set baris dipetakan. Untuk informasi selengkapnya tentang cara menggunakan metaproperti, lihat Menentukan Metaproperti di OPENXML.

Parameter bendera dan ColPattern bersifat opsional. Jika tidak ada pemetaan yang ditentukan, pemetaan atribut-sentris diasumsikan. Pemetaan yang bersifat sentris atribut adalah nilai default parameter bendera .

Pemetaan yang ber sentris atribut

Mengatur parameter bendera di OPENXML ke 1 (XML_ATTRIBUTES) menentukan pemetaan yang ber sentris atribut . Jika flag berisi XML_ ATTRIBUTES, set baris yang ditampilkan menyediakan atau memanfaatkan baris di mana setiap elemen XML direpresentasikan sebagai baris. Atribut XML dipetakan ke atribut yang ditentukan dalam Deklarasi Skema atau yang disediakan oleh Nama Tabel dari klausa WITH, berdasarkan korespondensi nama. Korespondensi nama berarti bahwa atribut XML dari nama tertentu disimpan dalam kolom di set baris dengan nama yang sama.

Jika nama kolom berbeda dari nama atribut yang dipetakannya, ColPattern harus ditentukan.

Jika atribut XML memiliki kualifikasi namespace, nama kolom dalam set baris juga harus memiliki kualifikasi.

Pemetaan elemen-sentris

Mengatur parameter flags di OPENXML ke 2 (XML_ELEMENTS) menentukan pemetaan yang berpusat pada elemen. Ini mirip dengan pemetaan yang berpusat pada atribut , kecuali untuk perbedaan berikut:

Keselarasan nama dari contoh pemetaan, pemetaan kolom dengan elemen XML yang memiliki nama sama memilih subelemen sederhana, kecuali ada pola tingkat kolom yang ditentukan. Dalam proses pengambilan, jika subelemen kompleks karena berisi subelemen tambahan, kolom diatur ke NULL. Nilai atribut subelemen kemudian diabaikan.
Untuk beberapa sublemen yang memiliki nama yang sama, simpul pertama dikembalikan.

Lihat Juga

sp_xml_preparedocument (Transact-SQL)sp_xml_removedocument (Transact-SQL)OPENXML (Transact-SQL)Data XML (SQL Server)

Last updated on 2017-06-13

Bagikan melalui