Exemplo: Recuperando informações de funcionários
Este exemplo recupera a ID e o nome de cada funcionário. No banco de dados AdventureWorks, a employeeID pode ser obtida na tabela Employee. Nomes de funcionários podem ser obtidos da tabela Contact. A coluna ContactID pode ser usada para unir as tabelas.
Assuma que você quer transformação de FOR XML EXPLICIT para gerar XML, conforme mostrado no seguinte:
<Employee EmpID="1" >
<Name FName="Guy" LName="Gilbert" />
</Employee>
...
Como há dois níveis na hierarquia, você escreve duas consultas SELECT e aplica UNION ALL. Esta é a primeira consulta que recupera valores do elemento <Employee> e seus atributos. A consulta atribui 1 como o valor de Tag para o elemento <Employee> e NULL como Parent, porque ele é o elemento de nível superior.
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
Esta é a segunda consulta. Ela recupera valores do elemento <Name>. Ela atribui 2 como o valor da Tag do elemento <Name> e 1 como o valor da marca Parent que identifica <Employee> como o pai.
SELECT 2 as Tag,
1 as Parent,
EmployeeID,
FirstName,
LastName
FROM HumanResources.Employee E, Person.Contact C
WHERE E.ContactID = C.ContactID
Você combina essas consultas com UNION ALL, aplica FOR XML EXPLICIT e especifica a cláusula ORDER BY necessária. Você deve classificar o conjunto de linhas primeiro por EmployeeID e, em seguida, pelo nome para que os valores NULL no nome sejam exibidos primeiro. Executando a consulta a seguir sem a cláusula FOR XML, é possível ver a tabela universal gerada.
Esta é a consulta final:
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
Este é o resultado parcial:
<Employee EmpID="1">
<Name FName="Guy" LName="Gilbert" />
</Employee>
<Employee EmpID="2">
<Name FName="Kevin" LName="Brown" />
</Employee>
...
O primeiro SELECT especifica nomes para as colunas no conjunto de linhas resultante. Esses nomes formam dois grupos de colunas. O grupo que tem o valor da Tag 1 no nome da coluna identifica Employee como um elemento e EmpID como o atributo. O outro grupo de colunas tem o valor da Marca 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 Guy Gilbert
1 NULL 2 NULL NULL
2 1 2 Kevin Brown
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 valor 1 da Tag. Portanto o grupo de colunas que quem o valor 1 da Tag é identificado, Employee!1!EmpID. Essa coluna identifica Employee como o nome do elemento. Em seguida, é criado um elemento <Employee> que tem os atributos de EmpID. Os valores das colunas correspondentes são atribuídos a esses atributos.
A segunda linha tem o valor da Tag 2. Portanto o grupo de colunas que tem o valor da Tag 2 no nome da coluna, Name!2!FName, Name!2!LName, é identificado. Esses nomes de colunas identificam Name como o nome do elemento. É criado um elemento <Name> que tem os atributos FName e Lname. Em seguida, os valores das colunas correspondentes são atribuídos a esses atributos. Essa linha identifica 1 como Parent. Esse elemento filho é adicionado ao elemento <Employee> anterior.
Esse processo é repetido para o restante das linhas do conjunto de linhas. Observe a importância de ordenar as linhas na tabela universal de forma que FOR XML EXPLICIT possa processar o conjunto de linhas em ordem e gerar o XML desejado.