Пример. Получение сведений о сотрудниках
Этот пример извлекает идентификатор работника и имя работника для каждого работника. В базе данных AdventureWork значение employeeID может быть получено из таблицы Employee. Имена работников могут быть получены из таблицы Contact. Столбец ContactID может быть использован для соединения таблиц.
Предположим, что требуется произвести преобразование FOR XML EXPLICIT, чтобы сформировать XML так, как показано далее:
<Employee EmpID="1" >
<Name FName="Guy" LName="Gilbert" />
</Employee>
...
Так как существует два уровня в иерархии, следует написать два запроса SELECT и применить предложение UNION ALL. Это первый запрос, который извлекает значения для элемента <Employee> и его атрибутов. Запрос присваивает полю Tag значение 1 для элемента <Employee>, а полю Parent значение NULL, так как это элемент верхнего уровня.
SELECT 1 as Tag,
NULL as Parent,
EmployeeID as [Employee!1!EmpID],
NULL as [Name!2!FName],
NULL as [Name!2!LName]
FROM HumanResources.Employee E, Person.Contact C
WHERE E.ContactID = C.ContactID
Это второй запрос. Он извлекает значения для элемента <Name>. Столбцу Tag присваивается значение 2 для элемента <Name>, а столбцу Parent — значение 1, в результате чего <Employee> используется в качестве родителя.
SELECT 2 as Tag,
1 as Parent,
EmployeeID,
FirstName,
LastName
FROM HumanResources.Employee E, Person.Contact C
WHERE E.ContactID = C.ContactID
Совместив эти запросы с помощью UNION ALL, примените FOR XML EXPLICIT и укажите необходимое предложение ORDER BY. Сначала нужно отсортировать набор строк по значению EmployeeID, потом по имени, так что значения NULL в именах будут идти первыми. Выполняя следующий запрос без предложения FOR XML, можно увидеть сформированную универсальную таблицу.
Это окончательный запрос:
SELECT 1 as Tag,
NULL as Parent,
EmployeeID as [Employee!1!EmpID],
NULL as [Name!2!FName],
NULL as [Name!2!LName]
FROM HumanResources.Employee E, Person.Contact C
WHERE E.ContactID = C.ContactID
UNION ALL
SELECT 2 as Tag,
1 as Parent,
EmployeeID,
FirstName,
LastName
FROM HumanResources.Employee E, Person.Contact C
WHERE E.ContactID = C.ContactID
ORDER BY [Employee!1!EmpID],[Name!2!FName]
FOR XML EXPLICIT
Промежуточный результат:
<Employee EmpID="1">
<Name FName="Guy" LName="Gilbert" />
</Employee>
<Employee EmpID="2">
<Name FName="Kevin" LName="Brown" />
</Employee>
...
Первая инструкция SELECT указывает имена для столбцов в результирующем наборе строк. Эти имена образуют две группы столбцов. Группа со значением Tag, равным 1, в имени столбца задает Employee в качестве элемента и EmpID в качестве атрибута. Другая группа столбцов со значением Tag, равным 2, в имени столбца задает <Name> в качестве элемента, а FName и LName в качестве атрибутов.
Следующая таблица показывает частичный набор строк, сформированный запросом:
Tag Parent Employee!1!EmpID Name!2!FName Name!2!LName
----------- ----------- ---------------- -------------------
1 NULL 1 NULL NULL
2 1 1 Guy Gilbert
1 NULL 2 NULL NULL
2 1 2 Kevin Brown
1 NULL 3 NULL NULL
2 1 3 Roberto 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.