OPENXML (Transact-SQL)

  • Artikel

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.

Konvensi sintaks transact-SQL

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:xmltextluapan .

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

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