OPENXML (Transact-SQL)

Artikel
06/22/2024

Berlaku untuk: SQL Server Azure SQL Database 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 OPENROWSET fungsi dapat muncul.

Konvensi sintaks transact-SQL

Sintaks

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

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 `attribute-centric` pemetaan.
`1`	Gunakan pemetaan `attribute-centric` . Dapat dikombinasikan dengan `XML_ELEMENTS`. Dalam hal ini, `attribute-centric` pemetaan diterapkan terlebih dahulu. Selanjutnya, `element-centric` pemetaan diterapkan untuk kolom yang tersisa.
`2`	Gunakan pemetaan `element-centric` . Dapat dikombinasikan dengan `XML_ATTRIBUTES`. Dalam hal ini, `element-centric` pemetaan diterapkan terlebih dahulu. Selanjutnya, `attribute-centric` pemetaan 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

Definisi skema 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 (attribute-centric atau element-centric pemetaan seperti yang ditentukan oleh bendera) terjadi.

Pola XPath yang ditentukan sebagai ColPattern digunakan untuk menentukan sifat khusus pemetaan (untuk attribute-centric dan element-centric pemetaan) 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

Klausul ini WITH menyediakan format set baris (dan informasi pemetaan tambahan sebagaimana diperlukan) dengan menggunakan SchemaDeclaration atau menentukan TableName yang ada. Jika klausul opsional WITH 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 root memiliki nilai `0`ID . 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, node `ParentID` tersebut adalah `NULL`.
`nodetype`	int	Mengidentifikasi jenis node. Nilai ini adalah bilangan bulat yang sesuai dengan penomoran jenis node XML DOM. Jenis node adalah: `1` = Node elemen `2` = Node atribut `3` = Simpul teks
`localname`	nvarchar	Memberikan nama lokal elemen atau atribut. `NULL` jika objek DOM tidak memiliki nama.
`prefix`	nvarchar	Awalan namespace dari nama simpul.
`namespaceuri`	nvarchar	URI namespace simpul. Jika nilainya adalah `NULL`, tidak ada namespace layanan yang ada.
`data type`	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. `NULL` jika tidak ada saudara kandung sebelumnya langsung.
`text`	ntext	Berisi nilai atribut atau konten elemen dalam bentuk teks (atau jika `NULL` 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 attribute-centric pemetaan. 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 set hasilnya.

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

Jika pernyataan yang sama SELECT dijalankan dengan bendera yang diatur ke 2, menunjukkan element-centric pemetaan, 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 set hasilnya.

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, , 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.

element-centric Meskipun pemetaan 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 set hasilnya.

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>. WITH Karena klausa 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;

Contoh: Menggunakan OPENXML

Bagikan melalui

OPENXML (Transact-SQL)

Sintaks

Argumen

idoc

rowpattern

flags

SchemaDeclaration

TableName

Keterangan

Contoh

J. Menggunakan pernyataan SELECT dasar dengan OPENXML

B. Tentukan ColPattern untuk pemetaan antara kolom dan atribut XML

C. Mendapatkan hasil dalam format tabel edge

Saran dan Komentar

Sumber Daya Tambahan:

Bagikan melalui

OPENXML (Transact-SQL)

Sintaks

Argumen

idoc

rowpattern

flags

SchemaDeclaration

TableName

Keterangan

Contoh

J. Menggunakan pernyataan SELECT dasar dengan OPENXML

B. Tentukan ColPattern untuk pemetaan antara kolom dan atribut XML

C. Mendapatkan hasil dalam format tabel edge

Konten terkait

Saran dan Komentar

Sumber Daya Tambahan: