Exemplo: recuperar informações de funcionários

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

Este exemplo recupera uma ID e um nome de funcionário para cada funcionário. No banco de dados AdventureWorks2022 , 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.

Suponha que você queira que a transformação FOR XML EXPLICIT gere um XML, como mostra o exemplo a seguir:

<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. Esta é a primeira consulta que recupera valores para o elemento <Employee> e seus atributos. A consulta atribui 1 como o valor de Tag para o elemento <Employee> e NULL como Parent, pois 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. Ela recupera valores do elemento <Name>. Ela atribui 2 como o valor Tag para o elemento <Name> e 1 como o valor de marca Parent 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 o valor Tag2 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 Pai Employee!1!EmpID Name!2!FName Name!2!LName
1 NULO 1 NULL NULO
2 1 1 Ken Monte
1 NULO 2 NULL NULO
2 1 2 Terri Barros
1 NULO 3 NULL NULO
2 1 3 Carlos Pereira
... ... ... ... ...

É 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 seguida, é criado um elemento <Employee> que tem atributos EmpID. 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. É criado um elemento <Name> que tem 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.

Confira também