Exemplos: uso do modo AUTO

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

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

Exemplo: recuperar informações de clientes, pedidos e detalhes de pedidos

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

USE AdventureWorks2022;
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: especificar funções GROUP BY e de agregação

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

USE AdventureWorks2022;
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;

Este é o resultado parcial:

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

Exemplo: especificar colunas computadas no modo AUTO

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

USE AdventureWorks2022;
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 elementos <IndividualCustomer> que têm o atributo Name que contém cada informação de cabeçalho de ordem de venda como um subelemento, a consulta é reescrita usando uma subseleção. 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.

A tabela Sales.Customer armazena informações individuais do cliente, incluindo o valor PersonID desse 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.CustomerID FOR XML AUTO;

Este é o resultado parcial:

<IndividualCustomer Name="Jon Yang">
  <SOH SalesOrderID="43793" />
  <SOH SalesOrderID="51522" />
  <SOH SalesOrderID="57418" />
</IndividualCustomer>
...

Exemplo: retornar 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. Ao 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 de forma exclusiva.

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

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:

<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 ocorrerá se a opção BINARY BASE64 não for 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, em que um nome de tabela ou coluna especificado na consulta não corresponde ao nome da tabela ou coluna no banco de dados, a consulta é executada. No entanto, a formatação retornada na referência não será consistente. 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, especialmente quando consultas dbobject são executadas em um banco de dados que diferencia maiúsculas de 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: entender 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 do elemento <Special Chars> é retornado como <Special_x0020_Chars>. O nome do atributo <Col#&2> é retornado como <Col_x0023__x0026_2>. Os caracteres especiais de XML e URL são codificados.

  • Se os valores dos elementos ou atributos contiverem qualquer uma das cinco entidades de caracteres XML padrão (', "", <, > e &), esses caracteres XML especiais sempre serão codificados usando a codificação de caracteres XML. No resultado anterior, o valor & no valor do atributo <Col1> é codificado como &. No entanto, o caractere # continua sendo #, 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.

Confira também