OPENXML (SQL Server)
Berlaku untuk:
SQL ServerAzure SQL Database Azure SQL Managed Instance
OPENXML adalah kata kunci Transact-SQL, yang menyediakan set baris melalui dokumen XML dalam memori yang mirip 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 handel ke dokumen yang diurai yang siap dikonsumsi. Dokumen yang diurai adalah representasi pohon model objek dokumen (DOM) dari berbagai simpul dalam dokumen XML. Handel 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 mundur 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.
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 memperlihatkan SELECT pernyataan dengan OPENXML yang 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.
Parameter OPENXML
Parameter untuk OPENXML mencakup yang berikut ini:
Handel 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
Handel 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 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 edge 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 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 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. |
| 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 = 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 Tipe Dokumen 11 = Simpul 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) | Adalah awalan namespace dari nama simpul. |
| namespaceuri | nvarchar(maks) | Adalah URI namespace simpul. Jika nilainya NULL, tidak ada namespace layanan yang ada. |
| Datatype | nvarchar(maks) | Adalah jenis data aktual dari elemen atau baris atribut dan sebaliknya 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. |
| 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 kolom, kolom set baris memetakan ke simpul XML dengan nama yang sama, berdasarkan pemetaan yang ditentukan oleh parameter bendera . Namun, jika ColPattern ditentukan sebagai bagian dari spesifikasi skema dalam klausa WITH, ColPattern akan menimpa pemetaan yang ditentukan dalam parameter bendera .
Memetakan antara kolom set 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 bendera mengasumsikan korespondensi nama di mana simpul XML memetakan ke kolom set baris 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 bendera .
ColPattern dapat digunakan untuk menentukan jenis pemetaan, seperti atribut-sentris atau elemen-sentris, yang menimpa atau meningkatkan pemetaan default yang ditunjukkan oleh bendera.
ColPattern ditentukan dalam keadaan berikut:
Nama kolom dalam set baris berbeda dari elemen atau nama 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 bendera berisi XML_ ATTRIBUTES, set baris yang diekspos menyediakan atau menggunakan baris di mana setiap elemen XML diwakili sebagai baris. Atribut XML dipetakan ke atribut yang ditentukan dalam SchemaDeclaration atau yang disediakan oleh TableName 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 bendera di OPENXML ke 2 (XML_ELEMENTS) menentukan pemetaan yang ber sentris elemen. Ini mirip dengan pemetaan yang berpusat pada atribut, kecuali untuk perbedaan berikut:
Korespondensi nama dari contoh pemetaan, pemetaan kolom ke elemen XML dengan nama yang sama memilih subelemen yang tidak lengkap, kecuali pola tingkat kolom 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.