Contoh: Menggunakan mode OTOMATIS

Artikel
04/26/2023

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance

Contoh berikut mengilustrasikan penggunaan mode OTOMATIS. Banyak dari kueri ini ditentukan terhadap dokumen XML instruksi manufaktur sepeda yang disimpan di kolom Instruksi tabel ProductModel dalam database sampel AdventureWorks2022.

Contoh: Mengambil informasi detail pelanggan, pesanan, dan pesanan

Kueri ini mengambil informasi detail pelanggan, pesanan, dan pesanan untuk pelanggan tertentu.

USE AdventureWorks2022;
GO
SELECT Cust.CustomerID,
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID,
       Detail.SalesOrderID, Detail.LineTotal, Detail.ProductID,
       Product.Name,
       Detail.OrderQty
FROM Sales.Customer AS Cust
INNER JOIN Sales.SalesOrderHeader AS OrderHeader
    ON Cust.CustomerID = OrderHeader.CustomerID
INNER JOIN Sales.SalesOrderDetail AS Detail
    ON OrderHeader.SalesOrderID = Detail.SalesOrderID
INNER JOIN Production.Product AS Product
    ON Product.ProductID = Detail.ProductID
WHERE Cust.CustomerID IN (29672, 29734)
ORDER BY OrderHeader.CustomerID,
         OrderHeader.SalesOrderID
FOR XML AUTO;

Karena kueri mengidentifikasi, Cust, OrderHeader, Detail, dan Product alias tabel, elemen terkait dihasilkan oleh AUTO mode . Sekali lagi, urutan tabel diidentifikasi oleh kolom yang ditentukan dalam SELECT klausul menentukan hierarki elemen-elemen ini.

Ini adalah hasil parsial.

<Cust CustomerID="29672">
  <OrderHeader CustomerID="29672" SalesOrderID="43660">
    <Detail SalesOrderID="43660" LineTotal="874.794000" ProductID="758" OrderQty="1">
      <Product Name="Road-450 Red, 52" />
    </Detail>
    <Detail SalesOrderID="43660" LineTotal="419.458900" ProductID="762" OrderQty="1">
      <Product Name="Road-650 Red, 44" />
    </Detail>
  </OrderHeader>
  <OrderHeader CustomerID="29672" SalesOrderID="47660">
    <Detail SalesOrderID="47660" LineTotal="469.794000" ProductID="765" OrderQty="1">
      <Product Name="Road-650 Black, 58" />
    </Detail>
  </OrderHeader>
  <OrderHeader CustomerID="29672" SalesOrderID="49857">
    <Detail SalesOrderID="49857" LineTotal="44.994000" ProductID="852" OrderQty="1">
      <Product Name="Women's Tights, S" />
    </Detail>
  </OrderHeader>
...
</Cust>

Contoh: Tentukan FUNGSI GROUP BY dan agregat

Kueri berikut mengembalikan ID pelanggan individual dan jumlah pesanan yang telah diminta pelanggan.

USE AdventureWorks2022;
GO
SELECT C.CustomerID, COUNT(*) AS NoOfOrders
FROM Sales.Customer AS C
INNER JOIN Sales.SalesOrderHeader AS SOH
On C.CustomerID = SOH.CustomerID
GROUP BY C.CustomerID
FOR XML AUTO;

Ini adalah hasil parsial:

<I CustomerID="11000" NoOfOrders="3" />
<I CustomerID="11001" NoOfOrders="3" />
...

Contoh: Tentukan kolom komputasi dalam mode OTOMATIS

Kueri ini mengembalikan nama pelanggan individual yang digabungkan dan informasi pesanan. Karena kolom komputasi ditetapkan ke tingkat paling dalam yang ditemui pada saat itu <SOH> , elemen dalam contoh ini. Nama pelanggan yang digabungkan ditambahkan sebagai atribut <SOH> elemen dalam hasilnya.

USE AdventureWorks2022;
GO
SELECT P.FirstName + ' ' + P.LastName AS Name,
       SOH.SalesOrderID
FROM Sales.Customer AS C
INNER JOIN Sales.SalesOrderHeader AS SOH
    ON  C.CustomerID = SOH.CustomerID
INNER JOIN Person.Person AS P
    ON P.BusinessEntityID = C.PersonID
FOR XML AUTO;

Ini adalah hasil parsial:

<SOH Name="Jon Yang" SalesOrderID="43793" />
<SOH Name="Eugene Huang" SalesOrderID="43767" />

Untuk mengambil <IndividualCustomer> elemen yang memiliki Name atribut yang berisi setiap informasi header pesanan penjualan sebagai subelemen, kueri ditulis ulang menggunakan sub pilih. Pemilihan dalam membuat tabel sementara IndividualCustomer dengan kolom komputasi yang berisi nama pelanggan individual. Tabel ini kemudian digabungkan ke SalesOrderHeader tabel untuk mendapatkan hasilnya.

Tabel menyimpan Sales.Customer informasi pelanggan individual, termasuk nilai untuk pelanggan tersebut PersonID . Ini PersonID kemudian digunakan untuk menemukan nama kontak dari Person.Person tabel.

SELECT IndividualCustomer.Name, SOH.SalesOrderID
FROM (SELECT FirstName+ ' '+LastName AS Name, C.PersonID, C.CustomerID
      FROM Sales.Customer AS C, Person.Person AS P
      WHERE C.PersonID = P.BusinessEntityID) AS IndividualCustomer
LEFT OUTER JOIN  Sales.SalesOrderHeader AS SOH
   ON IndividualCustomer.CustomerID = SOH.CustomerID
ORDER BY IndividualCustomer.CustomerID, SOH.CustomerID FOR XML AUTO;

Ini adalah hasil parsial:

<IndividualCustomer Name="Jon Yang">
  <SOH SalesOrderID="43793" />
  <SOH SalesOrderID="51522" />
  <SOH SalesOrderID="57418" />
</IndividualCustomer>
...

Contoh: Mengembalikan data biner

Kueri ini mengembalikan foto produk dari ProductPhoto tabel. ThumbNailPhotoadalah kolom varbinary(max) dalam ProductPhoto tabel. Secara default, AUTO mode kembali ke data biner referensi yang merupakan URL relatif ke akar virtual database tempat kueri dijalankan. Atribut ProductPhotoID kunci harus ditentukan untuk mengidentifikasi gambar. Saat mengambil referensi gambar seperti yang diilustrasikan dalam contoh ini, kunci utama tabel juga harus ditentukan dalam SELECT klausa untuk mengidentifikasi baris secara unik.

SELECT ProductPhotoID, ThumbNailPhoto
FROM   Production.ProductPhoto
WHERE ProductPhotoID = 70
FOR XML AUTO;

Ini adalah hasilnya:

<Production.ProductPhoto
  ProductPhotoID="70"
  ThumbNailPhoto= "dbobject/Production.ProductPhoto[@ProductPhotoID='70']/@ThumbNailPhoto" />

Kueri yang sama dijalankan dengan BINARY BASE64 opsi . Kueri mengembalikan data biner dalam format yang dikodekan base64.

SELECT ProductPhotoID, ThumbNailPhoto
FROM   Production.ProductPhoto
WHERE ProductPhotoID = 70
FOR XML AUTO, BINARY BASE64;

Ini adalah hasilnya:

<Production.ProductPhoto ProductPhotoID="70" ThumbNailPhoto="Base64 encoded photo" />

Secara default, saat Anda menggunakan mode OTOMATIS untuk mengambil data biner, referensi ke URL relatif ke akar virtual database tempat kueri dijalankan akan dikembalikan alih-alih data biner. Ini akan terjadi jika opsi BINARY BASE64 tidak ditentukan.

Saat mode AUTO mengembalikan referensi URL ke data biner dalam database yang tidak peka huruf besar/kecil di mana nama tabel atau kolom yang ditentukan dalam kueri tidak cocok dengan nama tabel atau kolom dalam database, kueri akan dijalankan. Namun, kasus yang dikembalikan dalam referensi tidak akan konsisten. Contohnya:

SELECT ProductPhotoID, ThumbnailPhoto
FROM   Production.ProductPhoto
WHERE  ProductPhotoID=70
FOR XML AUTO;

Ini adalah hasilnya:

<Production.PRODUCTPHOTO
  PRODUCTPHOTOID="70"
  THUMBNAILPHOTO= "dbobject/Production.PRODUCTPHOTO[@ProductPhotoID='70']/@ThumbNailPhoto" />

Ini bisa menjadi masalah terutama ketika dbobject kueri dijalankan terhadap database sensitif kasus. Untuk menghindari hal ini, kasus nama tabel atau kolom yang ditentukan dalam kueri harus cocok dengan kasus nama tabel atau kolom dalam database.

Contoh: Memahami pengodean

Contoh ini menunjukkan berbagai pengodean yang terjadi dalam hasilnya.

Buat tabel ini:

CREATE TABLE [Special Chars] (Col1 char(1) primary key, [Col#&2] varbinary(50));

Tambahkan data berikut ke tabel:

INSERT INTO [Special Chars] VALUES ('&', 0x20), ('#', 0x20);

Kueri ini mengembalikan data dari tabel. Mode FOR XML AUTO ditentukan. Data biner dikembalikan sebagai referensi.

SELECT * FROM [Special Chars] FOR XML AUTO;

Ini adalah hasilnya:

<Special_x0020_Chars Col1="#"
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='#']/@Col_x0023__x0026_2"
/>
<Special_x0020_Chars Col1="&"
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='&']/@Col_x0023__x0026_2"
/>

Ini adalah proses untuk mengodekan karakter khusus dalam hasilnya:

Dalam hasil kueri, karakter XML dan URL khusus dalam elemen dan nama atribut yang dikembalikan dikodekan dengan menggunakan nilai heksadesimal dari karakter Unicode yang sesuai. Pada hasil sebelumnya, nama <Special Chars> elemen dikembalikan sebagai <Special_x0020_Chars>. Nama <Col#&2> atribut dikembalikan sebagai <Col_x0023__x0026_2>. Karakter khusus XML dan URL dikodekan.
Jika nilai elemen atau atribut berisi salah satu dari lima entitas karakter XML standar (', "", <, >, dan &), karakter XML khusus ini selalu dikodekan menggunakan pengodean karakter XML. Pada hasil sebelumnya, nilai & dalam nilai atribut <Col1> dikodekan sebagai &. Namun, karakter # tetap #, karena ini adalah karakter XML yang valid dan bukan karakter XML khusus.
Jika nilai elemen atau atribut berisi karakter URL khusus yang memiliki arti khusus dalam URL, nilai tersebut hanya dikodekan dalam nilai URL DBOBJECT dan hanya dikodekan ketika karakter khusus adalah bagian dari nama tabel atau kolom. Dalam hasilnya, karakter # yang merupakan bagian dari nama Col#&2 tabel dikodekan sebagai _x0023_ in the DBOJBECT URL.

Lihat juga

Menggunakan Mode OTOMATIS dengan FOR XML

Bagikan melalui

Contoh: Menggunakan mode OTOMATIS

Contoh: Mengambil informasi detail pelanggan, pesanan, dan pesanan

Contoh: Tentukan FUNGSI GROUP BY dan agregat

Contoh: Tentukan kolom komputasi dalam mode OTOMATIS

Contoh: Mengembalikan data biner

Contoh: Memahami pengodean

Lihat juga

Saran dan Komentar

Sumber Daya Tambahan: