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

  • Статья

Применимо к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

Пример. Получение сведений о клиенте, заказе и заказе

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

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;

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

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

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

Пример. Указание функций GROUP BY и агрегатных функций

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

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;

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

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

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

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

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;

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

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

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

В Sales.Customer таблице хранятся отдельные сведения о клиенте, включая PersonID значение для этого клиента. Затем идентификатор PersonID используется для поиска контактного лица в таблице 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;

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

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

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

Запрос возвращает фотографию продукта из таблицы ProductPhoto . ThumbNailPhoto — столбец varbinary(max) в таблице ProductPhoto . По умолчанию режим AUTO возвращает ссылку на двоичные данные, являющуюся относительным URL-адресом виртуального корня базы данных, в которой выполняется запрос. Для идентификации изображения необходимо указать ключевой атрибут ProductPhotoID . При получении ссылки на изображение, как показано в этом примере, первичный ключ таблицы также должен быть указан в SELECT предложении для уникальной идентификации строки.

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

Результат:

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

Результат:

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

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

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

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

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

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

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

INSERT INTO [Special Chars] VALUES ('&', 0x20), ('#', 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="&"
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='&']/@Col_x0023__x0026_2"
/>

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

  • В результате запроса специальные символы для XML и URL-адреса в возвращаемых именах элемента и атрибута кодируются при помощи шестнадцатеричного значения соответствующего символа Юникода. В предыдущем результате имя элемента <Special Chars> возвращается как <Special_x0020_Chars>. Имя <Col#&2> атрибута возвращается как <Col_x0023__x0026_2>. Кодируются специальные символы для XML, и URL-адресов.

  • Если значения элементов или атрибутов содержат какие-либо из пяти стандартных сущностей СИМВОЛОВ XML (', "", <, >и &), эти специальные XML-символы всегда кодируются с помощью кодировки XML-символов. В предыдущем результате значение & в значении атрибута <Col1> закодировано как &. Однако символ #остается #, так как он является допустимым XML-символом, а не специальным XML-символом.

  • Если значения элементов или атрибутов содержат специальные символы URL-адреса, имеющие особое значение в URL-адресе, они кодируются только в значении URL-адреса DBOBJECT и кодируются только в том случае, если специальный символ является частью имени таблицы или столбца. В результате символ # , являющийся частью имени таблицы Col#&2 , кодируется как _x0023_ in the DBOJBECT URL.

См. также