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

В этом примере извлекается идентификатор сотрудника и имя сотрудника для каждого сотрудника. В базе данных AdventureWorks2012 идентификатор 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 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 как атрибуты.

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

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-дерева:

Первая строка определяет Tag значение 1. Таким образом, определяется группа столбцов с Tag значением 1Employee!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-код.

См. также

Использование режима EXPLICIT вместе с FOR XML