OPENXML (SQL Server)

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance Database SQL di Microsoft Fabric

OPENXML adalah kata kunci Transact-SQL yang menyediakan kumpulan baris data atas dokumen XML dalam memori, yang serupa dengan tabel atau tampilan. 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 handle ke dokumen hasil penguraian yang siap digunakan. Dokumen yang diurai adalah representasi pohon model objek dokumen (DOM) dari berbagai simpul dalam dokumen XML. Penangan dokumen diteruskan ke OPENXML. OPENXML kemudian menyediakan tampilan kumpulan baris dokumen, berdasarkan parameter yang diteruskan ke dokumen tersebut.

Catatan

sp_xml_preparedocument menggunakan versi pengurai MSXML yang diperbarui SQL, Msxmlsql.dll. Versi pengurai MSXML ini dirancang untuk mendukung SQL Server dan tetap kompatibel dengan versi sebelumnya dari MSXML 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.

Catatan

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. Contohnya juga menunjukkan pernyataan SELECT dengan OPENXML untuk mengambil 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.

Struktur pohon XML yang telah diurai.

Parameter OPENXML

Parameter untuk OPENXML mencakup yang berikut ini:

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

Handel dokumen dikembalikan oleh prosedur tersimpan sp_xml_preparedocument.

Ekspresi XPath untuk mengidentifikasi simpul 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 kumpulan 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 edge untuk menentukan skema rowset. Jangan gunakan klausa WITH.

Saat Anda melakukan ini, OPENXML mengembalikan set baris dalam format tabel tepi. Ini disebut sebagai edge table, karena setiap edge dalam pohon dokumen XML yang telah di-parse dipetakan ke sebuah baris dalam rowset.

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 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 dikhususkan.
parentid	bigint	Mengidentifikasi induk simpul. Induk yang diidentifikasi oleh ID ini belum tentu merupakan elemen induk. Namun, hal ini bergantung pada tipe node dari node 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 = Node elemen 2 = Node atribut 3 = Node 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 = node Fragmen Dokumen 12 = Node notasi Untuk informasi selengkapnya, lihat artikel "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)	Merupakan prefiks namespace dari nama node.
namespaceuri	nvarchar(maks)	Merupakan URI namespace dari node. Jika nilainya NULL, tidak ada namespace.
Datatype	nvarchar(maks)	Merupakan jenis data aktual dari elemen atau baris atribut, dan jika tidak, bernilai NULL. Jenis data disimpulkan dari DTD sebaris atau dari skema sebaris.
Sebelumnya	bigint	Adalah ID XML dari elemen saudara kandung sebelumnya. Apakah NULL jika tidak ada saudara kandung sebelumnya langsung.
text	ntext	Berisi nilai atribut atau konten elemen dalam formulir teks. Atau NULL, jika entri tabel edge tidak memerlukan nilai.

Gunakan klausa WITH untuk menentukan tabel yang sudah 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.

Gunakan klausa 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 suatu kolom, kolom rowset dipetakan ke node XML dengan nama yang sama, berdasarkan pemetaan yang ditentukan oleh parameter flags. Namun, jika ColPattern ditentukan sebagai bagian dari spesifikasi skema dalam klausa WITH, hal itu akan menimpa pemetaan yang ditentukan dalam parameter flags.

Pemetaan antara kolom kumpulan baris dan node 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 flags

Pemetaan yang ditentukan oleh parameter flags mengasumsikan adanya korespondensi nama, di mana node XML dipetakan ke kolom rowset yang bersesuaian yang namanya sama.
Dengan menggunakan parameter ColPattern

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

ColPattern dapat digunakan untuk menentukan jenis pemetaan, seperti berpusat pada atribut atau berpusat pada elemen, yang menimpa atau menyempurnakan pemetaan default yang ditentukan oleh flag.

ColPattern ditentukan dalam keadaan berikut:
- Nama kolom dalam rowset berbeda dari nama elemen atau atribut yang menjadi tujuan pemetaannya. Dalam kasus ini, ColPattern digunakan untuk mengidentifikasi elemen XML dan nama atribut yang menjadi tujuan pemetaan kolom rowset.
- Anda ingin memetakan atribut metaproperty ke kolom. Dalam hal ini, ColPattern digunakan untuk mengidentifikasi metaproperti yang menjadi tujuan pemetaan kolom rowset. 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 berpusat pada atribut

Mengatur parameter flag di OPENXML ke 1 (XML_ATTRIBUTES) menetapkan pemetaan berpusat pada atribut. Jika flags berisi XML_ ATTRIBUTES, rowset yang diekspos menyediakan atau mengonsumsi baris, dengan setiap elemen XML direpresentasikan sebagai satu baris. Atribut XML dipetakan ke atribut yang ditentukan dalam SchemaDeclaration atau yang disediakan oleh TableName pada klausa WITH, berdasarkan kesesuaian nama. Korespondensi nama berarti bahwa atribut XML dari nama tertentu disimpan dalam kolom pada himpunan 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 flag di OPENXML menjadi 2 (XML_ELEMENTS) menetapkan pemetaan berpusat pada elemen. Ini mirip dengan pemetaan yang berpusat pada atribut, kecuali untuk perbedaan berikut:

Dalam contoh pemetaan korespondensi nama, pemetaan kolom ke elemen XML dengan nama yang sama akan memilih subelemen yang tidak kompleks, kecuali jika ditentukan pola pada tingkat kolom. 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.

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2026-08-25