Bagikan melalui

Membuat saudara kandung dengan kueri mode OTOMATIS berlapis

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Contoh berikut menunjukkan cara menghasilkan saudara kandung dengan menggunakan kueri mode OTOMATIS berlapis. Satu-satunya cara lain untuk menghasilkan XML tersebut adalah dengan menggunakan mode EKSPLISIT. Namun, ini bisa rumit.

Contoh

Kueri ini membangun XML yang menyediakan informasi pesanan penjualan. Ini termasuk yang berikut:

  • Informasi header pesanan penjualan, SalesOrderID, SalesPersonID, dan OrderDate. AdventureWorks2022 menyimpan informasi ini dalam SalesOrderHeader tabel.

  • Informasi detail pesanan penjualan. Ini termasuk satu atau beberapa produk yang dipesan, harga satuan, dan kuantitas yang dipesan. Informasi ini disimpan dalam SalesOrderDetail tabel.

  • Informasi sales person. Ini adalah salesperson yang mengambil pesanan. Tabel SalesPerson menyediakan SalesPersonID. Untuk kueri ini, Anda harus menggabungkan tabel ini ke Employee tabel untuk menemukan nama orang penjualan.

Dua kueri berbeda SELECT yang mengikuti menghasilkan XML dengan perbedaan kecil dalam bentuk.

Kueri pertama menghasilkan XML di mana <SalesPerson> dan <SalesOrderHeader> muncul sebagai anak saudara dari <SalesOrder>:

SELECT
      (SELECT top 2 SalesOrderID, SalesPersonID, CustomerID,
         (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
           from Sales.SalesOrderDetail
            WHERE  SalesOrderDetail.SalesOrderID =
                   SalesOrderHeader.SalesOrderID
            FOR XML AUTO, TYPE)
        FROM  Sales.SalesOrderHeader
        WHERE SalesOrderHeader.SalesOrderID = SalesOrder.SalesOrderID
        for xml auto, type),
        (SELECT *
         FROM  (SELECT SalesPersonID, EmployeeID
              FROM Sales.SalesPerson, HumanResources.Employee
              WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As
                     SalesPerson
         WHERE  SalesPerson.SalesPersonID = SalesOrder.SalesPersonID
       FOR XML AUTO, TYPE)
FROM (SELECT SalesOrderHeader.SalesOrderID, SalesOrderHeader.SalesPersonID
      FROM Sales.SalesOrderHeader, Sales.SalesPerson
      WHERE SalesOrderHeader.SalesPersonID = SalesPerson.SalesPersonID
     ) as SalesOrder
ORDER BY SalesOrder.SalesOrderID
FOR XML AUTO, TYPE;

Di kueri sebelumnya, pernyataan terluar SELECT melakukan hal berikut:

  • Mengkueri set baris, SalesOrder, yang ditentukan dalam FROM klausa. Hasilnya adalah XML dengan satu atau beberapa <SalesOrder> elemen.

  • AUTO Menentukan mode dan arahanTYPE. AUTO mode mengubah hasil kueri menjadi XML, dan direktif TYPE mengembalikan hasil sebagai jenis xml .

  • Termasuk dua pernyataan berlapis yang dipisahkan SELECT oleh koma. Berlapis SELECT pertama mengambil informasi pesanan penjualan, header dan detail, dan pernyataan berlapis SELECT kedua mengambil informasi tenaga penjual.

    • Pernyataan SELECT yang mengambil SalesOrderID, SalesPersonID, dan CustomerID itu sendiri mencakup pernyataan berlapis SELECT ... FOR XML lain (dengan AUTO mode dan TYPE arahan) yang mengembalikan informasi detail pesanan penjualan.

Pernyataan SELECT yang mengambil informasi orang penjualan mengkueri set baris, SalesPerson, yang dibuat dalam FROM klausa. Agar FOR XML kueri berfungsi, Anda harus memberikan nama untuk set baris anonim yang dihasilkan dalam FROM klausa. Dalam hal ini, nama yang disediakan adalah SalesPerson.

Ini adalah hasil parsial:

<SalesOrder>
  <Sales.SalesOrderHeader SalesOrderID="43659" SalesPersonID="279" CustomerID="676">
    <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="776" OrderQty="1" UnitPrice="2024.9940" />
    <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="777" OrderQty="3" UnitPrice="2024.9940" />
    <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="778" OrderQty="1" UnitPrice="2024.9940" />
  </Sales.SalesOrderHeader>
  <SalesPerson SalesPersonID="279" EmployeeID="279" />
</SalesOrder>
...

Kueri berikut menghasilkan informasi pesanan penjualan yang sama, kecuali bahwa dalam XML yang dihasilkan, <SalesPerson> muncul sebagai saudara kandung dari <SalesOrderDetail>:

<SalesOrder>
    <SalesOrderHeader ...>
          <SalesOrderDetail .../>
          <SalesOrderDetail .../>
          ...
          <SalesPerson .../>
    </SalesOrderHeader>

</SalesOrder>
<SalesOrder>
  ...
</SalesOrder>

Ini adalah kueri:

SELECT SalesOrderID, SalesPersonID, CustomerID,
             (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
              from Sales.SalesOrderDetail
              WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
              FOR XML AUTO, TYPE),
              (SELECT *
               FROM  (SELECT SalesPersonID, EmployeeID
                    FROM Sales.SalesPerson, HumanResources.Employee
                    WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As SalesPerson
               WHERE  SalesPerson.SalesPersonID = SalesOrderHeader.SalesPersonID
         FOR XML AUTO, TYPE)
FROM Sales.SalesOrderHeader
WHERE SalesOrderID=43659 or SalesOrderID=43660
FOR XML AUTO, TYPE;

Ini adalah hasilnya:

<Sales.SalesOrderHeader SalesOrderID="43659" SalesPersonID="279" CustomerID="676">
  <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="776" OrderQty="1" UnitPrice="2024.9940" />
  <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="777" OrderQty="3" UnitPrice="2024.9940" />
  <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="778" OrderQty="1" UnitPrice="2024.9940" />
  <SalesPerson SalesPersonID="279" EmployeeID="279" />
</Sales.SalesOrderHeader>
<Sales.SalesOrderHeader SalesOrderID="43660" SalesPersonID="279" CustomerID="117">
  <Sales.SalesOrderDetail SalesOrderID="43660" ProductID="762" OrderQty="1" UnitPrice="419.4589" />
  <Sales.SalesOrderDetail SalesOrderID="43660" ProductID="758" OrderQty="1" UnitPrice="874.7940" />
  <SalesPerson SalesPersonID="279" EmployeeID="279" />
</Sales.SalesOrderHeader>

Karena direktif TYPE mengembalikan hasil kueri sebagai jenis xml , Anda bisa mengkueri XML yang dihasilkan dengan menggunakan berbagai metode tipe data xml . Untuk informasi selengkapnya, lihat Metode Tipe Data xml. Dalam kueri berikut, perhatikan hal berikut:

  • Kueri sebelumnya ditambahkan dalam FROM klausa. Hasil kueri dikembalikan sebagai tabel. XmlCol Perhatikan alias yang ditambahkan.

  • Klausa SELECT menentukan XQuery terhadap yang XmlCol dikembalikan dalam FROM klausa. Metode query() tipe data xml digunakan dalam menentukan XQuery. Untuk informasi selengkapnya, lihat Metode query() (Tipe Data xml).

    SELECT XmlCol.query('<Root> { /* } </Root>')
FROM (
SELECT SalesOrderID, SalesPersonID, CustomerID,
             (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
              from Sales.SalesOrderDetail
              WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
              FOR XML AUTO, TYPE),
              (SELECT *
               FROM  (SELECT SalesPersonID, EmployeeID
                    FROM Sales.SalesPerson, HumanResources.Employee
                    WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As SalesPerson
               WHERE  SalesPerson.SalesPersonID = SalesOrderHeader.SalesPersonID
         FOR XML AUTO, TYPE)
FROM Sales.SalesOrderHeader
WHERE SalesOrderID='43659' or SalesOrderID='43660'
FOR XML AUTO, TYPE ) as T(XmlCol);

Baca juga