Exemplo: Recuperando informações de funcionários
Este exemplo recupera uma ID e um nome de funcionário para cada funcionário. No banco de dados AdventureWorks2012 , a employeeID pode ser obtida na coluna BusinessEntityID da tabela Employee. Nomes de funcionários podem ser obtidos da tabela Person. A coluna BusinessEntityID pode ser usada para unir as tabelas.
Digamos que você queira a transformação de FOR XML EXPLICIT para gerar XML, conforme mostrado aqui:
<Employee EmpID="1" >
<Name FName="Ken" LName="S??nchez" />
</Employee>
...
Como há dois níveis na hierarquia, você escreve duas consultas SELECT e aplica 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 como Parent, porque é 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 que identifica <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 estas consultas com UNION ALL, aplica FOR XML EXPLICIT, e especifica a cláusula ORDER BY exigida. Você deve classificar o conjunto de linhas primeiro por BusinessEntityID e, em seguida, pelo nome para que os valores NULL no nome sejam exibidos primeiro. Se você executar a consulta a seguir sem a cláusula FOR XML, poderá 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 as colunas no conjunto de linhas resultante. Esses nomes formam dois grupos de colunas. O grupo que tem o valor da Tag1 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.
Esta tabela 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 valor 1 da Tag1. Portanto, o grupo de colunas que tem o valor da Tag1 é identificado, Employee!1!EmpID. Essa coluna identifica Employee como o nome do elemento. Em <Employee> seguida, é criado um elemento que tem EmpID atributos. Os valores das colunas correspondentes são atribuídos a esses atributos.
A segunda linha tem o valor da Tag2. Portanto, o grupo de colunas que tem o valor da Tag2 no nome da coluna, Name!2!FName, Name!2!LName, é identificado. Esses nomes de colunas identificam Name como o nome do elemento. Um <Name> elemento é criado com FName atributos e LName . Em seguida, os valores das colunas correspondentes são atribuídos a esses atributos. Essa linha identifica 1 como Parent. Esse filho do elemento é adicionado ao elemento anterior <Employee> .
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.