Menggunakan mode OTOMATIS dengan FOR XML

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Seperti yang dijelaskan dalam FOR XML (SQL Server), mode AUTO mengembalikan hasil kueri sebagai elemen XML berlapis. Ini tidak memberikan banyak kontrol atas bentuk XML yang dihasilkan dari hasil kueri. Kueri mode OTOMATIS berguna jika Anda ingin menghasilkan hierarki sederhana. Namun, Gunakan Mode EKSPLISIT dengan FOR XML dan Gunakan Mode PATH dengan FOR XML memberikan lebih banyak kontrol dan fleksibilitas dalam memutuskan bentuk XML dari hasil kueri.

Setiap tabel dalam klausa FROM, di mana setidaknya satu kolom tercantum dalam klausa SELECT, dinyatakan sebagai elemen XML. Kolom yang tercantum dalam klausa SELECT dipetakan ke atribut atau subelemen, jika opsi ELEMEN OPSIONAL ditentukan dalam klausa FOR XML.

Hierarki XML, penumpukan elemen, dalam XML yang dihasilkan didasarkan pada urutan tabel yang diidentifikasi oleh kolom yang ditentukan dalam klausul SELECT. Oleh karena itu, urutan di mana nama kolom ditentukan dalam klausa SELECT signifikan. Tabel pertama paling kiri yang diidentifikasi membentuk elemen teratas dalam dokumen XML yang dihasilkan. Tabel paling kiri kedua, diidentifikasi oleh kolom dalam pernyataan SELECT, membentuk subelemen dalam elemen atas, dan sebagainya.

Jika nama kolom yang tercantum dalam klausa SELECT berasal dari tabel yang sudah diidentifikasi oleh kolom yang ditentukan sebelumnya dalam klausa SELECT, kolom ditambahkan sebagai atribut elemen yang sudah dibuat, alih-alih membuka tingkat hierarki baru. Jika opsi ELEMENTS ditentukan, kolom ditambahkan sebagai atribut.

Misalnya, jalankan kueri ini:

SELECT Cust.CustomerID,
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID,
       OrderHeader.Status,
       Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO;

Ini adalah hasil parsial:

<Cust CustomerID="1" CustomerType="S">
  <OrderHeader CustomerID="1" SalesOrderID="43860" Status="5" />
  <OrderHeader CustomerID="1" SalesOrderID="44501" Status="5" />
  <OrderHeader CustomerID="1" SalesOrderID="45283" Status="5" />
  <OrderHeader CustomerID="1" SalesOrderID="46042" Status="5" />
</Cust>
...

Perhatikan hal berikut dalam klausa SELECT:

  • CustomerID mereferensikan tabel Cust. Oleh karena itu, <Cust> elemen dibuat dan CustomerID ditambahkan sebagai atributnya.

  • Selanjutnya, tiga kolom, OrderHeader.CustomerID, OrderHeader.SaleOrderID, dan OrderHeader.Status, referensikan tabel OrderHeader. Oleh karena itu, <OrderHeader> elemen ditambahkan sebagai subelemen elemen <Cust> dan tiga kolom ditambahkan sebagai atribut .<OrderHeader>

  • Selanjutnya, kolom Cust.CustomerType kembali mereferensikan tabel Cust yang sudah diidentifikasi oleh kolom Cust.CustomerID. Oleh karena itu, tidak ada elemen baru yang dibuat. Sebagai gantinya, atribut CustomerType ditambahkan ke <Cust> elemen yang sebelumnya dibuat.

  • Kueri menentukan alias untuk nama tabel. Alias ini muncul sebagai nama elemen yang sesuai.

  • ORDER BY diperlukan untuk mengelompokkan semua anak di bawah satu induk.

Kueri ini mirip dengan yang sebelumnya, kecuali klausa SELECT menentukan kolom dalam tabel OrderHeader sebelum kolom dalam tabel Cust. Oleh karena itu, elemen pertama <OrderHeader> dibuat dan kemudian elemen turunan <Cust> ditambahkan ke dalamnya.

select OrderHeader.CustomerID,
       OrderHeader.SalesOrderID,
       OrderHeader.Status,
       Cust.CustomerID,
       Cust.CustomerType
from Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
where Cust.CustomerID = OrderHeader.CustomerID
for xml auto;

Ini adalah hasil parsial:

<OrderHeader CustomerID="1" SalesOrderID="43860" Status="5">
  <Cust CustomerID="1" CustomerType="S" />
</OrderHeader>
...

Jika opsi ELEMENTS ditambahkan dalam klausa FOR XML, XML yang berpusat pada elemen dikembalikan.

SELECT Cust.CustomerID,
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID,
       OrderHeader.Status,
       Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO, ELEMENTS

Ini adalah hasil parsial:

<Cust>
  <CustomerID>1</CustomerID>
  <CustomerType>S</CustomerType>
  <OrderHeader>
    <CustomerID>1</CustomerID>
    <SalesOrderID>43860</SalesOrderID>
    <Status>5</Status>
  </OrderHeader>
   ...
</Cust>
...

Dalam kueri ini, nilai CustomerID dibandingkan dari satu baris ke baris berikutnya dalam membuat <elemen Cust> , karena CustomerID adalah kunci utama tabel. Jika CustomerID tidak diidentifikasi sebagai kunci utama untuk tabel, semua nilai kolom (CustomerID, CustomerType dalam kueri ini) dibandingkan dari satu baris ke baris berikutnya. Jika nilainya berbeda, elemen Cust> baru <ditambahkan ke XML.

Saat membandingkan nilai kolom ini, jika salah satu kolom yang akan dibandingkan adalah jenis teks, ntext, gambar, atau xml, FOR XML mengasumsikan bahwa nilainya berbeda dan tidak dibandingkan, meskipun mungkin sama. Ini karena membandingkan objek besar tidak didukung. Elemen ditambahkan ke hasil untuk setiap baris yang dipilih. Kolom (n)varchar(maks) dan varbinary(maks) dibandingkan.

Saat kolom dalam klausul SELECT tidak dapat dikaitkan dengan salah satu tabel yang diidentifikasi dalam klausul FROM, seperti dalam kasus kolom agregat atau kolom komputasi, kolom ditambahkan dalam dokumen XML di tingkat berlapis terdalam di tempat ketika ditemukan dalam daftar. Jika kolom seperti itu muncul sebagai kolom pertama dalam klausa SELECT, kolom ditambahkan ke elemen atas.

Jika karakter kartubebas tanda bintang (*) ditentukan dalam klausa SELECT, penumpukan ditentukan dengan cara yang sama seperti yang dijelaskan sebelumnya, berdasarkan urutan bahwa baris dikembalikan oleh mesin kueri.

Langkah berikutnya

Artikel berikut ini menyediakan informasi selengkapnya tentang mode OTOMATIS:

Baca juga