示例:检索员工信息

适用于:SQL Server Azure SQL 数据库 Azure SQL 托管实例

此示例检索每个雇员的雇员 ID 和雇员姓名。 在 AdventureWorks2022 数据库中,可从 Employee 表的 BusinessEntityID 列获得 employeeID。 可从 Person 表中获得雇员姓名。 可使用 BusinessEntityID 列来联接表。

假定要让 FOR XML EXPLICIT 转换生成 XML,如下例所示:

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

因为层次结构中有两个级别,所以应编写两个 SELECT 查询并应用 UNION ALL。 下面是检索 <Employee> 元素及其属性值的第一个查询。 该查询将值 1 赋给 <Employee> 元素的 Tag,将 NULL 赋给 Parent,因为它是一个顶级元素。

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;

下面是第二个查询。 它将检索 <Name> 元素的值。 它将值 2 赋给 <Name> 元素的 Tag,将值 1 赋给 Parent 标记,从而将 <Employee> 标识为父元素。

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 子句。 您必须先按 BusinessEntityID 、再按姓名对行集进行排序,以便先显示姓名中的 NULL 值。 通过执行下面这个不带 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 指定所得到的行集中的列名。 这些名称形成两个列组。 列名中 Tag 值为 1 的组将 Employee 标识为元素,将 EmpID 标识为属性。 另一个列组的列中的 Tag 值为 2,它将 <Name> 标识为元素,将 FNameLName 标识为属性。

下表显示了查询生成的部分行集:

标记 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> 元素。 将为这些属性分配相应的列值。

第二个行具有 Tag2。 因此,标识了列名( Tag2 )中具有 Name!2!FNameName!2!LName的列组。 这些列名将 Name 标识为元素名称。 将创建一个带有 FNameLName 属性的 <Name> 元素。 然后,将为这些属性分配相应的列值。 此行将 1 作为 Parent标识。 此子元素将添加到上一个 <Employee> 元素。

对于行集中的其余行,重复此过程。 请注意,对通用表中的行进行排序很重要,因为这使得 FOR XML EXPLICIT 可以按顺序处理行集并生成所需的 XML。

另请参阅