Exemplos: uso do modo AUTO

Artigo
06/13/2017

Os exemplos a seguir ilustram o uso do modo AUTO. Muitas dessas consultas são especificadas em relação a documentos XML de instruções da fabricação de bicicletas que são armazenados na coluna da tabela ProductModel no banco de dados de exemplo AdventureWorks2012 .

Exemplo: Recuperando informações de cliente, pedido e detalhes do pedido

Essa consulta recupera informações de cliente, pedido e detalhes do pedido de um cliente específico.

USE AdventureWorks2012;  
GO  
SELECT Cust.CustomerID,   
       OrderHeader.CustomerID,  
       OrderHeader.SalesOrderID,   
       Detail.SalesOrderID, Detail.LineTotal, Detail.ProductID,   
       Product.Name,  
       Detail.OrderQty  
FROM Sales.Customer AS Cust  
INNER JOIN Sales.SalesOrderHeader AS OrderHeader   
    ON Cust.CustomerID = OrderHeader.CustomerID  
INNER JOIN Sales.SalesOrderDetail AS Detail  
    ON OrderHeader.SalesOrderID = Detail.SalesOrderID  
INNER JOIN Production.Product AS Product  
    ON Product.ProductID = Detail.ProductID  
WHERE Cust.CustomerID IN (29672, 29734)  
ORDER BY OrderHeader.CustomerID,  
         OrderHeader.SalesOrderID  
FOR XML AUTO;

Como a consulta identifica alias das tabelas Cust, OrderHeader, Detaile Product , os elementos correspondentes são gerados pelo modo AUTO . Mais uma vez, a ordem em que as tabelas são identificadas pelas colunas especificadas na cláusula SELECT determina a hierarquia desses elementos.

Este é o resultado parcial.

<Cust CustomerID="29672">

<OrderHeader CustomerID="29672" 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="29672" SalesOrderID="47660">

<Detail SalesOrderID="47660" LineTotal="469.794000" ProductID="765" OrderQty="1">

<Product Name="Road-650 Black, 58" />

</Detail>

</OrderHeader>

<OrderHeader CustomerID="29672" SalesOrderID="49857">

<Detail SalesOrderID="49857" LineTotal="44.994000" ProductID="852" OrderQty="1">

<Product Name="Women's Tights, S" />

</Detail>

</OrderHeader>

...

</Cust>

Exemplo: Especificando GROUP BY e funções de agregação

A consulta a seguir retorna IDs de clientes individuais e o número de pedidos do cliente.

USE AdventureWorks2012;  
GO  
SELECT C.CustomerID, COUNT(*) AS NoOfOrders  
FROM Sales.Customer AS C  
INNER JOIN Sales.SalesOrderHeader AS SOH  
On C.CustomerID = SOH.CustomerID  
GROUP BY C.CustomerID  
FOR XML AUTO;This is the partial result:

<I CustomerID="11000" NoOfOrders="3" />

<I CustomerID="11001" NoOfOrders="3" />

...

Exemplo: Especificando colunas computadas no modo AUTO

Essa consulta retorna nomes de clientes individuais concatenados e informações de pedido. Como a coluna computada é atribuída ao nível mais interno encontrado nesse ponto, o <SOH> elemento neste exemplo. Os nomes de clientes concatenados são adicionados como atributos do <SOH> elemento no resultado.

USE AdventureWorks2012;  
GO  
SELECT P.FirstName + ' ' + P.LastName AS Name,  
       SOH.SalesOrderID  
FROM Sales.Customer AS C  
INNER JOIN Sales.SalesOrderHeader AS SOH  
    ON  C.CustomerID = SOH.CustomerID  
INNER JOIN Person.Person AS P  
    ON P.BusinessEntityID = C.PersonID  
FOR XML AUTO;

Este é o resultado parcial:

<SOH Name="Jon Yang" SalesOrderID="43793" />  
<SOH Name="Eugene Huang" SalesOrderID="43767" />

Para recuperar os <IndividualCustomer> elementos que têm o Name atributo que contém cada informação de cabeçalho de pedido de venda como um subelemento, a consulta é reescrita usando uma submarca. A seleção interna cria uma tabela IndividualCustomer temporária com a coluna computada que contém os nomes dos clientes individuais. Em seguida, essa tabela é unida à tabela SalesOrderHeader para obter o resultado.

Observe que a tabela Sales.Customer armazena informações do cliente individual, inclusive o valor de PersonID do cliente. Esse PersonID é usado para localizar o nome de contato da tabela Person.Person .

SELECT IndividualCustomer.Name, SOH.SalesOrderID  
FROM (SELECT FirstName+ ' '+LastName AS Name, C.PersonID, C.CustomerID  
      FROM Sales.Customer AS C, Person.Person AS P  
      WHERE C.PersonID = P.BusinessEntityID) AS IndividualCustomer  
LEFT OUTER JOIN  Sales.SalesOrderHeader AS SOH  
   ON IndividualCustomer.CustomerID = SOH.CustomerID  
ORDER BY IndividualCustomer.CustomerID, SOH.CustomerIDFOR XML AUTO;

Este é o resultado parcial:

<IndividualCustomer Name="Jon Yang">

<SOH SalesOrderID="43793" />

<SOH SalesOrderID="51522" />

<SOH SalesOrderID="57418" />

</IndividualCustomer>

...

Exemplo: Retornando dados binários

Esta consulta retorna uma fotografia do produto a partir da tabela ProductPhoto . ThumbNailPhoto é uma coluna varbinary(max) na tabela ProductPhoto. Por padrão, o modo AUTO retorna para os dados binários uma referência que é uma URL relativa à raiz virtual do banco de dados onde a consulta é executada. O atributo de chave ProductPhotoID deve ser especificado para identificar a imagem. Para recuperar uma referência de imagem, conforme ilustrado neste exemplo, a chave primária da tabela também deve ser especificada na cláusula SELECT para identificar uma linha exclusivamente.

SELECT ProductPhotoID, ThumbNailPhoto  
FROM   Production.ProductPhoto   
WHERE ProductPhotoID=70  
FOR XML AUTO;

Este é o resultado:

-- result

<Production.ProductPhoto

ProductPhotoID="70"

ThumbNailPhoto= "dbobject/Production.ProductPhoto[@ProductPhotoID='70']/@ThumbNailPhoto" />

A mesma consulta é executada com a opção BINARY BASE64 . A consulta retorna os dados binários em formato codificado na base64.

SELECT ProductPhotoID, ThumbNailPhoto  
FROM   Production.ProductPhoto   
WHERE ProductPhotoID=70  
FOR XML AUTO, BINARY BASE64;

Este é o resultado:

-- result

<Production.ProductPhoto ProductPhotoID="70" ThumbNailPhoto="Base64 encoded photo" />

Por padrão, quando o modo AUTO é usado para recuperar dados binários, em vez dos dados binários, é retornada uma referência a uma URL relativa à raiz virtual do banco de dados onde a consulta foi executada. Isso acontecerá se a opção BINARY BASE64 não estiver especificada.

Quando o modo AUTO retorna uma referência de URL aos dados binários em bancos de dados que não diferenciam maiúsculas de minúsculas, a consulta é executada quando um nome de tabela ou coluna especificado na consulta não corresponde ao nome da tabela ou coluna do banco de dados. Porém, as maiúsculas e minúsculas retornadas na referência não são consistentes. Por exemplo:

SELECT ProductPhotoID, ThumbnailPhoto  
FROM   Production.ProductPhoto   
WHERE  ProductPhotoID=70  
FOR XML AUTO;

Este é o resultado:

<Production.PRODUCTPHOTO

PRODUCTPHOTOID="70"

THUMBNAILPHOTO= "dbobject/Production.PRODUCTPHOTO[@ProductPhotoID='70']/@ThumbNailPhoto" />

Isso pode ser um problema, principalmente quando são executadas consultas de dbobject em um banco de dados que diferencia maiúsculas e minúsculas. Para evitar isso, as maiúsculas e minúsculas do nome da tabela ou coluna especificado nas consultas devem corresponder às maiúsculas e minúsculas do nome da tabela ou coluna do banco de dados.

Exemplo: Entendendo a codificação

Este exemplo mostra as várias codificações que ocorrem no resultado.

Crie esta tabela:

CREATE TABLE [Special Chars] (Col1 char(1) primary key, [Col#&2] varbinary(50));

Adicione os seguintes dados à tabela:

INSERT INTO [Special Chars] VALUES ('&', 0x20), ('#', 0x20);

Esta consulta retorna os dados da tabela. O modo FOR XML AUTO é especificado. São retornados dados binários como uma referência.

SELECT * FROM [Special Chars] FOR XML AUTO;

Este é o resultado:

<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"

/>

Este é o processo para codificar caracteres especiais no resultado:

No resultado da consulta, os caracteres especiais XML e URL nos nomes dos elementos e atributos que são retornados são codificados usando o valor hexadecimal do caractere Unicode correspondente. No resultado anterior, o nome <Special Chars> do elemento é retornado como <Special_x0020_Chars>. O nome <Col#&2> do atributo é retornado como <Col_x0023__x0026_2>. Os caracteres especiais de XML e URL são codificados.
Se os valores dos elementos ou atributo contiverem qualquer uma das cinco entidades de caractere XML padrão (', "", <, >e &), esses caracteres XML especiais serão sempre codificados usando a codificação de caracteres XML. No resultado anterior, o valor & no valor do atributo><Col1é codificado como .& Entretanto, o caractere # permanece #, porque é um caractere XML válido e não um caractere XML especial.
Se os valores dos elementos e atributos contiverem qualquer caractere especial da URL com significado especial na URL, eles serão codificados apenas no valor da URL DBOBJECT e apenas quando o caractere especial fizer parte de um nome de tabela ou coluna. No resultado, o caractere # que faz parte do nome da tabela Col#&2 é codificado como _x0023_ in the DBOJBECT URL.

Consulte Também

Usar o modo AUTO com FOR XML

Partilhar via

Exemplos: uso do modo AUTO

Exemplo: Recuperando informações de cliente, pedido e detalhes do pedido

Exemplo: Especificando GROUP BY e funções de agregação

Exemplo: Especificando colunas computadas no modo AUTO

Exemplo: Retornando dados binários

Exemplo: Entendendo a codificação

Consulte Também

Recursos adicionais