Usar el modo AUTO
Como se explica en el tema Generar XML mediante FOR XML, el modo AUTO devuelve los resultados de una consulta como elementos XML anidados. Esto no ofrece un gran control sobre la forma del XML generado a partir del resultado de una consulta. Las consultas en modo AUTO son útiles si desea generar jerarquías sencillas. Sin embargo, Utilizar el modo EXPLICIT y Usar el modo PATH ofrecen un mayor control y una mayor flexibilidad a la hora de decidir la forma del XML procedente del resultado de una consulta.
Cada tabla de la cláusula FROM, de la que al menos se presenta una columna en la cláusula SELECT, se representa como un elemento XML. Las columnas que se incluyen en la cláusula SELECT se asignan a atributos o subelementos, si se especifica la opción ELEMENTS en la cláusula FOR XML.
La jerarquía XML, anidamiento de los elementos, del XML resultante está basada en el orden de las tablas identificadas por las columnas especificadas en la cláusula SELECT. Por tanto, el orden en que se especifican los nombres de columna en la cláusula SELECT es importante. La primera, la tabla situada más a la izquierda que se identifica, constituye el elemento superior del documento XML resultante. La segunda tabla situada más a la izquierda, identificada por las columnas de la instrucción SELECT, constituye un subelemento del elemento superior, etc.
Si un nombre de columna que aparece en la cláusula SELECT procede de una tabla ya identificada por una columna especificada anteriormente en la cláusula SELECT, la columna se agrega como atributo del elemento ya creado, en lugar de abrir un nuevo nivel de jerarquía. Si se especifica la opción ELEMENTS, la columna se agrega como atributo.
Por ejemplo, ejecute esta consulta:
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,
Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO
Éste es el resultado parcial:
<Cust CustomerID="1" CustomerType="S">
<OrderHeader CustomerID="1" SalesOrderID="43860" Status="5" />
<OrderHeader CustomerID="1" SalesOrderID="44501" Status="5" />
<OrderHeader CustomerID="1" SalesOrderID="45283" Status="5" />
<OrderHeader CustomerID="1" SalesOrderID="46042" Status="5" />
</Cust>
...
Observe lo siguiente en la cláusula SELECT:
- CustomerID hace referencia a la tabla Cust. Por tanto, se crea un elemento <
Cust> y se agrega CustomerID como su atributo. - A continuación, tres columnas, OrderHeader.CustomerID, OrderHeader.SaleOrderID y OrderHeader.Status, hacen referencia a la tabla OrderHeader. Por tanto, se agrega un elemento <
OrderHeader> como subelemento del elemento <Cust> y las tres columnas se agregan como atributos de <OrderHeader>. - A continuación, la columna Cust.CustomerType hace referencia de nuevo a la tabla Cust, que ya se había identificado con la columna Cust.CustomerID. Por tanto, no se crea ningún elemento nuevo. En su lugar, se agrega el atributo CustomerType al elemento <
Cust> que se había creado anteriormente. - La consulta especifica alias para los nombres de tabla. Estos alias aparecen como nombres de los elementos correspondientes.
- ORDER BY es necesario para agrupar todos los elementos secundarios en un único elemento primario.
Esta consulta es similar a la anterior, pero la cláusula SELECT especifica columnas en la tabla OrderHeader antes de las columnas de la tabla Cust. Por tanto, se crea primero el elemento <OrderHeader> y después se le agrega el elemento secundario <Cust>.
select OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,
Cust.CustomerID,
Cust.CustomerType
from Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
where Cust.CustomerID = OrderHeader.CustomerID
for xml auto
Éste es el resultado parcial:
<OrderHeader CustomerID="1" SalesOrderID="43860" Status="5">
<Cust CustomerID="1" CustomerType="S" />
</OrderHeader>
...
Si se agrega la opción ELEMENTS a la cláusula FOR XML, se devuelve XML centrado en elementos.
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,
Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO, ELEMENTS
Éste es el resultado parcial:
<Cust>
<CustomerID>1</CustomerID>
<CustomerType>S</CustomerType>
<OrderHeader>
<CustomerID>1</CustomerID>
<SalesOrderID>43860</SalesOrderID>
<Status>5</Status>
</OrderHeader>
...
</Cust>
...
En esta consulta, los valores CustomerID de una fila se comparan con los de la siguiente al crear elementos <Cust>, porque CustomerID es la clave principal de la tabla. Si no se identifica CustomerID como clave principal de la tabla, todos los valores de columna (CustomerID, CustomerType en esta consulta) de una fila se comparan con los de la siguiente. Si los valores difieren, se agrega un nuevo elemento <Cust> al XML.
Cuando se comparan estos valores de columna, si algunas de las columnas que se comparan son de tipo text, ntext, image o xml, FOR XML asume que los valores son diferentes y no los compara, incluso si son los mismos. Esto se debe a que no se admite la comparación de objetos grandes. Se agregan elementos al resultado para cada fila seleccionada. Tenga en cuenta que se comparan las columnas de (n)varchar(max) y varbinary(max).
Si no se puede asociar una columna de la cláusula SELECT con ninguna de las tablas identificadas en la cláusula FROM, como en el caso de una columna de agregado o una columna calculada, se agrega la columna en el documento XML en el nivel de anidamiento más profundo cuando se encuentra en la lista. Si esa columna aparece como la primera de la cláusula SELECT, la columna se agrega al elemento superior.
Si se especifica el carácter comodín * (asterisco) en la cláusula SELECT, el anidamiento se determina del mismo modo que se ha explicado anteriormente, en función de las filas devueltas por el motor de consulta.
Si en la consulta se especifica la opción BINARY BASE64, los datos binarios se devuelven en formato codificado en base64. De manera predeterminada, si en la consulta no se especifica la opción BINARY BASE64, el modo AUTO admite la codificación URL de datos binarios. Es decir, en lugar de datos binarios, se devuelve una referencia a una dirección URL relativa a la raíz virtual de la base de datos donde se ejecutó la consulta. Esta referencia se puede utilizar para obtener acceso a los datos binarios reales en operaciones posteriores utilizando la consulta SQLXML ISAPI dbobject. La consulta debe proporcionar suficiente información, como columnas de clave principal, para identificar la imagen.
Al especificar una consulta, si se utiliza un alias para la columna binaria de la vista, se devuelve el alias en la codificación URL de los datos binarios. En operaciones posteriores, el alias no tiene ningún significado y no es posible utilizar la codificación de dirección URL para recuperar la imagen. Por consiguiente, no utilice alias si consulta una vista con el modo FOR XML AUTO.
Descripción de la heurística del modo AUTO para dar forma al XML devuelto
El modo AUTO determina la forma del XML devuelto en función de la consulta. Al determinar cómo se anidan los elementos, la heurística del modo AUTO compara los valores de columna de filas adyacentes. Se comparan columnas de todos los tipos, excepto ntext, text, image y xml. Se comparan columnas de tipo (n)varchar(max) y varbinary(max).
El siguiente ejemplo muestra la heurística del modo AUTO que determina la forma del XML resultante:
SELECT T1.Id, T2.Id, T1.Name
FROM T1, T2
WHERE ...
FOR XML AUTO
ORDER BY T1.Id
Para determinar dónde comienza un nuevo elemento <T1>, se comparan todos los valores de columna de T1, excepto ntext, text, image y xml, si no se especifica la clave en la tabla T1. A continuación, imagine que la columna Name es de tipo nvarchar(40) y la instrucción SELECT devuelve este conjunto de filas:
T1.Id T1.Name T2.Id
-----------------------
1 Andrew 2
1 Andrew 3
1 Nancy 4
La heurística del modo AUTO compara todos los valores de la tabla T1, las columnas Id y Name. Puesto que las dos primeras filas tienen los mismos valores para las columnas Id y Name, se agrega al resultado un elemento <T1> que tiene dos elementos <T2> secundarios.
A continuación se muestra el XML devuelto:
<T1 Id="1" Name="Andrew">
<T2 Id="2" />
<T2 Id="3" />
</T1>
<T1 Id="1" Name="Nancy" >
<T2 Id="4" />
</T>
Imagine ahora que la columna Name es de tipo text. La heurística del modo AUTO no compara los valores para este tipo. En su lugar, asume que los valores no son los mismos. Esto genera el siguiente XML:
<T1 Id="1" Name="Andrew" >
<T2 Id="2" />
</T1>
<T1 Id="1" Name="Andrew" >
<T2 Id="3" />
</T1>
<T1 Id="1" Name="Nancy" >
<T2 Id="4" />
</T1>
Ejemplos
Los siguientes ejemplos ilustran el uso del modo AUTO. Muchas de estas consultas se especifican utilizando los documentos XML de instrucciones de fabricación de bicicletas almacenados en la columna Instructions de la tabla ProductModel. Para obtener más información acerca de las instrucciones XML, vea Representación de tipo de datos xml en la base de datos AdventureWorks.
A. Recuperar información de clientes, pedidos y pedidos detallados
Esta consulta recupera información del cliente, pedidos y pedidos detallados de un cliente específico.
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
Detail.SalesOrderID, Detail.LineTotal,Detail.ProductID,
Product.Name,
Detail.OrderQty
FROM Sales.Customer Cust,
Sales.SalesOrderHeader OrderHeader,
Sales.SalesOrderDetail Detail,
Production.Product Product
WHERE Cust.CustomerID = OrderHeader.CustomerID
AND OrderHeader.SalesOrderID = Detail.SalesOrderID
AND Detail.ProductID = Product.ProductID
AND (Cust.CustomerID=117 or Cust.CustomerID=442)
ORDER BY OrderHeader.CustomerID,
OrderHeader.SalesOrderID
FOR XML AUTO
La consulta identifica los alias de tabla Cust, OrderHeader, Detail y Product, por lo que el modo AUTO genera los elementos correspondientes. De nuevo, el orden en que se identifican las tablas con las columnas especificadas en la cláusula SELECT determina la jerarquía de estos elementos.
El resultado parcial es el siguiente.
<Cust CustomerID="117">
<OrderHeader CustomerID="117" SalesOrderID="43660">
<Detail SalesOrderID="43660" LineTotal="874.794000" ProductID="758" OrderQty="1">
<Product Name="Road-450 Red, 52" />
</Detail>
<Detail SalesOrderID="43660" LineTotal="419.458900" ProductID="762" OrderQty="1">
<Product Name="Road-650 Red, 44" />
</Detail>
</OrderHeader>
<OrderHeader CustomerID="117" SalesOrderID="47660">
<Detail SalesOrderID="47660" LineTotal="469.794000" ProductID="765" OrderQty="1">
<Product Name="Road-650 Black, 58" />
</Detail>
</OrderHeader>
<OrderHeader CustomerID="117" SalesOrderID="49857">
<Detail SalesOrderID="49857" LineTotal="44.994000" ProductID="852" OrderQty="1">
<Product Name="Women's Tights, S" />
</Detail>
</OrderHeader>
...
</Cust>
B. Especificar GROUP BY y funciones de agregado
La consulta siguiente devuelve los Id. de cliente individuales y el número de pedidos que ha solicitado el cliente.
SELECT I.CustomerID, count(*) as NoOfOrders
from Sales.Individual I,Sales.SalesOrderHeader SOH
WHERE I.CustomerID = SOH.CustomerID
GROUP BY I.CustomerID
FOR XML AUTO
Éste es el resultado parcial:
<I CustomerID="11000" NoOfOrders="3" />
<I CustomerID="11001" NoOfOrders="3" />
...
C. Especificar columnas calculadas en el modo AUTO
Esta consulta devuelve nombres de cliente individuales concatenados y la información de los pedidos. La columna calculada se asigna al nivel más interno de ese punto, el elemento <SOH> en este ejemplo. Los nombres de cliente concatenados se agregan como atributos del elemento <SOH> en el resultado.
select C.FirstName + ' ' + C.LastName as Name,
SOH.SalesOrderID
from Sales.Individual I, Person.Contact C,
Sales.SalesOrderHeader SOH
where I.ContactID = C.ContactID
AND I.CustomerID = SOH.CustomerID
FOR XML AUTO
Éste es el resultado parcial:
<SOH Name="David Robinett" SalesOrderID="53647" />
<SOH Name="Rebecca Robinson" SalesOrderID="72188" />
Para recuperar los elementos <IndividualCustomer> teniendo el atributo Name que contiene la información de encabezado de cada pedido de ventas como un subelemento, la consulta se escribe de nuevo utilizando un elemento sub select. El elemento select interno crea una tabla IndividualCustomer temporal con la columna calculada que contiene los nombres de los clientes individuales. Esta tabla se combina después con la tabla SalesOrderHeader para obtener el resultado.
Observe que la tabla Sales.Individual almacena información de clientes individuales, incluido el valor ContactID del cliente. Este ContactID se utiliza para buscar el nombre de contacto en la tabla Person.Contact.
SELECT IndividualCustomer.Name, SOH.SalesOrderID
FROM (SELECT FirstName+ ' '+LastName as Name, I.CustomerID
FROM Sales.Individual I, Person.Contact C
WHERE I.ContactID = C.ContactID) IndividualCustomer
left outer join Sales.SalesOrderHeader SOH
ON IndividualCustomer.CustomerID = SOH.CustomerID
ORDER BY IndividualCustomer.CustomerID, SOH.CustomerID
FOR XML AUTO
Éste es el resultado parcial:
<IndividualCustomer Name="Jon Yang">
<SOH SalesOrderID="43793" />
<SOH SalesOrderID="51522" />
<SOH SalesOrderID="57418" />
</IndividualCustomer>
...
...
D. Devolver datos binarios
Esta consulta devuelve la fotografía de un empleado de la tabla Employees. Photo es una columna image en la tabla Employees. De manera predeterminada, el modo AUTO devuelve a los datos binarios una referencia que es una dirección URL relativa a la raíz virtual de la base de datos donde se ejecuta la consulta. Se debe especificar el atributo de la clave EmployeeID para identificar la imagen. Al recuperar la referencia de una imagen como se muestra en este ejemplo, también debe especificarse la clave principal en la cláusula SELECT para identificar una fila de forma única.
SELECT ProductPhotoID, ThumbNailPhoto
FROM Production.ProductPhoto
WHERE ProductPhotoID=70
FOR XML AUTO
El resultado es el siguiente:
-- result
<Production.ProductPhoto
ProductPhotoID="70"
ThumbNailPhoto= "dbobject/Production.ProductPhoto[@ProductPhotoID='70']/@ThumbNailPhoto" />
La misma consulta se ejecuta con la opción BINARY BASE64. La consulta devuelve los datos binarios en formato codificado en base64.
SELECT ProductPhotoID, ThumbNailPhoto
FROM Production.ProductPhoto
WHERE ProductPhotoID=70
FOR XML AUTO, BINARY BASE64
El resultado es el siguiente:
-- result
<Production.ProductPhoto ProductPhotoID="70" ThumbNailPhoto="Base64 encoded photo" />
De forma predeterminada, cuando se utiliza el modo AUTO para recuperar datos binarios, se devuelve una referencia a una dirección URL relativa a la raíz virtual de la base de datos donde se ejecutó la consulta en lugar de datos binarios. Esto ocurre si no se especifica la opción BINARY BASE64.
Cuando el modo AUTO devuelve una referencia de URL a los datos binarios de bases de datos que no distinguen mayúsculas y minúsculas y donde un nombre de tabla o columna especificado en la consulta no coincide con el nombre de tabla o columna de la base de datos, se ejecuta la consulta. Sin embargo, las mayúsculas o minúsculas devueltas en la referencia no serán coherentes. Por ejemplo:
SELECT PRODUCTPHOTOID, THUMBNAILPHOTO
FROM Production.PRODUCTPHOTO
WHERE PRODUCTPHOTOID=70
FOR XML AUTO
El resultado es el siguiente:
<Production.PRODUCTPHOTO
PRODUCTPHOTOID="70"
THUMBNAILPHOTO= "dbobject/Production.PRODUCTPHOTO[@ProductPhotoID='70']/@ThumbNailPhoto" />
Eso puede ser un problema especialmente cuando se ejecutan consultas dbobject en una base de datos que distingue mayúsculas y minúsculas. Para evitarlo, el formato de mayúsculas y minúsculas del nombre de tabla o columna especificado en las consultas debe coincidir con el formato de mayúsculas y minúsculas del nombre de tabla o columna de la base de datos.
E. Descripción de la codificación
Este ejemplo muestra varias codificaciones que tienen lugar en el resultado.
Cree esta tabla:
CREATE TABLE [Special Chars] (Col1 char(1) primary key, [Col#&2] varbinary(50))
Agregue los siguientes datos a la tabla:
INSERT INTO [Special Chars] values ('&', 0x20)
INSERT INTO [Special Chars] values ('#', 0x20)
Esta consulta devuelve los datos de la tabla. El modo FOR XML AUTO está especificado. Los datos binarios se devuelven como una referencia.
SELECT * FROM [Special Chars] FOR XML AUTO
El resultado es el siguiente:
<Special_x0020_Chars
Col1="#"
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='#']/@Col_x0023__x0026_2"
/>
<Special_x0020_Chars
Col1="&"
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='&']/@Col_x0023__x0026_2"
/>
Éste es el proceso para codificar caracteres especiales en el resultado:
- En el de resultado de la consulta, los caracteres especiales XML y URL de los nombres del elemento y atributo devueltos se codifican mediante el valor hexadecimal del carácter Unicode correspondiente. En el resultado anterior, el nombre de elemento <
Special Chars> se devuelve como <Special_x0020_Chars>. El nombre de atributo <Col#&2> se devuelve como <Col_x0023__x0026_2>. Los caracteres especiales XML y URL están codificados. - Si los valores de los elementos o atributos contienen alguna de las cinco entidades de carácter XML estándar (', "", <, > y &), estos caracteres XML especiales se codifican siempre utilizando la codificación de caracteres XML. En el resultado anterior, el valor & del valor de atributo <
Col1> está codificado como &. Sin embargo, el carácter # permanece como #, porque es un carácter XML válido y no un carácter XML especial. - Si los valores de los elementos o atributos contienen caracteres especiales de dirección URL que tienen un significado especial en la dirección URL, sólo se codifican en el valor DBOBJECT de la dirección URL y únicamente cuando el carácter especial forma parte de un nombre de columna o tabla. En el resultado, el carácter # que forma parte del nombre de tabla Col#&2 está codificado como _x0023_ en el valor DBOJBECT de la dirección URL.