Contoh: Menggunakan OPENXML
Berlaku untuk:
SQL ServerAzure SQL Database Azure SQL Managed Instance
Contoh dalam artikel ini memperlihatkan bagaimana OPENXML digunakan untuk membuat tampilan kumpulan baris dokumen XML. Untuk informasi tentang sintaks OPENXML, lihat OPENXML (Transact-SQL). Contoh menunjukkan semua aspek OPENXML, tetapi jangan tentukan metaproperti di OPENXML. Untuk informasi selengkapnya tentang cara menentukan metaproperti di OPENXML, lihat Menentukan Metaproperti di OPENXML.
Contoh
Saat mengambil data, rowpattern digunakan untuk mengidentifikasi simpul dalam dokumen XML yang menentukan baris. Selain itu, rowpattern dinyatakan dalam bahasa pola XPath yang digunakan dalam implementasi MSXML XPath. Misalnya, jika pola berakhir dalam elemen atau atribut, baris dibuat untuk setiap elemen atau node atribut yang dipilih oleh rowpattern.
Nilai bendera menyediakan pemetaan default. Jika tidak ada ColPattern yang ditentukan dalam SchemaDeclaration, pemetaan yang ditentukan dalam bendera diasumsikan . Nilai bendera diabaikan jika ColPattern ditentukan dalam SchemaDeclaration. ColPattern yang ditentukan menentukan pemetaan, atribut-sentris atau berpusat pada elemen, dan juga perilaku dalam menangani data luapan dan tidak dikonsumsi.
J. Menjalankan pernyataan SELECT dengan OPENXML
Dokumen XML dalam contoh ini terdiri dari <Customer>elemen , , <Order>dan <OrderDetail> . Pernyataan OPENXML mengambil informasi pelanggan dalam set baris dua kolom, CustomerID dan ContactName, dari dokumen XML.
Pertama, prosedur tersimpan sp_xml_preparedocument dipanggil untuk mendapatkan handel dokumen. Handel dokumen ini diteruskan ke OPENXML.
Pernyataan OPENXML mengilustrasikan hal berikut:
rowpattern (/ROOT/Customer) mengidentifikasi simpul yang akan diproses
<Customer>.Nilai parameter bendera diatur ke 1 dan menunjukkan pemetaan atribut-sentris. Akibatnya, atribut XML memetakan ke kolom dalam set baris yang ditentukan dalam SchemaDeclaration.
Dalam SchemaDeclaration, dalam klausa WITH, nilai ColName yang ditentukan cocok dengan nama atribut XML yang sesuai. Oleh karena itu, parameter ColPattern tidak ditentukan dalam SchemaDeclaration.
Pernyataan SELECT kemudian mengambil semua kolom dalam set baris yang disediakan oleh OPENXML.
DECLARE @DocHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<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 Gonzalez">
<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 @DocHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@DocHandle, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20));
EXEC sp_xml_removedocument @DocHandle;
Ini adalah hasilnya:
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzalez
<Customer> Karena elemen tidak memiliki subelemen apa pun, jika pernyataan SELECT yang sama dijalankan dengan bendera yang diatur ke 2 untuk menunjukkan pemetaan yang ber sentris elemen, nilai CustomerID dan ContactName untuk kedua pelanggan dikembalikan sebagai NULL.
@xmlDocument juga dapat berjenis xml atau dari jenis (n)varchar(maks).
Jika <CustomerID> dan <ContactName> dalam dokumen XML adalah subelemen, pemetaan elemen-sentris mengambil nilai.
DECLARE @XmlDocumentHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer>
<CustomerID>VINET</CustomerID>
<ContactName>Paul Henriot</ContactName>
<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</CustomerID>
<ContactName>Carlos Gonzalez</ContactName>
<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 @XmlDocumentHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer',2)
WITH (CustomerID varchar(10),
ContactName varchar(20));
EXEC sp_xml_removedocument @XmlDocumentHandle;
Ini adalah hasilnya:
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzalez
Handel dokumen yang dikembalikan oleh sp_xml_preparedocument valid selama batch dan bukan sesi.
B. Tentukan ColPattern untuk pemetaan antara kolom set baris dan atribut dan elemen XML
Contoh ini menunjukkan bagaimana pola XPath ditentukan dalam parameter ColPattern opsional untuk menyediakan pemetaan antara kolom set baris dan atribut dan elemen XML.
Dokumen XML dalam contoh ini terdiri dari <Customer>elemen , , <Order>dan <OrderDetail> . Pernyataan OPENXML mengambil informasi pelanggan dan pesanan sebagai set baris (CustomerID, OrderDate, ProdID, dan Qty) dari dokumen XML.
Pertama, prosedur tersimpan sp_xml_preparedocument dipanggil untuk mendapatkan handel dokumen. Handel dokumen ini diteruskan ke OPENXML.
Pernyataan OPENXML mengilustrasikan hal berikut:
- rowpattern (/ROOT/Customer/Order/OrderDetail) mengidentifikasi simpul yang akan diproses
<OrderDetail>.
Untuk ilustrasi, nilai parameter bendera diatur ke 2 dan menunjukkan pemetaan yang ber sentris elemen. Namun, pemetaan yang ditentukan dalam ColPattern menimpa pemetaan ini. Artinya, pola XPath yang ditentukan dalam ColPattern memetakan kolom dalam set baris ke atribut. Ini menghasilkan pemetaan atribut-sentris.
Dalam SchemaDeclaration, dalam klausul WITH, ColPattern juga ditentukan dengan parameter ColName dan ColType . ColPattern opsional adalah pola XPath yang ditentukan dan menunjukkan hal berikut:
Kolom OrderID, CustomerID, dan OrderDate di peta set baris ke atribut induk simpul yang diidentifikasi oleh rowpattern, dan rowpattern mengidentifikasi
<OrderDetail>simpul. Oleh karena itu, kolom CustomerID dan OrderDate memetakan ke atribut CustomerID dan OrderDate dari<Order>elemen .Kolom ProdID dan Qty di peta set baris ke atribut ProductID dan Kuantitas simpul yang diidentifikasi dalam rowpattern.
Pernyataan SELECT kemudian mengambil semua kolom dalam set baris yang disediakan oleh OPENXML.
DECLARE @XmlDocumentHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<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 Gonzalez">
<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 @XmlDocumentHandle OUTPUT, @XmlDocument;
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity');
EXEC sp_xml_removedocument @XmlDocumentHandle;
Ini adalah 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
Pola XPath yang ditentukan sebagai ColPattern juga dapat ditentukan untuk memetakan elemen XML ke kolom set baris. Ini menghasilkan pemetaan yang ber sentris elemen. Dalam contoh berikut, dokumen <CustomerID> XML dan <OrderDate> merupakan subelemen elemen <Orders> . Karena ColPattern menimpa pemetaan yang ditentukan dalam parameter bendera , parameter bendera tidak ditentukan dalam OPENXML.
DECLARE @docHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order EmployeeID="5" >
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<OrderDate>1996-07-04T00:00:00</OrderDate>
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzalez">
<Order EmployeeID="3" >
<OrderID>10283</OrderID>
<CustomerID>LILAS</CustomerID>
<OrderDate>1996-08-16T00:00:00</OrderDate>
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID varchar(10) '../CustomerID',
OrderDate datetime '../OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity');
EXEC sp_xml_removedocument @docHandle;
C. Menggabungkan pemetaan yang ber sentris atribut dan elemen-sentris
Dalam contoh ini, parameter bendera diatur ke 3 dan menunjukkan bahwa pemetaan atribut-sentris dan elemen-sentris akan diterapkan. Dalam hal ini, pemetaan atribut-sentris diterapkan terlebih dahulu, dan kemudian pemetaan yang bersentrik elemen diterapkan untuk semua kolom yang belum ditangani.
DECLARE @docHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument =N'<ROOT>
<Customer CustomerID="VINET" >
<ContactName>Paul Henriot</ContactName>
<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 Gonzalez</ContactName>
<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 @docHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer',3)
WITH (CustomerID varchar(10),
ContactName varchar(20));
EXEC sp_xml_removedocument @docHandle;
Ini adalah hasilnya
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzalez
Pemetaan yang bersifat sentris atribut diterapkan untuk CustomerID. Tidak ada atribut ContactName dalam <Customer> elemen . Oleh karena itu, pemetaan elemen-sentris diterapkan.
D. Tentukan fungsi text() XPath sebagai ColPattern
Dokumen XML dalam contoh ini terdiri dari <Customer> elemen dan <Order> . Pernyataan OPENXML mengambil set baris yang terdiri dari atribut oid dari <Order> elemen, ID induk simpul yang diidentifikasi oleh rowpattern, dan string nilai daun dari konten elemen.
Pertama, prosedur tersimpan sp_xml_preparedocument dipanggil untuk mendapatkan handel dokumen. Handel dokumen ini diteruskan ke OPENXML.
Pernyataan OPENXML mengilustrasikan hal berikut:
rowpattern (/root/Customer/Order) mengidentifikasi simpul yang akan diproses
<Order>.Nilai parameter bendera diatur ke 1 dan menunjukkan pemetaan atribut-sentris. Akibatnya, atribut XML memetakan ke kolom set baris yang ditentukan dalam SchemaDeclaration.
Dalam SchemaDeclaration dalam klausa WITH, nama kolom kumpulan baris oid dan jumlah cocok dengan nama atribut XML yang sesuai. Oleh karena itu, parameter ColPattern tidak ditentukan. Untuk kolom komentar di set baris, fungsi XPath,
text(), ditentukan sebagai ColPattern. Ini menimpa pemetaan yang berfokus pada atribut yang ditentukan dalam bendera, dan kolom berisi string nilai daun dari konten elemen.
Pernyataan SELECT kemudian mengambil semua kolom dalam set baris yang disediakan oleh OPENXML.
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
--sample XML document
SET @xmlDocument =N'<root>
<Customer cid= "C1" name="Janine" city="Issaquah">
<Order oid="O1" date="1/20/1996" amount="3.5" />
<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
</Order>
</Customer>
<Customer cid="C2" name="Ursula" city="Oelde" >
<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
white red">
<Urgency>Important</Urgency>
Happy Customer.
</Order>
<Order oid="O4" date="1/20/1996" amount="10000"/>
</Customer>
</root>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/root/Customer/Order', 1)
WITH (oid char(5),
amount float,
comment ntext 'text()');
EXEC sp_xml_removedocument @docHandle;
Ini adalah hasilnya:
oid amount comment
----- ----------- -----------------------------
O1 3.5 NULL
O2 13.4 Customer was very satisfied
O3 100.0 Happy Customer.
O4 10000.0 NULL
E. Tentukan TableName dalam klausa WITH
Contoh ini menentukan TableName dalam klausul WITH alih-alih SchemaDeclaration. Ini berguna jika Anda memiliki tabel yang memiliki struktur yang Anda inginkan dan tidak ada pola kolom, parameter ColPattern , diperlukan.
Dokumen XML dalam contoh ini terdiri dari <Customer> elemen dan <Order> . Pernyataan OPENXML mengambil informasi pesanan dalam set baris tiga kolom (oid, tanggal, dan jumlah) dari dokumen XML.
Pertama, prosedur tersimpan sp_xml_preparedocument dipanggil untuk mendapatkan handel dokumen. Handel dokumen ini diteruskan ke OPENXML.
Pernyataan OPENXML mengilustrasikan hal berikut:
rowpattern (/root/Customer/Order) mengidentifikasi simpul yang akan diproses
<Order>.Tidak ada SchemaDeclaration dalam klausul WITH. Sebagai gantinya, nama tabel ditentukan. Oleh karena itu, skema tabel digunakan sebagai skema set baris.
Nilai parameter bendera diatur ke 1 dan menunjukkan pemetaan atribut-sentris. Oleh karena itu, atribut elemen, yang diidentifikasi oleh rowpattern, memetakan ke kolom set baris dengan nama yang sama.
Pernyataan SELECT kemudian mengambil semua kolom dalam set baris yang disediakan oleh OPENXML.
-- Create a test table. This table schema is used by OPENXML as the
-- rowset schema.
CREATE TABLE T1(oid char(5), date datetime, amount float);
GO
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
-- Sample XML document
SET @xmlDocument =N'<root>
<Customer cid= "C1" name="Janine" city="Issaquah">
<Order oid="O1" date="1/20/1996" amount="3.5" />
<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very
satisfied</Order>
</Customer>
<Customer cid="C2" name="Ursula" city="Oelde" >
<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
white red">
<Urgency>Important</Urgency>
</Order>
<Order oid="O4" date="1/20/1996" amount="10000"/>
</Customer>
</root>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/root/Customer/Order', 1)
WITH T1;
EXEC sp_xml_removedocument @docHandle;
Ini adalah hasilnya:
oid date amount
----- --------------------------- ----------
O1 1996-01-20 00:00:00.000 3.5
O2 1997-04-30 00:00:00.000 13.4
O3 1999-07-14 00:00:00.000 100.0
O4 1996-01-20 00:00:00.000 10000.0
F. Mendapatkan hasil dalam format tabel tepi
Dalam contoh ini, klausa WITH tidak ditentukan dalam pernyataan OPENXML. Akibatnya, set baris yang dihasilkan oleh OPENXML memiliki format tabel tepi. Pernyataan SELECT mengembalikan semua kolom dalam tabel edge.
Contoh dokumen XML dalam contoh terdiri dari <Customer>elemen , , <Order>dan <OrderDetail> .
Pertama, prosedur tersimpan sp_xml_preparedocument dipanggil untuk mendapatkan handel dokumen. Handel dokumen ini diteruskan ke OPENXML.
Pernyataan OPENXML mengilustrasikan hal berikut:
rowpattern (/ROOT/Customer) mengidentifikasi simpul yang akan diproses
<Customer>.Klausa WITH tidak disediakan. Oleh karena itu, OPENXML mengembalikan set baris dalam format tabel edge.
Pernyataan SELECT kemudian mengambil semua kolom dalam tabel edge.
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
SET @xmlDocument = N'<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 Gonzalez">
<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 @docHandle OUTPUT, @xmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer');
EXEC sp_xml_removedocument @docHandle;
Hasilnya dikembalikan sebagai tabel edge. Anda dapat menulis kueri terhadap tabel edge untuk mendapatkan informasi. Contohnya:
Kueri berikut mengembalikan jumlah simpul Pelanggan dalam dokumen. Karena klausa WITH tidak ditentukan, OPENXML mengembalikan tabel edge. Pernyataan SELECT meminta tabel edge.
SELECT count(*) FROM OPENXML(@docHandle, '/') WHERE localname = 'Customer';Kueri berikut mengembalikan nama lokal simpul XML jenis elemen.
SELECT distinct localname FROM OPENXML(@docHandle, '/') WHERE nodetype = 1 ORDER BY localname;
G. Tentukan rowpattern yang diakhir dengan atribut
Dokumen XML dalam contoh ini terdiri dari <Customer>elemen , , <Order>dan <OrderDetail> . Pernyataan OPENXML mengambil informasi tentang detail pesanan dalam set baris tiga kolom (ProductID, Quantity, dan OrderID) dari dokumen XML.
Pertama, sp_xml_preparedocument dipanggil untuk mendapatkan handel dokumen. Handel dokumen ini diteruskan ke OPENXML.
Pernyataan OPENXML mengilustrasikan hal berikut:
rowpattern (/ROOT/Customer/Order/OrderDetail/@ProductID) berakhir dengan atribut XML, ProductID. Dalam kumpulan baris yang dihasilkan, baris dibuat untuk setiap simpul atribut yang dipilih dalam dokumen XML.
Dalam contoh ini, parameter bendera tidak ditentukan. Sebagai gantinya, pemetaan ditentukan oleh parameter ColPattern .
Dalam SchemaDeclaration dalam klausul WITH, ColPattern juga ditentukan dengan parameter ColName dan ColType . ColPattern opsional adalah pola XPath yang ditentukan untuk menunjukkan hal berikut:
Pola XPath (.) yang ditentukan sebagai ColPattern untuk kolom ProdID di set baris mengidentifikasi simpul konteks, simpul saat ini. Sesuai rowpattern yang ditentukan, itu adalah atribut ProductID dari
<OrderDetail>elemen .ColPattern, .. /@Quantity, yang ditentukan untuk kolom Qty dalam set baris mengidentifikasi atribut Kuantitas induk,
<OrderDetail>, simpul simpul konteks, <ProductID>.Demikian pula, ColPattern, .. /.. /@OrderID, yang ditentukan untuk kolom OID dalam set baris mengidentifikasi atribut OrderID induk,
<Order>, dari simpul induk simpul konteks. Node induk adalah<OrderDetail>, dan node konteksnya adalah<ProductID>.
Pernyataan SELECT kemudian mengambil semua kolom dalam set baris yang disediakan oleh OPENXML.
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
--Sample XML document
SET @xmlDocument =N'<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 Gonzalez">
<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 @docHandle OUTPUT, @xmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail/@ProductID')
WITH ( ProdID int '.',
Qty int '../@Quantity',
OID int '../../@OrderID');
EXEC sp_xml_removedocument @docHandle;
Ini adalah hasilnya:
ProdID Qty OID
----------- ----------- -------
11 12 10248
42 10 10248
72 3 10283
H. Tentukan dokumen XML yang memiliki beberapa simpul teks
Jika Anda memiliki beberapa simpul teks dalam dokumen XML, pernyataan SELECT dengan ColPattern, text(), hanya mengembalikan simpul teks pertama, bukan semuanya. Contohnya:
DECLARE @h int;
EXEC sp_xml_preparedocument @h OUTPUT,
N'<root xmlns:a="urn:1">
<a:Elem abar="asdf">
T<a>a</a>U
</a:Elem>
</root>',
'<ns xmlns:b="urn:1" />';
SELECT * FROM openxml(@h, '/root/b:Elem')
WITH (Col1 varchar(20) 'text()');
EXEC sp_xml_removedocument @h;
Pernyataan SELECT mengembalikan T sebagai hasilnya, dan bukan TaU.
I. Tentukan tipe data XML dalam klausa WITH
Dalam klausul WITH, pola kolom yang dipetakan ke kolom tipe data xml , baik dititik atau tidak dititip, harus mengembalikan urutan kosong atau urutan elemen, instruksi pemrosesan, simpul teks, dan komentar. Data dilemparkan ke jenis data xml .
Dalam contoh berikut, deklarasi skema tabel dalam klausa WITH menyertakan kolom jenis xml .
DECLARE @h int;
DECLARE @x xml;
set @x = '<Root>
<row id="1"><lname>Duffy</lname>
<Address>
<Street>111 Maple</Street>
<City>Seattle</City>
</Address>
</row>
<row id="2"><lname>Wang</lname>
<Address>
<Street>222 Pine</Street>
<City>Bothell</City>
</Address>
</row>
</Root>';
EXEC sp_xml_preparedocument @h output, @x;
SELECT *
FROM OPENXML (@h, '/Root/row', 10)
WITH (id int '@id',
lname varchar(30),
xmlname xml 'lname',
OverFlow xml '@mp:xmltext');
EXEC sp_xml_removedocument @h;
Secara khusus, Anda meneruskan variabel jenis xml (@x) ke fungsi sp_xml_preparedocument().
Ini adalah hasilnya:
id lname xmlname OverFlow
--- ------- ------------------------------ -------------------------------
1 Duffy <lname>Duffy</lname> <row><Address>
<Street>111 Maple</Street>
<City>Seattle</City>
</Address></row>
2 Wang <lname>Wang</lname> <row><Address>
<Street>222 Pine</Street>
<City>Bothell</City>
</Address></row>
Perhatikan hal-hal berikut dari hasilnya:
Untuk kolom lname jenis varchar(30), nilainya diambil dari elemen yang
<lname>sesuai.Untuk kolom xmlname dari jenis xml, elemen nama yang sama dikembalikan sebagai nilainya.
Bendera diatur ke 10. 10 berarti 2 + 8, di mana 2 menunjukkan pemetaan yang berpusat pada elemen dan 8 menunjukkan bahwa hanya data XML yang tidak dikonsumsi yang harus ditambahkan ke kolom OverFlow yang ditentukan dalam klausa WITH. Jika Anda mengatur bendera ke 2, seluruh dokumen XML disalin ke kolom OverFlow yang ditentukan dalam klausa WITH.
Jika kolom dalam klausul WITH adalah kolom XML yang diketik dan instans XML tidak mengonfirmasi ke skema, kesalahan dikembalikan.
j. Mengambil nilai individual dari atribut multinilai
Dokumen XML dapat memiliki atribut yang multinila. Misalnya, atribut IDREFS dapat dinilalui. Dalam dokumen XML, nilai atribut multinilai ditentukan sebagai string, dengan nilai dipisahkan oleh spasi. Dalam dokumen XML berikut, atribut attends elemen <Student dan atribut attendedBy> class <> multivalued. Mengambil nilai individual dari atribut XML multinilai dan menyimpan setiap nilai dalam baris terpisah dalam database memerlukan pekerjaan ekstra. Contoh ini menunjukkan prosesnya.
Contoh dokumen XML ini terdiri dari elemen-elemen berikut:
<Pelajar>
Id (ID siswa), nama, dan menghadiri atribut. Atribut attends adalah atribut multinilai.
<Kelas>
Atribut id (ID kelas), nama, dan attendedBy . Atribut attendedBy adalah atribut multinilai.
Atribut attends di <Student> dan atribut attendedBy di <Class> mewakili hubungan m:n antara tabel Siswa dan Kelas. Siswa dapat mengikuti banyak kelas dan kelas dapat memiliki banyak siswa.
Asumsikan bahwa Anda ingin menghancurkan dokumen ini dan menyimpannya dalam database seperti yang diperlihatkan dalam hal berikut:
<Student>Simpan data dalam tabel Siswa.<Class>Simpan data dalam tabel Kursus.Simpan data hubungan m:n, antara Siswa dan Kelas, dalam tabel CourseAttendence. Lebih banyak pekerjaan diperlukan untuk mengekstrak nilai. Untuk mengambil informasi ini dan menyimpannya dalam tabel, gunakan prosedur tersimpan ini:
Insert_Idrefs_Values
Menyisipkan nilai ID kursus dan ID siswa dalam tabel CourseAttendence.
Extract_idrefs_values
Mengekstrak ID siswa individu dari setiap <elemen Kursus> . Tabel edge digunakan untuk mengambil nilai-nilai ini.
Berikut langkah-langkahnya:
-- Create these tables:
DROP TABLE CourseAttendance;
DROP TABLE Students;
DROP TABLE Courses;
GO
CREATE TABLE Students(
id varchar(5) primary key,
name varchar(30)
);
GO
CREATE TABLE Courses(
id varchar(5) primary key,
name varchar(30),
taughtBy varchar(5)
);
GO
CREATE TABLE CourseAttendance(
id varchar(5) references Courses(id),
attendedBy varchar(5) references Students(id),
constraint CourseAttendance_PK primary key (id, attendedBy)
);
GO
-- Create these stored procedures:
DROP PROCEDURE f_idrefs;
GO
CREATE PROCEDURE f_idrefs
@t varchar(500),
@idtab varchar(50),
@id varchar(5)
AS
DECLARE @sp int;
DECLARE @att varchar(5);
SET @sp = 0;
WHILE (LEN(@t) > 0)
BEGIN
SET @sp = CHARINDEX(' ', @t+ ' ');
SET @att = LEFT(@t, @sp-1);
EXEC('INSERT INTO '+@idtab+' VALUES ('''+@id+''', '''+@att+''')');
SET @t = SUBSTRING(@t+ ' ', @sp+1, LEN(@t)+1-@sp);
END;
GO
DROP PROCEDURE fill_idrefs
GO
CREATE PROCEDURE fill_idrefs
@xmldoc int,
@xpath varchar(100),
@from varchar(50),
@to varchar(50),
@idtable varchar(100)
AS
DECLARE @t varchar(500);
DECLARE @id varchar(5);
/* Temporary Edge table */
SELECT *
INTO #TempEdge
FROM OPENXML(@xmldoc, @xpath);
DECLARE fillidrefs_cursor CURSOR FOR
SELECT CAST(iv.text AS nvarchar(200)) AS id,
CAST(av.text AS nvarchar(4000)) AS refs
FROM #TempEdge c, #TempEdge i,
#TempEdge iv, #TempEdge a, #TempEdge av
WHERE c.id = i.parentid
AND UPPER(i.localname) = UPPER(@from)
AND i.id = iv.parentid
AND c.id = a.parentid
AND UPPER(a.localname) = UPPER(@to)
AND a.id = av.parentid;
OPEN fillidrefs_cursor
FETCH NEXT FROM fillidrefs_cursor INTO @id, @t;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
execute f_idrefs @t, @idtable, @id;
END
FETCH NEXT FROM fillidrefs_cursor INTO @id, @t
END;
CLOSE fillidrefs_cursor;
DEALLOCATE fillidrefs_cursor;
Go
-- This is the sample document that is shredded and the data is stored in the preceding tables.
DECLARE @h int;
EXECUTE sp_xml_preparedocument @h OUTPUT, N'<Data>
<Student id = "s1" name = "Student1" attends = "c1 c3 c6" />
<Student id = "s2" name = "Student2" attends = "c2 c4" />
<Student id = "s3" name = "Student3" attends = "c2 c4 c6" />
<Student id = "s4" name = "Student4" attends = "c1 c3 c5" />
<Student id = "s5" name = "Student5" attends = "c1 c3 c5 c6" />
<Student id = "s6" name = "Student6" />
<Class id = "c1" name = "Intro to Programming"
attendedBy = "s1 s4 s5" />
<Class id = "c2" name = "Databases"
attendedBy = "s2 s3" />
<Class id = "c3" name = "Operating Systems"
attendedBy = "s1 s4 s5" />
<Class id = "c4" name = "Networks" attendedBy = "s2 s3" />
<Class id = "c5" name = "Algorithms and Graphs"
attendedBy = "s4 s5"/>
<Class id = "c6" name = "Power and Pragmatism"
attendedBy = "s1 s3 s5" />
</Data>';
INSERT INTO Students SELECT * FROM OPENXML(@h, '//Student') WITH Students
INSERT INTO Courses SELECT * FROM OPENXML(@h, '//Class') WITH Courses
/* Using the edge table */
EXECUTE fill_idrefs @h, '//Class', 'id', 'attendedby', 'CourseAttendance';
SELECT * FROM Students;
SELECT * FROM Courses;
SELECT * FROM CourseAttendance;
EXECUTE sp_xml_removedocument @h;
K. Mengambil biner dari data yang dikodekan base64 di XML
Data biner sering disertakan dalam XML menggunakan pengodean base64. Saat Anda menghancurkan XML ini dengan menggunakan OPENXML, Anda menerima data yang dikodekan base64. Contoh ini menunjukkan bagaimana Anda dapat mengonversi data yang dikodekan base64 kembali ke biner.
Buat tabel dengan sampel data biner.
Gunakan kueri FOR XML dan opsi BINARY BASE64 untuk membuat XML yang memiliki data biner yang dikodekan sebagai base64.
Menghancurkan XML dengan menggunakan OPENXML. Data yang dikembalikan oleh OPENXML akan menjadi data yang dikodekan base64. Selanjutnya, panggil
.valuefungsi untuk mengonversinya kembali ke biner.
CREATE TABLE T (Col1 int primary key, Col2 varbinary(100));
GO
-- Insert sample binary data
INSERT T VALUES(1, 0x1234567890);
GO
-- Create test XML document that has base64 encoded binary data (use FOR XML query and specify BINARY BASE64 option)
SELECT * FROM T
FOR XML AUTO, BINARY BASE64;
GO
-- result
-- <T Col1="1" Col2="EjRWeJA="/>
-- Now shredd the sample XML using OPENXML.
-- Call the .value function to convert
-- the base64 encoded data returned by OPENXML to binary.
DECLARE @h int;
EXEC sp_xml_preparedocument @h OUTPUT, '<T Col1="1" Col2="EjRWeJA="/>';
SELECT Col1,
CAST('<binary>' + Col2 + '</binary>' AS XML).value('.', 'varbinary(max)') AS BinaryCol
FROM openxml(@h, '/T')
WITH (Col1 integer, Col2 varchar(max)) ;
EXEC sp_xml_removedocument @h;
GO
Ini adalah hasilnya. Data biner yang dikembalikan adalah data biner asli dalam tabel T.
Col1 BinaryCol
----------- ---------------------
1 0x1234567890