Compartilhar via

  • Artigo

Exemplos: Usando modo AUTO

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 Instructions da tabela ProductModel. Para obter mais informações sobre as instruções XML, consulte Representação de tipo de dados xml no banco de dados do AdventureWorks.

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.

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

Como a consulta identifica alias das tabelas Cust, OrderHeader, Detail e Product, os elementos correspondentes são gerados pelo modo AUTO. De novo, 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="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>

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.

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

Este é o resultado parcial:

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

Exemplo: Especificando colunas computadas em modo AUTO

Essa consulta retorna nomes de clientes individuais concatenados e informações de pedido. Porque a coluna computada é atribuída ao nível interno encontrado naquele ponto, o elemento <SOH> neste exemplo. Os nomes concatenados dos clientes são adicionados como atributos do elemento <SOH> no 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

Este é o resultado parcial:

<SOH Name="David Robinett" SalesOrderID="53647" />
<SOH Name="Rebecca Robinson" SalesOrderID="72188" />

Para recuperar os elementos <IndividualCustomer> que têm o atributo Name que contém cada informação de cabeçalho de pedidos de vendas 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.

Observe que a tabela Sales.Individual armazena informações do cliente individual, inclusive o valor de ContactID do cliente. Esse ContactID é usado para localizar o nome de contato da tabela 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

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 a foto de um funcionário da tabela Employees. Photo é uma coluna de imagem na tabela Employees. 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 EmployeeID 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 a 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)
INSERT INTO [Special Chars] values ('#', 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="&amp;" 
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='&amp;']/@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, os <Special Chars> do nome do elemento são retornados 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 do atributo contiverem qualquer uma das cinco entidades de caracteres 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 &amp;. Porém, 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 que tenham significado especial na URL, eles serão codificados apenas no valor da URL DBOBJECT e serão codificados 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_ na URL de DBOJBECT.

Consulte também

Referência

Usando o modo AUTO