Поделиться через

Пример. Получение сведений о сотрудниках

  • Статья

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

В этом примере извлекаются идентификаторы и имена всех работников. В базе данных AdventureWorks2022 значение employeeID может быть получено из столбца BusinessEntityID таблицы Employee. Имена работников могут быть получены из таблицы Person. Столбец BusinessEntityID можно использовать для соединения этих таблиц.

Предположим, что требуется преобразование FOR XML EXPLICIT для создания XML, как показано в следующем примере:

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

Так как существует два уровня в иерархии, следует написать два запроса SELECT и применить предложение UNION ALL. Это первый запрос, который получает значения для <Employee> элемента и его атрибутов. Запрос назначается 1 в качестве Tag значения для <Employee> элемента и 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 в качестве Tag значения для <Name> элемента и 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 AL, примените директиву 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> как элемент и FName LName как атрибуты.

Следующая таблица показывает частичный набор строк, сформированный запросом:

Тег 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 Юрий Tamburello
... ... ... ... ...

Вот как строки универсальной таблицы обрабатываются для создания результирующего дерева XML:

Первая строка задает для атрибута Tag значение 1. В результате получается группа столбцов, у которых имеется значение Tag , равное 1 , Employee!1!EmpID. Этот столбец задает в качестве имени элемент Employee . Затем <Employee> создается элемент с EmpID атрибутами. Соответствующие значения столбца присваиваются этим атрибутам.

Вторая строка имеет атрибут Tag со значением 2. В результате получается группа столбцов, у которых атрибут Tag имеет значение 2 в имени столбца, Name!2!FName, Name!2!LName. Эти имена столбцов задают в качестве имени элемента Name . Создается <Name> элемент, имеющий FName и LName атрибуты. После этого соответствующие значения столбца присваиваются этим атрибутам. Эта строка задает 1 в качестве Parent. Этот дочерний элемент добавляется в предыдущий <Employee> элемент.

Процесс повторяется для оставшихся строк набора строк. Обратите внимание на важность порядка строк в универсальной таблице, чтобы FOR XML EXPLICIT, обработав набор строк по порядку, мог создать желаемый XML.

См. также