Bagikan melalui

Contoh: Menggunakan Mode OTOMATIS

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

Contoh: Mengambil informasi pelanggan, pesanan, dan detail pesanan

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

USE AdventureWorks2012;  
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 alias tabel Cust, OrderHeader, Detail, dan Product, elemen terkait dihasilkan oleh mode AUTO. 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: Menentukan fungsi GROUP BY dan agregat

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

USE AdventureWorks2012;  
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;This is the partial result:

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

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

...

Contoh: Menentukan kolom komputasi dalam mode OTOMATIS

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

USE AdventureWorks2012;  
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 elemen <IndividualCustomer> yang memiliki atribut Name dan menyertakan setiap informasi header pesanan penjualan sebagai subelemen, kueri ini ditulis ulang menggunakan subquery. Proses "inner select" membuat tabel sementara IndividualCustomer dengan kolom terhitung yang berisi nama pelanggan individu. Tabel ini kemudian digabungkan ke SalesOrderHeader tabel untuk mendapatkan hasilnya.

Harap dicatat bahwa tabel Sales.Customer menyimpan informasi pelanggan individual, termasuk nilai PersonID untuk pelanggan tersebut. 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.CustomerIDFOR 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. ThumbNailPhoto adalah kolom varbinary(max) dalam tabel ProductPhoto. Secara default, mode AUTO menghasilkan referensi data biner yang merupakan URL relatif terhadap akar virtual dari database tempat kueri dijalankan. Atribut ProductPhotoID kunci harus ditentukan untuk mengidentifikasi gambar. Dalam 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:

-- result

<Production.ProductPhoto

ProductPhotoID="70"

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

Kueri yang sama dilakukan menggunakan opsi BINARY BASE64. 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:

-- result

<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 disebutkan 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 saat kueri dbobject dijalankan terhadap database yang peka huruf besar/kecil. 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 telah 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 atribut <Col#&2> 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 merupakan 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

  • Last updated on