Bagikan melalui

Contoh: Mengambil informasi karyawan

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Contoh ini mengambil ID karyawan dan nama karyawan untuk setiap karyawan. AdventureWorks2022 Dalam database, employeeID dapat diperoleh dari kolom BusinessEntityID dalam tabel Karyawan. Nama karyawan dapat diperoleh dari tabel Orang. Kolom BusinessEntityID dapat digunakan untuk menggabungkan tabel.

Asumsikan bahwa Anda ingin transformasi EKSPLISIT XML menghasilkan XML seperti yang ditunjukkan dalam sampel berikut:

<Employee EmpID="1" >
  <Name FName="Ken" LName="Sánchez" />
</Employee>
...

Karena ada dua tingkat dalam hierarki, Anda akan menulis dua SELECT kueri dan menerapkan UNION ALL. Ini adalah kueri pertama yang mengambil nilai untuk <Employee> elemen dan atributnya. Kueri menetapkan 1 sebagai nilai untuk <Employee> elemen dan NULL sebagai Parent, karena merupakan Tag elemen tingkat atas.

SELECT 1    as Tag,
       NULL as Parent,
       E.BusinessEntityID AS [Employee!1!EmpID],
       NULL       as [Name!2!FName],
       NULL       as [Name!2!LName]
FROM   HumanResources.Employee AS E
INNER JOIN Person.Person AS P
ON  E.BusinessEntityID = P.BusinessEntityID;

Ini adalah kueri kedua. Ini mengambil nilai untuk <Name> elemen . Ini menetapkan 2 sebagai nilai untuk <Name> elemen dan 1 sebagai Parent nilai tag yang <Employee> mengidentifikasi Tag sebagai induk.

SELECT 2 as Tag,
       1 as Parent,
       E.BusinessEntityID,
       FirstName,
       LastName
FROM   HumanResources.Employee AS E
INNER JOIN Person.Person AS P
ON  E.BusinessEntityID = P.BusinessEntityID;

Anda menggabungkan kueri ini dengan UNION ALL, menerapkan FOR XML EXPLICIT, dan menentukan klausa yang diperlukan ORDER BY . Anda harus mengurutkan kumpulan baris terlebih dahulu menurut BusinessEntityID lalu berdasarkan nama sehingga nilai NULL dalam nama muncul terlebih dahulu. Dengan menjalankan kueri berikut tanpa klausa FOR XML, Anda dapat melihat tabel universal yang dihasilkan.

Ini adalah kueri akhir:

SELECT 1    as Tag,
       NULL as Parent,
       E.BusinessEntityID as [Employee!1!EmpID],
       NULL       as [Name!2!FName],
       NULL       as [Name!2!LName]
FROM   HumanResources.Employee AS E
INNER JOIN Person.Person AS P
ON  E.BusinessEntityID = P.BusinessEntityID
UNION ALL
SELECT 2 as Tag,
       1 as Parent,
       E.BusinessEntityID,
       FirstName,
       LastName
FROM   HumanResources.Employee AS E
INNER JOIN Person.Person AS P
ON  E.BusinessEntityID = P.BusinessEntityID
ORDER BY [Employee!1!EmpID],[Name!2!FName]
FOR XML EXPLICIT;

Ini adalah hasil parsial:

<Employee EmpID="1">
  <Name FName="Ken" LName="Sánchez" />
</Employee>
<Employee EmpID="2">
  <Name FName="Terri" LName="Duffy" />
</Employee>
...

Yang pertama SELECT menentukan nama untuk kolom dalam himpunan baris yang dihasilkan. Nama-nama ini membentuk dua grup kolom. Grup yang memiliki Tag nilai 1 dalam nama kolom mengidentifikasi Employee sebagai elemen dan EmpID sebagai atribut . Grup kolom lainnya memiliki Tag nilai 2 dalam kolom dan mengidentifikasi <Name> sebagai elemen dan FName dan LName sebagai atribut.

Tabel berikut ini memperlihatkan set baris parsial yang dihasilkan oleh kueri:

Tag Parent Karyawan!1! EmpID Nama!2! FName Nama!2! LName
1 NULL 1 NULL NULL
2 1 1 Ken Sánchez
1 NULL 2 NULL NULL
2 1 2 Terri Duffy
1 NULL 3 NULL NULL
2 1 3 Roberto Tamburello
... ... ... ... ...

Ini adalah bagaimana baris dalam tabel universal diproses untuk menghasilkan pohon XML yang dihasilkan:

Baris pertama mengidentifikasi Tag nilai 1. Oleh karena itu, grup kolom yang memiliki Tag nilai 1 diidentifikasi, Employee!1!EmpID. Kolom ini mengidentifikasi Employee sebagai nama elemen. Elemen <Employee> kemudian dibuat yang memiliki EmpID atribut. Nilai kolom terkait ditetapkan ke atribut ini.

Baris kedua memiliki Tag nilai 2. Oleh karena itu, grup kolom yang memiliki Tag nilai 2 dalam nama kolom, Name!2!FName, Name!2!LName, diidentifikasi. Nama kolom ini mengidentifikasi Name sebagai nama elemen. Elemen <Name> dibuat yang memiliki FName atribut dan LName . Nilai kolom yang sesuai kemudian ditetapkan ke atribut ini. Baris ini mengidentifikasi 1 sebagai Parent. Turunan elemen ini ditambahkan ke elemen sebelumnya <Employee> .

Proses ini diulang untuk sisa baris dalam set baris. Perhatikan pentingnya mengurutkan baris dalam tabel universal sehingga FOR XML EXPLICIT dapat memproses set baris secara berurutan dan menghasilkan XML yang Anda inginkan.

Baca juga