Директива TYPE в запросах FOR XML

Статья
06/25/2013

Начиная с версии SQL Server 2005, поддержка SQL Server для xml (Transact-SQL) позволяет при необходимости получать результат запроса FOR XML в виде типа данных xml путем указания директивы TYPE. Это позволяет обрабатывать результат запроса FOR XML на сервере. Например, к нему можно применить инструкции на языке XQuery, присвоить его результат переменной типа xml или написать вложенные запросы FOR XML.

Примечание
SQL Server возвращает клиенту тип данных XML экземпляра, представляющий собой результат различных серверных конструкций, таких как запросы FOR XML, использующие директиву TYPE или тип данных xml для получения значений XML-данных экземпляра из столбцов таблицы и выходных параметров SQL. В коде клиентского приложения поставщик ADO.NET запрашивает эти XML-данные для отправки с сервера в двоичной кодировке. Однако при использовании предложения FOR XML без директивы TYPE XML-данные возвращаются как данные строкового типа. Как бы то ни было, поставщик клиента имеет возможность обрабатывать XML-данные в любом из форматов. Обратите внимание на то, что предложение FOR XML верхнего уровня без директивы TYPE не может быть использовано с курсорами.

SQL Server возвращает клиенту тип данных XML экземпляра, представляющий собой результат различных серверных конструкций, таких как запросы FOR XML, использующие директиву TYPE или тип данных xml для получения значений XML-данных экземпляра из столбцов таблицы и выходных параметров SQL. В коде клиентского приложения поставщик ADO.NET запрашивает эти XML-данные для отправки с сервера в двоичной кодировке. Однако при использовании предложения FOR XML без директивы TYPE XML-данные возвращаются как данные строкового типа. Как бы то ни было, поставщик клиента имеет возможность обрабатывать XML-данные в любом из форматов. Обратите внимание на то, что предложение FOR XML верхнего уровня без директивы TYPE не может быть использовано с курсорами.

Примеры

В следующих примерах показано использование директивы TYPE в запросах FOR XML.

Получение результатов запроса FOR XML в виде XML-данных

Следующий запрос используется для получения контактной информации о клиенте из таблицы Contacts. Поскольку в запросе FOR XML указана директива TYPE, результат возвращается в виде xml-данных.

USE AdventureWorks2012;
Go
SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
FOR XML AUTO, TYPE;

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

<Person.Person BusinessEntityID="1" FirstName="Ken" LastName="Sánchez"/>

<Person.Person BusinessEntityID="2" FirstName="Terri" LastName="Duffy"/>

...

Назначение результатов запроса FOR XML переменной типа xml

В следующем примере результат инструкции FOR XML присваивается переменной @x типа xml. Запрос извлекает информацию о контактах, например BusinessEntityID, FirstName, LastName, а также дополнительные телефонные номера из столбца AdditionalContactInfoxmlTYPE. Поскольку в предложении FOR XML указана директива TYPE, результат возвращается в виде типа xml и присваивается переменной.

USE AdventureWorks2012;
GO
DECLARE @x xml;
SET @x = (
   SELECT BusinessEntityID, 
          FirstName, 
          LastName, 
          AdditionalContactInfo.query('
declare namespace aci="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
              //act:telephoneNumber/act:number') as MorePhoneNumbers
   FROM Person.Person
   FOR XML AUTO, TYPE);
SELECT @x;
GO

Запрос к результатам запроса FOR XML

Запросы FOR XML возвращают XML-данные. Таким образом, можно применить методы типа xml, например query() и value(), к XML-данным, возвращенным запросами FOR XML.

В следующем запросе метод query() для типа данных xml используется с целью запроса к результатам запроса FOR XML. Дополнительные сведения см. в разделе query() (тип данных xml).

USE AdventureWorks2012;
GO
SELECT (SELECT BusinessEntityID, FirstName, LastName, AdditionalContactInfo.query('
DECLARE namespace aci="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
DECLARE namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
 //act:telephoneNumber/act:number
') AS PhoneNumbers
FROM Person.Person
FOR XML AUTO, TYPE).query('/Person.Person[1]');

В следующем внутреннем запросе SELECT … FOR XML возвращается результат типа xml, к которому внешняя инструкция SELECT применяет метод query() к типу xml. Обратите внимание на указанную директиву TYPE.

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

<Person.Person BusinessEntityID="1" FirstName="Ken" LastName="Sánchez">

<act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">111-111-1111</act:number>

<act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">112-111-1111</act:number>

</PhoneNumbers>

</Person.Person>

В следующем запросе метод value() для типа данных xml используется с целью извлечения значения из результирующего XML-документа, возвращенного запросом SELECT…FOR XML. Дополнительные сведения см. в разделе Метод value() (тип данных xml).

USE AdventureWorks2012;
GO
DECLARE @FirstPhoneFromAdditionalContactInfo varchar(40);
SELECT @FirstPhoneFromAdditionalContactInfo = 
 ( SELECT BusinessEntityID, FirstName, LastName, AdditionalContactInfo.query('
declare namespace aci="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
   //act:telephoneNumber/act:number
   ') AS PhoneNumbers
   FROM Person.Person Contact
   FOR XML AUTO, TYPE).value('
declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
  /Contact[@BusinessEntityID="1"][1]/PhoneNumbers[1]/act:number[1]', 'varchar(40)'
 )
SELECT @FirstPhoneFromAdditionalContactInfo;

Выражение пути XQuery в методе value() извлекает из контактных сведений первый номер телефона заказчика с идентификатором BusinessEntityID, равным 1.

Примечание
Если директива TYPE не указана, результат запроса FOR XML возвращается в виде значения типа nvarchar(max).

Использование результатов запроса FOR XML в инструкциях INSERT, UPDATE и DELETE (Transact-SQL DML)

В следующем примере показано использование запросов FOR XML в инструкциях языка DML. В данном примере запрос FOR XML возвращает экземпляр типа xml. Инструкция INSERT вставляет этот экземпляр XML в таблицу.

CREATE TABLE T1(intCol int, XmlCol xml);
GO
INSERT INTO T1 
VALUES(1, '<Root><ProductDescription ProductModelID="1" /></Root>');
GO

CREATE TABLE T2(XmlCol xml)
GO
INSERT INTO T2(XmlCol) 
SELECT (SELECT XmlCol.query('/Root') 
        FROM T1 
        FOR XML AUTO,TYPE); 
GO

См. также

Справочник

FOR XML (SQL Server)

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