Статья
09/28/2010

Примеры. Использование режима AUTO

В следующем примере иллюстрируется применение режима AUTO. Многие из этих запросов являются запросами к XML-документам с инструкциями по производству велосипедов, хранящимся в столбце Instructions таблицы ProductModel. Дополнительные сведения об инструкциях XML см. в разделе Представление типов XML-данных в базе данных AdventureWorks.

Пример. Извлечение данных о заказчике, заказе и подробных сведений о заказе

Следующий запрос получает данные о заказчике, заказе и подробные данные о заказе определенного заказчика.

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

Так как в заказе указываются псевдонимы таблиц Cust, OrderHeader, Detail и Product, в режиме AUTO формируются соответствующие элементы. И вновь порядок, в котором таблицы идентифицируются столбцами, задаваемыми в предложении SELECT, определяет иерархию этих элементов.

Частичный результат.

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

Пример. Использование предложения GROUP BY и статистических функций

Следующий запрос возвращает отдельные идентификаторы заказчиков и номера заказов, запрашиваемых заказчиками.

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

Промежуточный результат:

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

Пример. Задание вычисляемых столбцов в режиме AUTO

Этот пример возвращает сцепленные имена заказчиков и данные для заказа. Так как вычисляемый столбец назначен самому внутреннему уровню, встретившемуся на данный момент, в этом примере — элементу <SOH>, сцепленные имена заказчиков добавляются в результат как атрибуты элемента <SOH>.

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

Промежуточный результат:

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

Для получения элементов <IndividualCustomer> с атрибутом Name, содержащим данные заголовков всех заказов на продажу в качестве подчиненного элемента, запрос переписывается при помощи подзапроса SELECT. Внутренняя выборка создает временную таблицу IndividualCustomer с вычисляемым столбцом, содержащим имена отдельных заказчиков. Эта таблица затем соединяется с таблицей SalesOrderHeader для получения результата.

Обратите внимание на то, что в таблице Sales.Individual хранятся данные для отдельного заказчика, в том числе значение ContactID для заказчика. Идентификатор ContactID используется затем для нахождения имени контактного лица из таблицы 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

Промежуточный результат:

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

Пример. Возврат двоичных данных

Запрос возвращает фотографию служащего из таблицы Служащие. Photo — столбец типа image таблицы Служащие. По умолчанию режим AUTO возвращает ссылку на двоичные данные, являющуюся относительным URL-адресом виртуального корня базы данных, на которой выполняется запрос. Для идентификации изображения должен задаваться ключевой атрибут EmployeeID. Как показано в этом примере, при поиске изображения для однозначной идентификации строки в предложении SELECT должен также задаваться первичный ключ таблицы.

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

Результирующий набор:

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

Такой же запрос выполняется с аргументом BINARY BASE64. Запрос возвращает двоичные данные в формате кодировки base64.

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

Результирующий набор:

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

По умолчанию при использовании режима AUTO для получения двоичных данных вместо самих двоичных данных возвращается ссылка на относительный URL-адрес виртуального корня базы данных, на которой выполняется запрос. Это происходит, если аргумент BINARY BASE64 не задается.

Если режим AUTO возвращает ссылку на URL-адрес двоичных данных в базах данных, в которых не учитывается регистр, запрос выполняется, даже если задаваемое в запросе имя таблицы или столбца не соответствует имени таблицы или столбца в базе данных. Однако значение регистра, возвращаемое в ссылке, не будет согласованным. Например:

SELECT PRODUCTPHOTOID, THUMBNAILPHOTO
FROM   Production.PRODUCTPHOTO 
WHERE PRODUCTPHOTOID=70
FOR XML AUTO

Результирующий набор:

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

Это может быть проблемой, особенно если запросы на объекты базы данных выполняются для базы данных с учетом регистра. Во избежание этого регистр задаваемого в запросах имени таблицы или столбца должен соответствовать регистру имени таблицы или столбца в базе данных.

Пример. Основные сведения о кодировке

В этом примере демонстрируется использование различных кодировок, которые могут применяться в результате.

Создайте такую таблицу:

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

Добавьте в таблицу следующие данные:

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

Следующий запрос возвращает данные из таблицы. Задается режим FOR XML AUTO. Двоичные данные возвращаются в виде ссылки.

SELECT * FROM [Special Chars] FOR XML AUTO

Результирующий набор:

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

Далее показан процесс кодирования специальных символов в результате.

В результате запроса специальные символы для XML и URL-адреса в возвращаемых именах элемента и атрибута кодируются при помощи шестнадцатеричного значения соответствующего символа Юникода. В предыдущем результате имя элемента <Special Chars> возвращается как <Special_x0020_Chars>. Имя атрибута <Col#&2> возвращается как <Col_x0023__x0026_2>. Кодируются специальные символы для XML, и URL-адресов.
Если значения элементов или атрибутов содержат какие-либо из пяти стандартных сущностей символов XML (', "", <, > и &), эти специальные символы XML всегда кодируются при помощи кодировки символов XML. В предыдущем результате значение & в значении атрибута <Col1> кодируется как &. Однако символ # остается #, так как это допустимый, а не специальный символ XML.
Если значения элементов или атрибутов содержат специальные символы для URL-адреса, имеющие особый смысл, они кодируются только в значении DBOBJECT URL, и только если специальный символ является частью имени таблицы или столбца. В результате символ #, являющийся частью имени таблицы Col#&2, в DBOJBECT URL кодируется как _x0023_.

См. также

Справочник

Использование режима AUTO

Поделиться через

Примеры. Использование режима AUTO

Пример. Извлечение данных о заказчике, заказе и подробных сведений о заказе

Пример. Использование предложения GROUP BY и статистических функций

Пример. Задание вычисляемых столбцов в режиме AUTO

Пример. Возврат двоичных данных

Пример. Основные сведения о кодировке

См. также

Справочник

Дополнительные ресурсы