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() dan value(), 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:

  1. Tentukan kelas CLR, SqlReaderBase, yang mengimplementasikan ISqlReader dan menghasilkan output bernilai tabel streaming dengan menerapkan ekspresi jalur pada instans XML.

  2. Buat rakitan dan fungsi yang ditentukan pengguna Transact-SQL untuk memulai kelas CLR.

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