Использование режима AUTO
Как описано в подразделе Извлечение документов XML при помощи предложения FOR XML, режим AUTO возвращает результаты запросов в виде вложенных XML-элементов. Такой механизм не обеспечивает достаточное управление структурой XML, формируемой из результатов запроса. Запросы в режиме AUTO полезны, если необходимо формировать простые иерархии. Однако использование режима EXPLICIT и использование режима PATH обеспечивают большую управляемость и гибкость при определении структуры XML по результатам запроса.
Каждая таблица в предложении FROM, из которой по крайней мере один столбец присутствует в предложении SELECT, представляется как элемент XML. Столбцы, перечисляемые в предложении SELECT, сопоставляются атрибутам или подчиненным элементам, если в предложении FOR XML указан необязательный аргумент ELEMENTS.
XML-иерархия (порядок вложенности элементов) в результирующих XML-данных основана на порядке таблиц, определяемых столбцами, которые указаны в предложении SELECT. Поэтому важен порядок, в котором в предложении SELECT указываются имена столбцов. Первая, самая левая идентифицируемая таблица образует верхний элемент в результирующем XML-документе. Вторая слева таблица, идентифицируемая столбцами в инструкции SELECT, образует элемент, подчиненный верхнему элементу и т.д.
Если столбец, имя которого указывается в предложении SELECT, принадлежит таблице, уже идентифицируемой столбцом, заданным до этого в предложении SELECT, вместо открытия нового уровня иерархии этот столбец добавляется как атрибут уже созданного элемента. Если указан аргумент ELEMENTS, столбец добавляется как атрибут.
В качестве примера рассмотрим выполнение следующего запроса:
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,
Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO
Промежуточный результат:
<Cust CustomerID="1" CustomerType="S">
<OrderHeader CustomerID="1" SalesOrderID="43860" Status="5" />
<OrderHeader CustomerID="1" SalesOrderID="44501" Status="5" />
<OrderHeader CustomerID="1" SalesOrderID="45283" Status="5" />
<OrderHeader CustomerID="1" SalesOrderID="46042" Status="5" />
</Cust>
...
В предложении SELECT следует отметить следующее.
- Идентификатор CustomerID ссылается на таблицу «Cust». Поэтому создается элемент <
Cust>, а CustomerID добавляется как его атрибут. - Далее три столбца, OrderHeader.CustomerID, OrderHeader.SaleOrderID и OrderHeader.Status ссылаются на таблицу OrderHeader. Поэтому элемент <
OrderHeader> добавляется как подчиненный элемент элемента <Cust>, и три столбца добавляются как атрибуты <OrderHeader>. - Далее, столбец Cust.CustomerType вновь ссылается на таблицу «Cust», которая уже была идентифицирована столбцом Cust.CustomerID. Поэтому никакого нового элемента не создается. Вместо этого атрибут CustomerType добавляется к созданному до этого элементу <
Cust>. - Запрос задает псевдонимы для имен таблиц. Эти псевдонимы представляются как имена соответствующих элементов.
- Для группирования всех дочерних элементов одного родителя необходимо предложение ORDER BY.
Следующий запрос аналогичен предыдущему, за исключением того, что предложение SELECT задает столбцы в таблице OrderHeader перед столбцами в таблице «Cust». Поэтому первым создается элемент <OrderHeader>, а к нему добавляется дочерний элемент <Cust>.
select OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,
Cust.CustomerID,
Cust.CustomerType
from Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
where Cust.CustomerID = OrderHeader.CustomerID
for xml auto
Промежуточный результат:
<OrderHeader CustomerID="1" SalesOrderID="43860" Status="5">
<Cust CustomerID="1" CustomerType="S" />
</OrderHeader>
...
Если в предложение FOR XML добавляется аргумент ELEMENTS, возвращается XML с использованием элементов.
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,
Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO, ELEMENTS
Промежуточный результат:
<Cust>
<CustomerID>1</CustomerID>
<CustomerType>S</CustomerType>
<OrderHeader>
<CustomerID>1</CustomerID>
<SalesOrderID>43860</SalesOrderID>
<Status>5</Status>
</OrderHeader>
...
</Cust>
...
В этом запросе при создании элементов <Cust> значение идентификатора CustomerID из одной строки сравнивается со значением этого идентификатора из следующей строки, так как CustomerID является первичным ключом таблицы. Если CustomerID не идентифицирован для таблицы как первичный ключ, во всех столбцах (CustomerID и CustomerType в этом запросе) проводится сравнение каждой строки со следующей строкой. Если значения строк различаются, в XML добавляется новый элемент <Cust>.
Если сравниваемые столбцы имеют тип данных text, ntext, image или xml, при сравнении значений соответствующих строк использование FOR XML предполагает, что значения различаются, поэтому они не сравниваются, даже если могут быть одинаковыми. Причиной этого является то, что сравнение больших объектов не поддерживается. Элементы добавляются в результат для каждой выбранной строки. Обратите внимание на то, что столбцы типа (n)varchar(max) и varbinary(max) сравниваются.
Если столбец в предложении SELECT не может быть сопоставлен ни с одной таблицей, идентифицируемой в предложении FROM, как в случае столбца со статистическими выражениями или вычисляемого столбца, столбец добавляется в XML-документ на самый глубокий уровень вложенности, если он присутствует в списке. Если такой столбец является первым столбцом в предложении SELECT, столбец добавляется к верхнему элементу.
Если в предложении SELECT задается символ-шаблон «*», вложенность определяется таким же образом, как это описано выше, на основании порядка, в котором строки возвращаются механизмом запроса.
Если в запросе задан аргумент BINARY BASE64, двоичные данные возвращаются в формате кодирования base64. По умолчанию, если аргумент BINARY BASE64 не задан, режим AUTO поддерживает кодирование URL-адресов двоичных данных. Это означает, что вместо двоичных данных возвращается ссылка на относительный URL-адрес виртуального корня базы данных, в которой выполнялся запрос. Эта ссылка может использоваться для доступа к действительным двоичным данным в последующих операциях при помощи запроса объекта базы данных SQLXML ISAPI. Для идентификации изображения запрос должен предоставить достаточно данных, например столбцы первичного ключа.
При задании запроса, если используется псевдоним для двоичного столбца представления, псевдоним возвращается в виде URL-адреса двоичных данных. В последующих операциях псевдоним не имеет смысла, и URL-адрес не может использоваться для получения изображения. Поэтому не используйте псевдонимы при запросе представления в режиме FOR XML AUTO.
Основные сведения об эвристиках режима AUTO при оформлении возвращаемых XML
Режим AUTO определяет основанную на запросе структуру возвращаемого XML. При определении вложения элементов эвристические процедуры, применяемые в режиме AUTO, сравнивают значения столбцов в соседних строках. Сравниваются столбцы всех типов, за исключением ntext, text, image и xml. Проводится также сравнение столбцов (n)varchar(max) и varbinary(max).
В следующем примере иллюстрируются эвристики режима AUTO, определяющие структуру результирующего XML:
SELECT T1.Id, T2.Id, T1.Name
FROM T1, T2
WHERE ...
FOR XML AUTO
ORDER BY T1.Id
Для определения начала нового элемента <T1> сравниваются все значения столбцов таблицы T1, за исключением столбцов типов данных ntext, text, image и xml, если ключ таблицы Т1 не задан. Далее предположим, что столбец Name имеет тип данных nvarchar(40) и инструкция SELECT возвращает следующий набор строк:
T1.Id T1.Name T2.Id
-----------------------
1 Andrew 2
1 Andrew 3
1 Nancy 4
Эвристики режима AUTO сравнивают все значения столбцов Id и Name таблицы Т1. Так как первые две строки имеют одинаковые значения столбцов Id и Name, в результат добавляется один элемент <T1>, имеющий два дочерних элемента <T2>.
Далее показан возвращенный XML:
<T1 Id="1" Name="Andrew">
<T2 Id="2" />
<T2 Id="3" />
</T1>
<T1 Id="1" Name="Nancy" >
<T2 Id="4" />
</T>
Теперь предположим, что столбец Name имеет тип данных text. Эвристики режима AUTO не выполняют сравнения для этого типа. Вместо этого в них предполагается, что значения различны. Это приводит к формированию приведенного ниже XML:
<T1 Id="1" Name="Andrew" >
<T2 Id="2" />
</T1>
<T1 Id="1" Name="Andrew" >
<T2 Id="3" />
</T1>
<T1 Id="1" Name="Nancy" >
<T2 Id="4" />
</T1>
Примеры
В следующем примере иллюстрируется применение режима 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="&"
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. - Если значения элементов или атрибутов содержат специальные символы для URL-адреса, имеющие особый смысл, они кодируются только в значении DBOBJECT URL, и только если специальный символ является частью имени таблицы или столбца. В результате символ #, являющийся частью имени таблицы Col#&2, в DBOJBECT URL кодируется как _x0023_.
См. также
Справочник
Создание XML с помощью предложения FOR XML