Sql for xml: FOR XML (SQL Server) — SQL Server

FOR XML (SQL Server) — SQL Server

Twitter




LinkedIn




Facebook




Адрес электронной почты

• 
  Статья
• 
  03/03/2023
• 
  

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

Запрос SELECT возвращает результаты в виде набора строк. При необходимости можно получать результаты SQL-запроса в формате XML. Для этого в запросе необходимо указать предложение FOR XML. Предложение FOR XML может использоваться в запросах верхнего уровня и во вложенных запросах. Предложение FOR XML верхнего уровня можно использовать только в инструкции SELECT. Во вложенных запросах предложение FOR XML можно использовать в инструкциях INSERT, UPDATE и DELETE. FOR XML также можно использовать в инструкциях присваивания.

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

• RAW
• AUTO
• EXPLICIT
• PATH

Режим RAW создает один <элемент строки> для каждой строки в наборе строк, возвращаемом инструкцией SELECT. XML-иерархию можно создать с помощью написания вложенных запросов FOR XML.

В режиме AUTO вложенность XML создается эвристически, в зависимости от метода определения инструкции SELECT. Управление формой создаваемой XML структуры минимально. Для создания XML-иерархии, расширяющей возможности XML-структуры, созданной эвристически в режиме AUTO, можно написать вложенные запросы FOR XML.

Режим EXPLICIT предоставляет больше возможностей по управлению формой XML-структуры. В XML-структуре могут быть использованы смешанные атрибуты и элементы. Это требует особого формата для результирующего набора строк, создаваемого в результате выполнения запроса. Формат этого набора строк затем сопоставляется с формой XML-структуры. Преимущества режима EXPLICIT состоят в возможности использования смешанных атрибутов и элементов, в возможности создания упаковщиков и вложенных составных свойств, создания значений, разделенных пробелами (например, атрибут OrderID может содержать список значений идентификаторов порядка), и смешанного содержимого.

Однако написание запросов в режиме EXPLICIT может быть очень обременительным. Можно использовать некоторые новые возможности предложения FOR XML, например написание вложенных запросов FOR XML в режиме RAW/AUTO/PATH и директивы TYPE вместо использования режима EXPLICIT для создания иерархий. Вложенные запросы FOR XML могут создавать любую XML структуру, которую можно создать с помощью режима EXPLICIT. Дополнительные сведения см. в статьях Использование вложенных запросов FOR XML и Директива TYPE в запросах FOR XML.

Режим PATH совместно с вложенным запросом FOR XML обеспечивает гибкость режима EXPLICIT более простым образом.

Эти режимы на самом деле работают только при выполнении запросов, для которых они установлены. Они не влияют на результаты любых последующих запросов.

Предложение FOR XML недопустимо для выборки с использованием предложений FOR BROWSE.

Пример

Следующая инструкция SELECT получает данные из таблиц Sales.Customer и Sales.SalesOrderHeader базы данных AdventureWorks2012 . В этом запросе задается режим AUTO в предложении FOR XML :

USE AdventureWorks2012
GO
SELECT Cust.CustomerID,
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID,
       OrderHeader.Status
FROM Sales.Customer Cust 
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON Cust.CustomerID = OrderHeader. CustomerID
FOR XML AUTO;

Предложение FOR XML и имена сервера

Если в инструкции SELECT, использующей предложение FOR XML, указано четырехкомпонентное имя, в возвращаемом документе имя сервера отсутствует, если запрос выполняется на локальном компьютере. Однако имя сервера возвращается как часть имени, если запрос выполняется на сетевом сервере.

В качестве примера рассмотрим запрос:

SELECT TOP 1 LastName
  FROM ServerName.AdventureWorks2012.Person.Person
  FOR XML AUTO;

Локальный сервер. Если ServerName является локальным сервером, запрос возвращает следующий текст:

<AdventureWorks2012.Person.Person LastName="Achong" />  

Сетевой сервер. Если ServerName является сетевым сервером, запрос возвращает следующий текст:

<ServerName.AdventureWorks2012.Person.Person LastName="Achong" />

Избегайте неоднозначности. Эту потенциальную неоднозначность можно избежать, указав следующий псевдоним:

SELECT TOP 1 LastName
  FROM ServerName.AdventureWorks2012.Person.Person x
  FOR XML AUTO;

Теперь запрос, для которого исключена неоднозначность, возвращает следующий текст:

<x LastName="Achong"/>

См. также раздел

Базовый синтаксис предложения FOR XML
Использование RAW Mode с FOR XML
Использование AUTO Mode с FOR XML
Использование режима EXPLICIT с предложением FOR XML
Использование режима PATH совместно с FOR XML
Инструкция OPENXML (SQL Server)
Добавление пространств имен в запросы с WITH XMLNAMESPACES

массивы — «array» в ms sql (for xml)

Всем здравствуйте!
Начал познавать язык запросов ms-sql и в качестве поставленного себе задания пытаюсь передать на сторону xml, содержащую определенные данные.

Проблема возникла в том, что возможности вывода одного бандла данных — ограниченны строкой (в моём случае), в результате я не могу получить необходимый мне результат просто использовав Join на паре таблиц. . Возможно мне поможет подзапрос с for xml в одно поле для того чтобы сформировать своеобразный «массив» в рамках одного поля для вывода красивой общей картинки в результате for xml — возможно, стоит собирать данные отдельно блоками в поля или есть неизвестный мне способ собрать так как я бы хотел — решил спросить у знатоков.

Ниже привожу входные данные, результат и желаемый результат. Спасибо :/

create table #pers (id_pers int , N varchar (20), S varchar (55), comment varchar (max))
create table #persinfo (id_persinfo int, id_pers int, number varchar(20))
insert into #pers values (1,'Alex','Ground','StackOvfw'),(2,'Dmitry','Terra','StackOvfw'),(3,'Dmitry','Land','StackOvfw'),(4,'Maximus','Earth','StackOvfw'),(5,'Daniel','Soil','StackOvfw')
insert into #persinfo values (1,1,'8-800-555-35-35'),(2,1,'8-801-555-35-35'), (3,2,'8-999-999-99-99')
select 
p.id_pers as [Person/URN_ID],
p.N as [Person/Name],
p.S as [Person/Surname],
pinfo.id_persinfo as [Person/Person_Infos/URN_ID],
pinfo. id_persinfo as [Person_INFOs/Person_info/URN_ID],
pinfo.number as [Person_INFOs/Person_info/TNumber]
from #pers p 
join #persinfo pinfo on p.id_pers = pinfo.id_pers
where p.id_pers = 1
for xml path('Bundle'), root ('Persons') 

Как результат получаю 2 позиции (т.к. столько отдаёт селект, но хотелось бы получать 1)

Результат:

<Persons>
  <Bundle>
    <Person>
      <URN_ID>1</URN_ID>
      <Name>Alex</Name>
      <Surname>Ground</Surname>
      <Person_Infos>
        <URN_ID>1</URN_ID>
      </Person_Infos>
    </Person>
    <Person_INFOs>
      <Person_info>
        <URN_ID>1</URN_ID>
        <TNumber>8-800-555-35-35</TNumber>
      </Person_info>
    </Person_INFOs>
  </Bundle>
  <Bundle>
    <Person>
      <URN_ID>1</URN_ID>
      <Name>Alex</Name>
      <Surname>Ground</Surname>
      <Person_Infos>
        <URN_ID>2</URN_ID>
      </Person_Infos>
    </Person>
    <Person_INFOs>
      <Person_info>
        <URN_ID>2</URN_ID>
        <TNumber>8-801-555-35-35</TNumber>
      </Person_info>
    </Person_INFOs>
  </Bundle>
</Persons>

Желаемый же результат таков:

<Persons>
  <Bundle>
    <Person>
      <URN_ID>1</URN_ID>
      <Name>Alex</Name>
      <Surname>Ground</Surname>
      <Person_Infos>
        <URN_ID>1</URN_ID>
        <URN_ID>2</URN_ID>
      </Person_Infos>
    </Person>
    <Person_INFOs>
      <Person_info>
        <URN_ID>1</URN_ID>
        <TNumber>8-800-555-35-35</TNumber>
      </Person_info>
      <Person_info>
        <URN_ID>2</URN_ID>
        <TNumber>8-801-555-35-35</TNumber>
      </Person_info>
    </Person_INFOs>
  </Bundle>
</Persons>

ДЛЯ XML (SQL Server) — SQL Server

Редактировать

Твиттер

LinkedIn

Фейсбук

Электронная почта

• Статья
• 03. 03.2023

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

Запрос SELECT возвращает результаты в виде набора строк. При желании вы можете получить формальные результаты SQL-запроса в виде XML, указав в запросе предложение FOR XML. Предложение FOR XML можно использовать в запросах верхнего уровня и в подзапросах. Предложение FOR XML верхнего уровня можно использовать только в операторе SELECT. В подзапросах FOR XML можно использовать в операторах INSERT, UPDATE и DELETE. FOR XML также можно использовать в операторах присваивания.

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

• RAW
• АВТО
• ЯВНОЕ
• ПУТЬ

В режиме RAW создается один элемент для каждой строки в наборе строк, который возвращается оператором SELECT. Вы можете создать иерархию XML, написав вложенные запросы FOR XML.

В режиме AUTO в результирующем XML создается вложенность с использованием эвристики, основанной на способе указания оператора SELECT. У вас есть минимальный контроль над формой сгенерированного XML. Вложенные запросы FOR XML могут быть написаны для создания иерархии XML за пределами формы XML, созданной эвристикой режима AUTO.

Режим EXPLICIT позволяет больше контролировать форму XML. Вы можете по желанию смешивать атрибуты и элементы при выборе формы XML. Для результирующего набора строк, созданного в результате выполнения запроса, требуется определенный формат. Затем этот формат набора строк сопоставляется с формой XML. Сила режима EXPLICIT заключается в произвольном смешивании атрибутов и элементов, создании оболочек и вложенных сложных свойств, создании значений, разделенных пробелами (например, атрибут OrderID может иметь список значений идентификаторов заказов) и смешанном содержимом.

Однако написание запросов в режиме EXPLICIT может быть громоздким. Вы можете использовать некоторые новые возможности FOR XML, такие как написание вложенных запросов FOR XML в режимах RAW/AUTO/PATH и директиву TYPE, вместо использования режима EXPLICIT для создания иерархий. Вложенные запросы FOR XML могут создавать любой XML-код, который можно сгенерировать с помощью режима EXPLICIT. Дополнительные сведения см. в разделах Использование вложенных запросов FOR XML и директивы TYPE в запросах FOR XML.

Режим PATH вместе с возможностью вложенных запросов FOR XML обеспечивает гибкость режима EXPLICIT более простым способом.

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

FOR XML недействителен для любого выбора, который используется с предложением FOR BROWSE.

Пример

Следующая инструкция SELECT извлекает информацию из таблиц Sales.Customer и Sales.SalesOrderHeader в базе данных AdventureWorks2012 . Этот запрос указывает Режим AUTO в пункте FOR XML :

 USE AdventureWorks2012
ИДТИ
ВЫБЕРИТЕ Custom.CustomerID,
       OrderHeader.CustomerID,
       OrderHeader. SalesOrderID,
       Заголовок заказа.Статус
ОТ Sales.Customer Custom
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Sales.SalesOrderHeader
ON Custom.CustomerID = OrderHeader.CustomerID
ДЛЯ XML АВТО;
 

Предложение FOR XML и имена серверов

Если инструкция SELECT с предложением FOR XML указывает в запросе имя, состоящее из четырех частей, имя сервера не возвращается в результирующем XML-документе при выполнении запроса на локальном компьютере. . Однако имя сервера возвращается в виде имени из четырех частей, когда запрос выполняется на сетевом сервере.

Например, рассмотрим такой запрос:

 SELECT TOP 1 LastName
  ОТ ServerName.AdventureWorks2012.Person.Person
  ДЛЯ XML АВТО;
 

Локальный сервер :   Если ServerName является локальным сервером, запрос возвращает следующий текст:

Сетевой сервер :   Если ServerName является сетевым сервером, запрос возвращает следующий текст:

  AdventureWorks2012.Person.Person LastName="Achong" />
 

Избегайте двусмысленности :   Этой потенциальной неоднозначности можно избежать, указав этот псевдоним:

 SELECT TOP 1 LastName
  ОТ ServerName.AdventureWorks2012.Person.Person x
  ДЛЯ XML АВТО;
 

Теперь запрос с устранением неоднозначности возвращает следующий текст:

См. также

Базовый синтаксис пункта FOR XML
Использовать режим RAW с FOR XML
Использовать режим AUTO с FOR XML
Использовать режим EXPLICIT с FOR XML
Использовать режим PATH с FOR XML
OPENXML (SQL Server)
Добавить пространства имен в запросы с помощью WITH XMLNAMESPACES

Обратная связь

Просмотреть все отзывы о странице

Базовый синтаксис предложения FOR XML — SQL Server

Редактировать

Твиттер

LinkedIn

Фейсбук

Электронная почта

• Статья
• 03. 03.2023

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

Режим FOR XML может быть RAW, AUTO, EXPLICIT или PATH. Он определяет форму результирующего XML.

Важно

Директива XMLDATA для параметра FOR XML устарела . Используйте генерацию XSD в случае режимов RAW и AUTO. Нет замены директиве XMLDATA в режиме EXPLICT. Эта функция будет удалена в будущей версии Microsoft SQL Server. Избегайте использования этой функции в новых разработках и планируйте модифицировать приложения, которые в настоящее время используют эту функцию.

Синтаксис

Ниже приведен основной синтаксис, описанный в предложении FOR (Transact-SQL).

 [ДЛЯ {ПРОСМОТРА |  } ]
 ::=
XML
    {
      { RAW [('ИмяЭлемента') ] | АВТО }
        [
           <Общие директивы>
           [ , {XMLDATA | XMLSCHEMA [('TargetNameSpaceURI') ]} ]
           [ , ЭЛЕМЕНТЫ [ XSINIL | ОТСУТСТВУЮЩИЙ ]
        ]
      | ЯВНЫЙ
        [
           <Общие директивы>
           [ , XMLДАННЫЕ ]
        ]
      | ПУТЬ [('ИмяЭлемента')]
        [
           <Общие директивы>
           [ , ЭЛЕМЕНТЫ [ XSINIL | ОТСУТСТВУЮЩИЙ ] ]
        ]
     }
<Общие директивы> ::=
   [ , ДВОИЧНЫЙ BASE64 ]
   [ , ТИП ]
   [ , КОРЕНЬ [ ('Корневое имя') ] ]
 

Синтаксис для базы данных SQL Azure

Документацию по предложению SELECT. .. FOR XML , которое также применяется к базе данных SQL Azure, см. в разделе FOR XML (SQL Server).

Аргументы

RAW [(‘

ElementName ‘)]

Берет результат запроса и преобразует каждую строку в результирующем наборе в элемент XML с общим идентификатором в качестве тега элемента . При использовании этой директивы вы можете дополнительно указать имя для элемента строки. Результирующий XML будет использовать указанные ElementName как элемент строки, сгенерированный для каждой строки. Дополнительные сведения см. в разделе Использование режима RAW с FOR XML.

AUTO

Возвращает результаты запроса в виде простого вложенного XML-дерева. Каждая таблица в предложении FROM, для которой хотя бы один столбец указан в предложении SELECT, представлена ​​как элемент XML. Столбцы, перечисленные в предложении SELECT, сопоставляются с соответствующими атрибутами элемента. Дополнительные сведения см. в разделе Использование режима AUTO с FOR XML.

ЯВНОЕ

Указывает, что форма результирующего XML-дерева определяется явно. При использовании этого режима запросы должны быть написаны особым образом, чтобы дополнительная информация о желаемой вложенности указывалась явно. Дополнительные сведения см. в разделе Использование режима EXPLICIT с FOR XML.

PATH

Обеспечивает более простой способ смешивания элементов и атрибутов, а также введение дополнительных вложений для представления сложных свойств. Вы можете использовать запросы в режиме FOR XML EXPLICIT для создания такого типа XML из набора строк, но режим PATH представляет собой более простую альтернативу, возможно, громоздким запросам в режиме EXPLICIT. Режим PATH вместе с возможностью написания вложенных запросов FOR XML и директивой TYPE для возврата 9Экземпляры типа 0017 xml позволяют писать менее сложные запросы. Это альтернатива написанию большинства запросов в режиме EXPLICIT. По умолчанию режим PATH создает оболочку элемента для каждой строки в результирующем наборе. При желании вы можете указать имя элемента. Если вы это сделаете, указанное имя будет использоваться в качестве имени элемента-оболочки. Если указать пустую строку (FOR XML PATH ('')) , элемент оболочки не будет создан. Дополнительные сведения см. в разделе Использование режима PATH с FOR XML.

MLDATA

Указывает, что должна быть возвращена встроенная схема XML-Data Reduced (XDR). Схема добавляется к документу как встроенная схема. Рабочий пример см. в разделе Использование режима RAW с FOR XML.

XMLSCHEMA

Возвращает встроенную XML-схему W3C (XSD). Вы можете дополнительно указать URI целевого пространства имен при указании этой директивы. Это возвращает указанное пространство имен в схеме. Дополнительные сведения см. в разделе Создание встроенной схемы XSD. Рабочий пример см. в разделе Использование режима RAW с FOR XML.

ELEMENTS

Если указана опция ELEMENTS, столбцы возвращаются как подэлементы. В противном случае они сопоставляются с атрибутами XML. Этот параметр поддерживается только в режимах RAW, AUTO и PATH. Вы можете дополнительно указать XSINIL или ABSENT при использовании этой директивы. XSINIL указывает, что элемент, у которого атрибут xsi:nil имеет значение True, должен быть создан для значений столбца NULL. По умолчанию или когда вместе с ELEMENTS указывается ABSENT, элементы для значений NULL не создаются. Рабочий пример см. в разделах Использование режима RAW с FOR XML и Использование режима AUTO с FOR XML.

BINARY BASE64

Если указан параметр BINARY Base64, любые двоичные данные, возвращаемые запросом, представляются в формате с кодировкой base64. Для извлечения двоичных данных с использованием режима RAW и EXPLICIT необходимо указать эту опцию. В режиме AUTO двоичные данные по умолчанию возвращаются в качестве ссылки. Рабочий пример см. в разделе Использование режима RAW с FOR XML.

TYPE

Указывает, что запрос возвращает результаты в формате xml .