Contoh: Menggunakan mode PATH

Artikel
04/26/2023

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

Contoh berikut mengilustrasikan penggunaan mode PATH dalam menghasilkan XML dari kueri SELECT. Banyak dari kueri ini ditentukan terhadap dokumen XML instruksi manufaktur sepeda yang disimpan di kolom Instruksi tabel ProductModel.

Menentukan kueri mode PATH

Kueri ini menentukan mode UNTUK JALUR XML.

USE AdventureWorks2022;
GO
SELECT
       ProductModelID,
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML PATH;
GO

Hasil berikut adalah XML yang berpusat pada elemen di mana setiap nilai kolom dalam himpunan baris yang dihasilkan dibungkus dalam elemen. SELECT Karena klausa tidak menentukan alias apa pun untuk nama kolom, nama elemen anak yang dihasilkan sama dengan nama kolom yang sesuai dalam SELECT klausa. Untuk setiap baris dalam kumpulan <row> baris, tag ditambahkan.

<row>
  <ProductModelID>122</ProductModelID>
  <Name>All-Purpose Bike Stand</Name>
</row>
<row>
  <ProductModelID>119</ProductModelID>
  <Name>Bike Wash</Name>
</row>

Hasil berikut sama RAW dengan kueri mode dengan ELEMENTS opsi yang ditentukan. Ini mengembalikan XML yang ber sentris elemen dengan elemen default <row> untuk setiap baris dalam tataan hasil.

USE AdventureWorks2022;
GO
SELECT ProductModelID,
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML RAW, ELEMENTS;

Anda dapat secara opsional menentukan nama elemen baris untuk menimpa default <row>. Misalnya, kueri berikut mengembalikan <ProductModel> elemen untuk setiap baris dalam set baris.

USE AdventureWorks2022;
GO
SELECT ProductModelID,
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML PATH ('ProductModel');
GO

XML yang dihasilkan akan memiliki nama elemen baris tertentu.

<ProductModel>
  <ProductModelID>122</ProductModelID>
  <Name>All-Purpose Bike Stand</Name>
</ProductModel>
<ProductModel>
  <ProductModelID>119</ProductModelID>
  <Name>Bike Wash</Name>
</ProductModel>

Jika Anda menentukan string panjang nol, elemen pembungkusan tidak diproduksi.

USE AdventureWorks2022;
GO
SELECT ProductModelID,
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML PATH ('');
GO

Ini adalah hasilnya:

<ProductModelID>122</ProductModelID>
<Name>All-Purpose Bike Stand</Name>
<ProductModelID>119</ProductModelID>
<Name>Bike Wash</Name>

Tentukan nama kolom seperti XPath

Dalam kueri berikut, ProductModelID nama kolom yang ditentukan dimulai dengan '@' dan tidak berisi tanda garis miring ('/'). Oleh karena itu, atribut <row> elemen yang memiliki nilai kolom terkait dibuat dalam XML yang dihasilkan.

USE AdventureWorks2022;
GO
SELECT ProductModelID AS "@id",
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML PATH ('ProductModelData');
GO

Ini adalah hasilnya:

<ProductModelData id="122">
  <Name>All-Purpose Bike Stand</Name>
</ProductModelData>
<ProductModelData id="119">
  <Name>Bike Wash</Name>
</ProductModelData>

Anda dapat menambahkan elemen tingkat atas tunggal dengan menentukan root opsi di FOR XML.

SELECT ProductModelID AS "@id",
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML PATH ('ProductModelData'), root ('Root');
GO

Untuk menghasilkan hierarki, Anda dapat menyertakan sintaks seperti PATH. Misalnya, ubah nama kolom untuk Name kolom menjadi "SomeChild/ModelName" dan Anda akan mendapatkan XML dengan hierarki, seperti yang ditunjukkan dalam hasil ini:

<Root>
  <ProductModelData id="122">
    <SomeChild>
      <ModelName>All-Purpose Bike Stand</ModelName>
    </SomeChild>
  </ProductModelData>
  <ProductModelData id="119">
    <SomeChild>
      <ModelName>Bike Wash</ModelName>
    </SomeChild>
  </ProductModelData>
</Root>

Selain ID dan nama model produk, kueri berikut mengambil lokasi instruksi manufaktur untuk model produk. Karena kolom Instruksi berjenis xml , query() metode tipe data xml ditentukan untuk mengambil lokasi.

SELECT ProductModelID AS "@id",
       Name,
       Instructions.query('declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
                /MI:root/MI:Location
              ') AS ManuInstr
FROM Production.ProductModel
WHERE ProductModelID = 7
FOR XML PATH ('ProductModelData'), root ('Root');
GO

Ini adalah hasil parsial. Karena kueri menentukan ManuInstr sebagai nama kolom, XML yang dikembalikan oleh query() metode dibungkus dalam tag seperti yang <ManuInstr> diperlihatkan dalam hal berikut:

<Root>
  <ProductModelData id="7">
    <Name>HL Touring Frame</Name>
    <ManuInstr>
      <MI:Location xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"
        <MI:step>...</MI:step>...
      </MI:Location>
      ...
    </ManuInstr>
  </ProductModelData>
</Root>

Dalam kueri XML FOR sebelumnya, Anda mungkin ingin menyertakan namespace untuk <Root> elemen dan <ProductModelData> . Anda dapat melakukan ini dengan terlebih dahulu menentukan awalan ke pengikatan namespace dengan menggunakan WITH XMLNAMESPACES dan menggunakan awalan dalam kueri FOR XML. Untuk informasi selengkapnya, lihat Menambahkan Namespace ke Kueri dengan XMLNAMESPACES.

USE AdventureWorks2022;
GO
WITH XMLNAMESPACES (
   'uri1' AS ns1,
   'uri2' AS ns2,
   'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' as MI)
SELECT ProductModelID AS "ns1:ProductModelID",
       Name           AS "ns1:Name",
       Instructions.query('
                /MI:root/MI:Location
              ')
FROM Production.ProductModel
WHERE ProductModelID=7
FOR XML PATH ('ns2:ProductInfo'), root('ns1:root');
GO

MI Awalan juga didefinisikan dalam WITH XMLNAMESPACES. Akibatnya, query() metode jenis xml yang ditentukan tidak menentukan awalan dalam prolog kueri. Ini adalah hasilnya:

<ns1:root xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" xmlns="uri2" xmlns:ns2="uri2" xmlns:ns1="uri1">
  <ns2:ProductInfo>
    <ns1:ProductModelID>7</ns1:ProductModelID>
    <ns1:Name>HL Touring Frame</ns1:Name>
    <MI:Location xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" LaborHours="2.5" LotSize="100" MachineHours="3" SetupHours="0.5" LocationID="10" xmlns="">
    <MI:step>
      Insert <MI:material>aluminum sheet MS-2341</MI:material> into the <MI:tool>T-85A framing tool</MI:tool>.
    </MI:step>
    ...
    </MI:Location>
    ...
  </ns2:ProductInfo>
</ns1:root>

Membuat daftar nilai menggunakan mode PATH

Untuk setiap model produk, kueri ini membuat daftar nilai ID produk. Untuk setiap ID produk, kueri juga membuat <ProductName> elemen berlapis, seperti yang ditunjukkan dalam fragmen XML ini:

<ProductModelData ProductModelID="7" ProductModelName="..." ProductIDs="product id list in the product model">
  <ProductName>...</ProductName>
  <ProductName>...</ProductName>
  ...
</ProductModelData>

Ini adalah kueri yang menghasilkan XML yang Anda inginkan:

USE AdventureWorks2022;
GO
SELECT ProductModelID     AS "@ProductModelID",
       Name               AS "@ProductModelName",
      (SELECT ProductID AS "data()"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
       FOR XML PATH ('')) AS "@ProductIDs",
       (SELECT Name AS "ProductName"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
        FOR XML PATH ('')) AS "ProductNames"
FROM   Production.ProductModel
WHERE  ProductModelID= 7 or ProductModelID=9
FOR XML PATH('ProductModelData');

Perhatikan hal berikut ini dari kueri sebelumnya:

Berlapis SELECT pertama mengembalikan daftar ProductID dengan menggunakan data() sebagai nama kolom. Karena kueri menentukan string kosong sebagai nama elemen baris di FOR XML PATH, tidak ada elemen yang dihasilkan. Sebagai gantinya, daftar nilai ditetapkan ke ProductID atribut .
Berlapis SELECT kedua mengambil nama produk untuk produk dalam model produk. Ini menghasilkan elemen yang dikembalikan yang dibungkus <ProductName> dalam <ProductNames> elemen , karena kueri menentukan ProductNames sebagai nama kolom.

Ini adalah hasil parsial:

<ProductModelData PId="7" ProductModelName="HL Touring Frame" ProductIDs="885 887 ...">
  <ProductNames>
    <ProductName>HL Touring Frame - Yellow, 60</ProductName>
    <ProductName>HL Touring Frame - Yellow, 46</ProductName>
  </ProductNames>
  ...
</ProductModelData>
<ProductModelData PId="9" ProductModelName="LL Road Frame" ProductIDs="722 723 724 ...">
  <ProductNames>
    <ProductName>LL Road Frame - Black, 58</ProductName>
    <ProductName>LL Road Frame - Black, 60</ProductName>
    <ProductName>LL Road Frame - Black, 62</ProductName>
    ...
  </ProductNames>
</ProductModelData>

Subkueri yang membuat nama produk mengembalikan hasil sebagai string yang diberikan dan kemudian ditambahkan ke XML. Jika Anda menambahkan direktif jenis, FOR XML PATH (''), type, subkueri mengembalikan hasil sebagai jenis xml dan tidak ada pemberian entitas yang terjadi.

USE AdventureWorks2022;
GO
SELECT ProductModelID AS "@ProductModelID",
      Name AS "@ProductModelName",
      (SELECT ProductID AS "data()"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
       FOR XML PATH ('')
       ) AS "@ProductIDs",
       (
       SELECT Name AS "ProductName"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
       FOR XML PATH (''), type
       ) AS "ProductNames"

FROM Production.ProductModel
WHERE ProductModelID= 7 OR ProductModelID=9
FOR XML PATH('ProductModelData');

Menambahkan namespace dalam XML yang dihasilkan

Seperti yang dijelaskan dalam Menambahkan Namespace Menggunakan WITH XMLNAMESPACES, Anda dapat menggunakan WITH XMLNAMESPACES untuk menyertakan namespace dalam kueri mode PATH. Misalnya, nama yang ditentukan dalam klausa SELECT menyertakan awalan namespace. Kueri mode berikut PATH membuat XML dengan namespace.

SELECT 'en'    as "English/@xml:lang",
       'food'  as "English",
       'ger'   as "German/@xml:lang",
       'Essen' as "German"
FOR XML PATH ('Translation')
GO

Atribut @xml:lang yang ditambahkan ke <English> elemen ditentukan dalam namespace xml yang telah ditentukan sebelumnya.

Ini adalah hasilnya:

<Translation>
  <English xml:lang="en">food</English>
  <German xml:lang="ger">Essen</German>
</Translation>

Kueri berikut mirip dengan contoh C, kecuali yang digunakannya WITH XMLNAMESPACES untuk menyertakan namespace dalam hasil XML. Untuk informasi selengkapnya, lihat Menambahkan Namespace ke Kueri dengan XMLNAMESPACES.

USE AdventureWorks2022;
GO
WITH XMLNAMESPACES ('uri1' AS ns1,  DEFAULT 'uri2')
SELECT ProductModelID AS "@ns1:ProductModelID",
      Name AS "@ns1:ProductModelName",
      (SELECT ProductID AS "data()"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
       FOR XML PATH ('')
       ) AS "@ns1:ProductIDs",
       (
       SELECT ProductID AS "@ns1:ProductID",
              Name AS "@ns1:ProductName"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
       FOR XML PATH , type
       ) AS "ns1:ProductNames"
FROM Production.ProductModel
WHERE ProductModelID= 7 OR ProductModelID=9
FOR XML PATH('ProductModelData'), root('root');

Ini adalah hasilnya:

<root xmlns="uri2"
  xmlns:ns1="uri1">
  <ProductModelData ns1:ProductModelID="7" ns1:ProductModelName="HL Touring Frame" ns1:ProductIDs="885 887 888 889 890 891 892 893">
    <ns1:ProductNames>
      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="885" ns1:ProductName="HL Touring Frame - Yellow, 60" />
      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="887" ns1:ProductName="HL Touring Frame - Yellow, 46" />
      ...
    </ns1:ProductNames>
  </ProductModelData>
  <ProductModelData ns1:ProductModelID="9" ns1:ProductModelName="LL Road Frame" ns1:ProductIDs="722 723 724 725 726 727 728 729 730 736 737 738">
    <ns1:ProductNames>
      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="722" ns1:ProductName="LL Road Frame - Black, 58" />
      ...
    </ns1:ProductNames>
  </ProductModelData>
</root>

Share via

Contoh: Menggunakan mode PATH

Menentukan kueri mode PATH

Tentukan nama kolom seperti XPath

Membuat daftar nilai menggunakan mode PATH

Menambahkan namespace dalam XML yang dihasilkan

Baca juga

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan: