Mempromosikan nilai XML yang sering digunakan dengan kolom komputasi
Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
Jika kueri dibuat terutama pada beberapa elemen dan nilai atribut, Anda mungkin ingin mempromosikan jumlah tersebut ke dalam kolom relasional. Ini berguna ketika kueri dikeluarkan pada sebagian kecil data XML saat seluruh instans XML diambil. Membuat indeks XML pada kolom XML tidak diperlukan. Sebagai gantinya, kolom yang dipromosikan dapat diindeks. Kueri harus ditulis untuk menggunakan kolom yang dipromosikan. Artinya, pengoptimal kueri tidak menargetkan lagi kueri pada kolom XML ke kolom yang dipromosikan.
Kolom yang dipromosikan bisa menjadi kolom komputasi dalam tabel yang sama atau dapat berupa kolom terpisah yang dikelola pengguna dalam tabel. Ini cukup ketika nilai singleton dipromosikan dari setiap instans XML. Namun, untuk properti multinilai, Anda harus membuat tabel terpisah untuk properti , seperti yang dijelaskan di bagian berikut.
Kolom komputasi berdasarkan jenis data xml
Kolom komputasi dapat dibuat dengan menggunakan fungsi yang ditentukan pengguna yang memanggil metode jenis data xml . Jenis kolom komputasi dapat berupa jenis SQL apa pun, termasuk XML. Ini diilustrasikan dalam contoh berikut.
Contoh: Kolom komputasi berdasarkan metode tipe data xml
Buat fungsi yang ditentukan pengguna untuk nomor ISBN buku:
CREATE FUNCTION udf_get_book_ISBN (@xData xml)
RETURNS varchar(20)
BEGIN
DECLARE @ISBN varchar(20)
SELECT @ISBN = @xData.value('/book[1]/@ISBN', 'varchar(20)')
RETURN @ISBN
END;
Tambahkan kolom komputasi ke tabel untuk ISBN:
ALTER TABLE T
ADD ISBN AS dbo.udf_get_book_ISBN(xCol);
Kolom komputasi dapat diindeks dengan cara yang biasa.
Contoh: Kueri pada kolom komputasi berdasarkan metode tipe data xml
Untuk mendapatkan <book>
ISBN-nya adalah 0-7356-1588-2:
SELECT xCol
FROM T
WHERE xCol.exist('/book/@ISBN[. = "0-7356-1588-2"]') = 1;
Kueri pada kolom XML dapat ditulis ulang untuk menggunakan kolom komputasi sebagai berikut:
SELECT xCol
FROM T
WHERE ISBN = '0-7356-1588-2';
Anda dapat membuat fungsi yang ditentukan pengguna untuk mengembalikan jenis data xml dan kolom komputasi dengan menggunakan fungsi yang ditentukan pengguna. Namun, Anda tidak dapat membuat indeks XML pada kolom XML komputasi.
Membuat tabel properti
Anda mungkin ingin mempromosikan beberapa properti multinilai dari data XML Anda ke dalam satu atau beberapa tabel, membuat indeks pada tabel tersebut, dan menargetkan lagi kueri Anda untuk menggunakannya. Skenario umum adalah salah satu di mana beberapa properti mencakup sebagian besar beban kerja kueri Anda. Anda dapat melakukan tindakan berikut:
Buat satu atau beberapa tabel untuk menyimpan properti multinila. Anda mungkin merasa nyaman untuk menyimpan satu properti per tabel dan menduplikasi kunci utama tabel dasar dalam tabel properti untuk bergabung kembali dengan tabel dasar.
Jika Anda ingin mempertahankan urutan relatif properti, Anda harus memperkenalkan kolom terpisah untuk urutan relatif.
Buat pemicu pada kolom XML untuk mempertahankan tabel properti. Dalam pemicu, lakukan salah satu hal berikut:
Gunakan metode tipe data xml , seperti
nodes()
danvalue()
, untuk menyisipkan dan menghapus baris tabel properti.Buat fungsi bernilai tabel streaming dalam runtime bahasa umum (CLR) untuk menyisipkan dan menghapus baris tabel properti.
Tulis kueri untuk akses SQL ke tabel properti dan untuk akses XML ke kolom XML di tabel dasar, dengan gabungan di antara tabel dengan menggunakan kunci utamanya.
Contoh: Membuat tabel properti
Untuk ilustrasi, asumsikan bahwa Anda ingin mempromosikan nama depan penulis. Buku memiliki satu atau beberapa penulis, sehingga nama depan adalah properti multinilai. Setiap nama depan disimpan dalam baris terpisah dari tabel properti. Kunci utama tabel dasar diduplikasi dalam tabel properti untuk gabungan kembali.
create table tblPropAuthor (propPK int, propAuthor varchar(max));
Contoh: Membuat fungsi yang ditentukan pengguna untuk menghasilkan set baris dari instans XML
Fungsi bernilai tabel berikut, udf_XML2Table, menerima nilai kunci utama dan instans XML. Ini mengambil nama depan semua penulis <book>
elemen dan mengembalikan kumpulan baris kunci primer, pasangan nama depan.
create function udf_XML2Table (@pk int, @xCol xml)
returns @ret_Table table (propPK int, propAuthor varchar(max))
with schemabinding
as
begin
insert into @ret_Table
select @pk, nref.value('.', 'varchar(max)')
from @xCol.nodes('/book/author/first-name') R(nref)
return
end;
Contoh: Membuat pemicu untuk mengisi tabel properti
Pemicu sisipkan menyisipkan baris ke dalam tabel properti:
create trigger trg_docs_INS on T for insert
as
declare @wantedXML xml
declare @FK int
select @wantedXML = xCol from inserted
select @FK = PK from inserted
insert into tblPropAuthor
select * from dbo.udf_XML2Table(@FK, @wantedXML);
Pemicu penghapusan menghapus baris dari tabel properti berdasarkan nilai kunci utama baris yang dihapus:
create trigger trg_docs_DEL on T for delete
as
declare @FK int
select @FK = PK from deleted
delete tblPropAuthor where propPK = @FK;
Pemicu pembaruan menghapus baris yang ada dalam tabel properti yang sesuai dengan instans XML yang diperbarui dan menyisipkan baris baru ke dalam tabel properti:
create trigger trg_docs_UPD
on T
for update
as
if update(xCol) or update(pk)
begin
declare @FK int
declare @wantedXML xml
select @FK = PK from deleted
delete tblPropAuthor where propPK = @FK
select @wantedXML = xCol from inserted
select @FK = pk from inserted
insert into tblPropAuthor
select * from dbo.udf_XML2Table(@FK, @wantedXML)
end;
Contoh: Temukan instans XML yang penulisnya memiliki nama depan yang sama
Kueri dapat dibentuk pada kolom XML. Atau, ini dapat mencari tabel properti untuk nama depan "David" dan melakukan gabungan kembali dengan tabel dasar untuk mengembalikan instans XML. Contohnya:
SELECT xCol
FROM T JOIN tblPropAuthor ON T.pk = tblPropAuthor.propPK
WHERE tblPropAuthor.propAuthor = 'David';
Contoh: Solusi menggunakan fungsi bernilai tabel streaming CLR
Solusi ini terdiri dari langkah-langkah berikut:
Tentukan kelas CLR, SqlReaderBase, yang mengimplementasikan ISqlReader dan menghasilkan output bernilai tabel streaming dengan menerapkan ekspresi jalur pada instans XML.
Buat rakitan dan fungsi yang ditentukan pengguna Transact-SQL untuk memulai kelas CLR.
Tentukan pemicu sisipkan, perbarui, dan hapus dengan menggunakan fungsi yang ditentukan pengguna untuk mempertahankan tabel properti.
Untuk melakukan ini, Anda terlebih dahulu membuat fungsi CLR streaming. Jenis data xml diekspos sebagai SqlXml kelas terkelola di ADO.NET dan mendukung metode CreateReader() yang mengembalikan XmlReader.
Catatan
Contoh kode di bagian ini menggunakan XPathDocument dan XPathNavigator. Ini memaksa Anda untuk memuat semua dokumen XML ke dalam memori. Jika Anda menggunakan kode serupa dalam aplikasi Anda untuk memproses beberapa dokumen XML besar, kode ini tidak dapat diskalakan. Sebagai gantinya, jaga alokasi memori tetap kecil dan gunakan antarmuka streaming jika memungkinkan. Untuk informasi selengkapnya tentang performa, lihat Arsitektur Integrasi CLR.
public class c_streaming_xml_tvf {
public static ISqlReader streaming_xml_tvf
(SqlXml xmlDoc, string pathExpression) {
return (new TestSqlReaderBase (xmlDoc, pathExpression));
}
}
// Class that implements ISqlReader
public class TestSqlReaderBase : ISqlReader {
XPathNodeIterator m_iterator;
public SqlChars FirstName;
// Metadata for current resultset
private SqlMetaData[] m_rgSqlMetaData;
public TestSqlReaderBase (SqlXml xmlDoc, string pathExpression) {
// Variables for XPath navigation
XPathDocument xDoc;
XPathNavigator xNav;
XPathExpression xPath;
// Set sql metadata
m_rgSqlMetaData = new SqlMetaData[1];
m_rgSqlMetaData[0] = new SqlMetaData ("FirstName",
SqlDbType.NVarChar, 50);
//Set up the Navigator
if (!xmlDoc.IsNull)
xDoc = new XPathDocument (xmlDoc.CreateReader());
else
xDoc = new XPathDocument ();
xNav = xDoc.CreateNavigator();
xPath = xNav.Compile (pathExpression);
m_iterator = xNav.Select(xPath);
}
public bool Read() {
bool moreRows = true;
if (moreRows = m_iterator.MoveNext())
FirstName = new SqlChars (m_iterator.Current.Value);
return moreRows;
}
}
Selanjutnya, buat rakitan dan fungsi yang ditentukan pengguna Transact-SQL, SQL_streaming_xml_tvf (tidak ditampilkan), yang sesuai dengan fungsi CLR, streaming_xml_tvf. Fungsi yang ditentukan pengguna digunakan untuk menentukan fungsi bernilai tabel, CLR_udf_XML2Table, untuk pembuatan set baris:
create function CLR_udf_XML2Table (@pk int, @xCol xml)
returns @ret_Table table (FK int, FirstName varchar(max))
with schemabinding
as
begin
insert into @ret_Table
select @pk, FirstName
FROM SQL_streaming_xml_tvf (@xCol, '/book/author/first-name')
return
end;
Terakhir, tentukan pemicu seperti yang ditunjukkan dalam contoh, "Buat pemicu untuk mengisi tabel properti", tetapi ganti udf_XML2Table dengan fungsi CLR_udf_XML2Table. Pemicu sisipan diperlihatkan dalam contoh berikut:
create trigger CLR_trg_docs_INS on T for insert
as
declare @wantedXML xml
declare @FK int
select @wantedXML = xCol from inserted
select @FK = PK from inserted
insert into tblPropAuthor
select *
from dbo.CLR_udf_XML2Table(@FK, @wantedXML);
Pemicu penghapusan identik dengan versi non-CLR. Namun, pemicu pembaruan hanya mengganti fungsi udf_XML2Table() dengan CLR_udf_XML2Table().
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