OPENXML (Transact-SQL)
Berlaku untuk:SQL ServerAzure SQL DatabaseAzure 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.
Sintaksis
OPENXML ( idoc int [ in ]
, rowpattern nvarchar [ in ]
, [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]
Catatan
Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.
Argumen
idoc
Handel dokumen representasi internal dokumen XML. Representasi internal dokumen XML dibuat dengan memanggil sp_xml_preparedocument
.
rowpattern
Pola XPath yang digunakan untuk mengidentifikasi simpul yang akan diproses sebagai baris. Simpul berasal dari dokumen XML yang handelnya diteruskan dalam parameter idoc .
flags
Menunjukkan pemetaan yang digunakan antara data XML dan set baris relasional, dan bagaimana kolom tumpahan diisi. bendera adalah parameter input opsional, dan bisa menjadi salah satu nilai berikut.
Nilai byte | Deskripsi |
---|---|
0 | Default untuk 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 (LOGICAL OR) dengan XML_ATTRIBUTES atau XML_ELEMENTS. Dalam konteks pengambilan, bendera ini menunjukkan bahwa data yang digunakan tidak boleh disalin ke properti @mp:xmltext luapan . |
SchemaDeclaration
Adalah definisi skema dari formulir: ColNameColType [ ColPattern | MetaProperty ] [ , ColNameColType [ ColPattern | MetaProperty ] ... ]
ColName
Nama kolom dalam set baris.
ColType
Jenis data SQL Server dari kolom dalam set baris. Jika jenis kolom berbeda dari jenis data xml yang mendasar dari atribut , pemakaian jenis terjadi.
ColPattern
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-sentris seperti yang ditentukan oleh bendera) terjadi.
Pola XPath 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 XPath umum yang ditentukan sebagai ColPattern juga mendukung metaproperti.
MetaProperty
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 layanan) tentang simpul XML. Metaproperti ini memberikan lebih banyak informasi daripada yang terlihat dalam representasi tekstual.
TableName
Nama tabel yang dapat diberikan (alih-alih 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 sesuai kebutuhan) 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 sebagainya) dalam satu tabel.
Tabel berikut ini menjelaskan struktur tabel tepi .
Nama kolom | Jenis data | Deskripsi |
---|---|---|
id | bigint | ID unik 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, tetapi tergantung pada NodeType simpul yang induknya diidentifikasi oleh ID ini. Misalnya, jika simpul adalah simpul teks, induknya mungkin merupakan node 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 = Node elemen 2 = Node atribut 3 = Simpul teks |
nama lokal | nvarchar | Memberikan nama lokal elemen atau atribut. Adalah NULL jika objek DOM tidak memiliki nama. |
Awalan | nvarchar | Awalan namespace dari nama simpul. |
namespaceuri | nvarchar | URI namespace simpul. Jika nilainya NULL, tidak ada namespace layanan yang ada. |
Datatype | nvarchar | Jenis data aktual dari elemen atau baris atribut, jika tidak, adalah NULL. Jenis data disimpulkan dari DTD sebaris atau dari skema sebaris. |
Prev | bigint | ID XML dari elemen saudara kandung sebelumnya. Adalah NULL jika tidak ada saudara kandung sebelumnya langsung. |
text | ntext | Berisi nilai atribut atau konten elemen dalam bentuk teks (atau NULL jika entri tabel edge tidak memerlukan nilai). |
Contoh
J. Menggunakan pernyataan SELECT dasar 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 bernama CustomerID
atau ContactName
dalam dokumen XML.
Berikut adalah hasil yang ditetapkan.
CustomerID ContactName
---------- -----------
NULL NULL
NULL NULL
B. Tentukan 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
adalah atribut elemen <OrderDetails>
. Namun, OrderID
, CustomerID
, dan OrderDate
adalah atribut dari elemen induk (<Orders>
).
ColPattern opsional ditentukan untuk pemetaan berikut:
OrderID
, ,CustomerID
danOrderDate
dalam peta set baris ke atribut induk simpul yang diidentifikasi oleh rowpattern dalam dokumen XML.Kolom
ProdID
dalam set baris memetakan keProductID
atribut, danQty
kolom di set baris memetakan keQuantity
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 node 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;
Baca juga
Saran dan Komentar
https://aka.ms/ContentUserFeedback.
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat:Kirim dan lihat umpan balik untuk