この例では、各従業員の従業員 ID と従業員名を取得します。 AdventureWorks2012 データベースでは、Employee テーブルの BusinessEntityID 列から employeeID を取得できます。 従業員名は Person テーブルから取得できます。 BusinessEntityID 列を使用してテーブルを結合できます。
次に示すように、FOR XML EXPLICIT 変換で XML を生成するとします。
<Employee EmpID="1" >
<Name FName="Ken" LName="S??nchez" />
</Employee>
...
階層内には 2 つのレベルがあるため、2 つの SELECT クエリを記述し、UNION ALL を適用します。 これは、 <Employee> 要素とその属性の値を取得する最初のクエリです。 クエリでは、<Employee>要素のTag値として1が割り当てられ、最上位要素であるため、Parentとして NULL が割り当てられます。
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;
これは 2 番目のクエリです。
<
Name
>要素の値を取得します。
<
Name
>要素のTag値として2を割り当て、<Employee>を親として識別Parentタグ値として1します。
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;
これらのクエリを UNION ALL と組み合わせ、 FOR XML EXPLICITを適用し、必要な ORDER BY 句を指定します。 名前の NULL 値が最初に表示されるように、最初に行セットを BusinessEntityID 順に並べ替え、次に名前で並べ替える必要があります。 FOR XML 句を指定せずに次のクエリを実行すると、ユニバーサル テーブルが生成されます。
これが最後のクエリです。
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;
結果の一部を次に示します。
<Employee EmpID="1">
<Name FName="Ken" LName="S??nchez" />
</Employee>
<Employee EmpID="2">
<Name FName="Terri" LName="Duffy" />
</Employee>
...
最初の SELECT では、結果の行セット内の列の名前を指定します。 これらの名前は、2 つの列グループを形成します。 列名にTagの値を持つグループは、Employeeを要素とし、EmpIDを属性として識別します。 もう 1 つの列グループには、列に2Tag値があり、<Name>を要素として識別し、属性としてFNameおよびLNameします。
次の表は、クエリによって生成された行セットの一部を示しています。
Tag Parent Employee!1!EmpID Name!2!FName Name!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
...
これにより、ユニバーサル テーブル内の行が処理され、結果の XML ツリーが生成されます。
最初の行は、Tag1値を識別します。 したがって、Tag 値 1 を持つ列グループが識別されますEmployee!1!EmpID。 この列は、 Employee を要素名として識別します。 その後、EmpID属性を持つ<Employee>要素が作成されます。 対応する列の値は、これらの属性に割り当てられます。
2 番目の行には、 Tag 値が 2。 したがって、列名 (Name!2!FName、Name!2!LName) に2Tag値を持つ列グループが識別されます。 これらの列名は、 Name を要素名として識別します。
FName属性とLName属性を持つ<Name>要素が作成されます。 その後、対応する列の値がこれらの属性に割り当てられます。 この行は、Parentとして1を識別します。 この要素の子は、前の <Employee> 要素に追加されます。
このプロセスは、行セット内の残りの行に対して繰り返されます。 FOR XML EXPLICIT で行セットを順番に処理し、必要な XML を生成できるように、ユニバーサル テーブル内の行を並べ替える重要性に注意してください。