Exemplo: recuperando informações de funcionários

Este exemplo recupera uma ID de funcionário e um nome de funcionário para cada funcionário. No banco de dados AdventureWorks2012, o ID do funcionário pode ser obtido na coluna BusinessEntityID na tabela Funcionário. Os nomes de funcionários podem ser obtidos na tabela Pessoa. A coluna BusinessEntityID pode ser usada para unir as tabelas.

Suponha que você queira que a transformação FOR XML EXPLICIT gere XML, conforme mostrado no seguinte:

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

Como há dois níveis na hierarquia, você escreveria duas SELECT consultas e aplicaria UNION ALL. Essa é a primeira consulta que recupera valores para o <Employee> elemento e seus atributos. A consulta atribui 1 como Tag valor para oEmployee<> elemento e NULL comoParent, porque ele é o elemento de nível superior.

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;  

Esta é a segunda consulta. Ele recupera valores para o <Name> elemento. Ele atribui 2 como Tag valor para o <Name> elemento e 1 como Parent valor de marca identificando <Employee> como o pai.

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;  

Você combina essas consultas com UNION ALL, aplica FOR XML EXPLICIT, e especifica a cláusula necessária ORDER BY. Você deve classificar o conjunto de linhas primeiro por BusinessEntityID e depois pelo nome para que os valores NULL no nome apareçam primeiro. Executando a consulta a seguir sem a cláusula FOR XML, você pode ver a tabela universal gerada.

Esta é a consulta final:

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;  

Este é o resultado parcial:

<Employee EmpID="1">

<Name FName="Ken" LName="S??nchez" />

</Employee>

<Employee EmpID="2">

<Name FName="Terri" LName="Duffy" />

</Employee>

...

O primeiro SELECT especifica nomes para colunas no conjunto de linhas resultante. Esses nomes formam dois grupos de colunas. O grupo que tem Tag valor 1 no nome da coluna identifica Employee como um elemento e EmpID como o atributo. O outro grupo de colunas tem Tag valor 2 na coluna e identifica <Name> como o elemento e FName e LName como os atributos.

A tabela a seguir mostra o conjunto de linhas parcial gerado pela consulta:

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

...

É assim que as linhas na tabela universal são processadas para produzir a árvore XML resultante:

A primeira linha identifica o Tag valor 1. Portanto, o grupo de colunas que tem o Tag valor 1 é identificado. Employee!1!EmpID Esta coluna identifica Employee como o nome do elemento. Em seguida, é criado um <Employee> elemento que tem EmpID atributos. Os valores de coluna correspondentes são atribuídos a esses atributos.

A segunda linha tem o Tag valor 2. Portanto, o grupo de colunas que tem o Tag valor 2 no nome da coluna, Name!2!FName, Name!2!LNameé identificado. Esses nomes de coluna identificam Name como o nome do elemento. Um <Name> elemento é criado que tem FName e LName atributos. Os valores de coluna correspondentes são atribuídos a esses atributos. Esta linha identifica 1 como Parent. Um elemento filho é adicionado ao elemento anterior <Employee>.

Esse processo é repetido para o restante das linhas no conjunto de linhas. Observe a importância de ordenar as linhas na tabela universal para que FOR XML EXPLICIT possa processar o conjunto de linhas em ordem e gerar o XML desejado.

Consulte Também

Utilize o modo EXPLICIT com FOR XML