Exemplos: usando modo AUTO

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

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

Essa consulta recupera informações de clientes, pedidos e detalhes de pedidos para 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 Cust, OrderHeader, Detail e aliases de tabela Product, os elementos correspondentes são gerados pelo modo AUTO. Novamente, a ordem na qual as tabelas são identificadas pelas colunas especificadas na SELECT cláusula determinam 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 individuais do cliente e o número de pedidos solicitados pelo 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 as informações do pedido. Como a coluna computada é atribuída ao nível mais interno encontrado nesse ponto, o elemento <SOH> 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 elementos <, IndividualCustomer, > que possuem o atributo Name, e que incluem como subelemento cada informação de cabeçalho do pedido de vendas, a consulta é reescrita usando uma subconsulta. A seleção interna cria uma tabela temporária IndividualCustomer com a coluna computada que contém os nomes dos clientes individuais. Essa tabela é então unida à SalesOrderHeader tabela para obter o resultado.

Observe que a Sales.Customer tabela armazena informações individuais do cliente, incluindo o PersonID valor desse cliente. Isso é então usado para encontrar o nome do contato na 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

Essa consulta retorna uma foto do produto da ProductPhoto tabela. ThumbNailPhoto é uma varbinary(max) coluna na ProductPhoto tabela. Por padrão, AUTO o modo retorna aos dados binários uma referência que é uma URL relativa à raiz virtual do banco de dados em que a consulta é executada. O ProductPhotoID atributo de chave 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 SELECT cláusula para identificar exclusivamente uma linha.

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 no formato codificado em 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 você usa o modo AUTO para recuperar dados binários, uma referência a uma URL relativa à raiz virtual do banco de dados em que a consulta foi executada será retornada em vez dos dados binários. 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 da coluna no banco de dados, a consulta é executada. No entanto, o caso retornado 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, o caso do nome da tabela ou da coluna especificado nas consultas deve corresponder ao caso do nome da tabela ou da coluna no banco de dados.

Exemplo: Noções básicas sobre 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);

Essa consulta retorna os dados da tabela. O modo FOR XML AUTO é especificado. Os dados binários são retornados 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"

/>

Esse é o processo para codificar caracteres especiais no resultado:

No resultado da consulta, os caracteres XML e URL especiais nos nomes de elemento e atributo 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>. Caracteres especiais 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 sempre serão codificados usando codificação de caractere XML. No resultado anterior, o valor & no valor do atributo <Col1> é codificado como &. No entanto, o caractere # permanece #, porque ele é um caractere XML válido e não um caractere XML especial.
Se os valores dos elementos ou atributos contiverem caracteres de URL especiais que tenham um significado especial na URL, eles serão codificados somente no valor da URL DBOBJECT e serão codificados somente quando o caractere especial fizer parte de uma tabela ou nome de coluna. No resultado, o caractere # que faz parte do nome Col#&2 da tabela é codificado como _x0023_ in the DBOJBECT URL.

Consulte Também

Usar o modo AUTO com FOR XML

Last updated on 2017-06-13

Compartilhar via

Exemplos: usando modo AUTO

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

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

Exemplo: especificando colunas computadas no modo AUTO

Exemplo: Retornando dados binários

Exemplo: Noções básicas sobre a codificação

Consulte Também

Recursos adicionais