Ejemplo: Recuperar información de los empleados
En este ejemplo, se recupera el Id. y el nombre de cada empleado. En la base de datos AdventureWorks, el Id. de empleado se puede obtener de la tabla Employee. Los nombres de los empleados se pueden obtener de la tabla Contact. Para combinar las tablas, se puede usar la columna ContactID.
Supongamos que desea una transformación FOR XML EXPLICIT para generar XML como se indica a continuación:
<Employee EmpID="1" >
<Name FName="Guy" LName="Gilbert" />
</Employee>
...
Dado que hay dos niveles en la jerarquía, habría que escribir dos consultas SELECT y aplicar UNION ALL. Ésta es la primera consulta, que recupera los valores correspondientes al elemento <Employee> y sus atributos. La consulta asigna 1 como valor de Tag para el elemento <Employee> y NULL como Parent, puesto que se trata del elemento de nivel 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
Ésta es la segunda consulta. Recupera los valores correspondientes al elemento <Name>. Asigna 2 como valor de Tag para el elemento <Name> y 1 como valor de etiqueta en Parent, para identificar <Employee> como elemento primario.
SELECT 2 as Tag,
1 as Parent,
EmployeeID,
FirstName,
LastName
FROM HumanResources.Employee E, Person.Contact C
WHERE E.ContactID = C.ContactID
Estas consultas se combinan con UNION ALL, se aplica FOR XML EXPLICIT y se especifica la cláusula ORDER BY requerida. El conjunto de filas se debe ordenar primero según el valor de EmployeeID y, a continuación, por nombre, de modo que los valores NULL en el nombre aparezcan al principio. Si ejecuta la siguiente consulta sin la cláusula FOR XML, podrá ver la tabla universal generada.
Ésta es la 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
Éste es el resultado parcial:
<Employee EmpID="1">
<Name FName="Guy" LName="Gilbert" />
</Employee>
<Employee EmpID="2">
<Name FName="Kevin" LName="Brown" />
</Employee>
...
El primer SELECT especifica nombres para columnas del conjunto de filas resultante. Estos nombres forman dos grupos de columnas. El grupo con el valor 1 de Tag en el nombre de columna identifica Employee como elemento y EmpID como atributo. El otro grupo de columnas tiene el valor 2 de Tag e identifica <Name> como elemento, y FName y LName como atributos.
La siguiente tabla muestra el conjunto de filas parcial generado por la 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
...
Así es como se procesan las filas de la tabla universal para crear el árbol XML resultante:
La primera fila identifica el valor 1 en Tag. Por consiguiente, se identifica el grupo de columnas que tiene el valor 1 en Tag, Employee!1!EmpID. Esta columna identifica Employee como nombre de elemento. A continuación, se crea un elemento <Employee> con atributos EmpID. Los valores de columna correspondientes se asignan a estos atributos.
La segunda fila tiene el valor 2 en Tag. Por lo tanto, se identifica el grupo de columnas con valor 2 en Tag en el nombre de columna, Name!2!FName, Name!2!LName. Estos nombres de columna identifican Name como nombre de elemento. Se crea un elemento <Name> con atributos FName y LName. A continuación, se asignan los valores de columna correspondientes a estos atributos. Esta fila identifica 1 como valor de Parent. Este elemento secundario se agrega al elemento <Employee> anterior.
Este proceso se repite con el resto de filas del conjunto. Observe la importancia de ordenar las filas de la tabla universal para que FOR XML EXPLICIT pueda procesar el conjunto de filas por orden y generar el XML deseado.