Sql пример for xml path: Примеры: Использование режима PATH — SQL Server

T-SQL | Конкатенация строк

181

Работа с базами данных в .NET Framework — Оконные функции T-SQL — Конкатенация строк

Исходник базы данных

Как уже говорилось, в стандарте SQL определены только два типа функций упорядоченного набора: функции гипотетического набора (RANK, DENSE_RANK, PERCENT_RANK и CUME_DIST) и функции обратного распределения (PERCENTILE_DISC и PERCENTILE_CONT). Как я уже демонстрировал на примере функций сдвига, нет причины, по которой эта концепция не работала бы и для других функций. Основная идея состоит в том, что если это агрегирующая функция, результат вычислении которой зависит от порядка следования элементов, это возможный кандидат на функцию упорядоченного набора.

Возьмем такой классический пример как конкатенация строки. К сожалению, на настоящий момент не существует встроенной агрегирующей функции конкатенации строки, которая бы соединяла группу строк. Но допустим, что такая функция существует. Конечно же, у вас может возникнуть необходимость в конкатенации группы строк в некотором порядке, поэтому имеет смысл реализовать такую функцию как функцию упорядоченного набора с предложением WITHIN GROUP, которое позволяет задать параметры упорядочения.

В Oracle, например, такая функция реализована (называется LISTAGG) как функция упорядоченного набора. Итак, чтобы обратиться к таблице с именем Sales.Orders и вернуть для каждого клиента строку со значениями orderid конкатенированными в порядке orderid, используйте следующий код:

-- Для Oracle
SELECT custid,
  LISTAGG(orderid, ',') WITHIN GROUP(ORDER BY orderid) AS custorders
FROM Sales.Orders
GROUP BY custid;

В SQL Server разработчики прибегают к самым разным альтернативным решениям, чтобы получить конкатенацию строк в определенном порядке. Один из наиболее эффективных приемов основывается на обработке XML с использованием параметра FOR XML в режиме PATH, примерно так:

SELECT custid,
  COALESCE(
    STUFF(
      (SELECT ',' + CAST(orderid AS VARCHAR(10)) AS [text()]
       FROM Sales.Orders AS O
       WHERE O.custid = C.custid
       ORDER BY orderid
       FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),
      1, 1, ''),
    '') AS custorders
FROM Sales. Customers AS C;

Расположенный на самом нижнем уровне вложения связанный вложенный запрос отфильтровывает только значения orderid из таблицы Orders (псевдоним O), которые связаны с текущим клиентом из таблицы Customers (псевдоним C). Используя предложение FOR XML PATH, можно объединить все значения одну строку XML. Использование пустой строки как входного значения в режиме PATH означает, что инкапсулирующие элементы не нужны, поэтому мы получаем конкатенацию значений без всяких тегов. Так как вложенный запрос содержит ORDER BY orderid, значения orderid в строке будут упорядочены. Заметьте, что упорядочивать можно по любому признаку — не обязательно по значениям, которые конкатенируются. Приведенный код также добавляет запятую в качестве разделителя перед каждым значением orderid, а затем функция STUFF удаляет первую запятую. И наконец, функция COALESCE преобразует результат NULL в пустую строку. Итак, мы видим, что существует возможность получить в SQL Server конкатенацию строк в определенном порядке, но выглядит это не очень изящно.

Итак, функции упорядоченного набора, которые мы рассмотрели ранее, это агрегирующие функции, результат вычисления которых зависит от упорядочения. В стандарте определено несколько специализированных функций, но принцип является общим и может применяться ко всем видам вычислений агрегатов. Я привел несколько примеров, выходящих за пределы поддерживаемого стандарта, — это функции смещения и конкатенация строк. SQL Server 2012 не поддерживает функции упорядоченного набора данных, но я привел альтернативные методы для получения аналогичной функциональности. Я очень надеюсь, что в будущем мы увидим в SQL Server поддержку таких функций — возможно, они будут реализовывать стандартное предложение WITHIN GROUP и будут доступны через пользовательские CLR-функции агрегирования, учитывающие упорядочение.

Оператор STUFF

FOR XML в SQL Sever

Введение

В этой статье мы обсудим предложение FOR XML Path в SQL Server. Эта статья может быть полезна новичкам, специалистам среднего уровня или профессионалам.

Мы рассмотрим,

1. Пример использования
2. ДЛЯ XML
3. АВТОМАТИЧЕСКИЙ режим с FOR XML
4. Режим PATH с FOR XML
5. Разница между режимом AUTO и режимом PATH.
6. Директива Elements с FOR XML
7. Режим RAW с FOR XML
8. Режим EXPLICIT с FOR XML

Давайте начнем эту статью с варианта использования,

ПРИМЕР ИСПОЛЬЗОВАНИЯ

В современном мире XML играет жизненно важную роль в разработке, и мы часто получаем XML-данные в наших базах данных. Предложение FOR XML Path может помочь нам работать с XML-данными в SQL Server.

Предположим, у нас есть требование вернуть данные в формате XML из базы данных; мы должны использовать предложение FOR XML.

FOR XML в SQL Server

Предложение FOR XML можно использовать для преобразования существующих данных в формат XML. Он также может объединять или объединять несколько столбцов в одну строку.

Предложение FOR XML имеет ниже 4 режимов, которые определяют форму XML-результата.

• СЫРЬЕ
• АВТО
• ЯВНОЕ
• ПУТЬ

Синтаксис  

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

АВТОМАТИЧЕСКИЙ РЕЖИМ С FOR XML

FOR XML AUTO и FOR XML PATH — это самые простые способы преобразования данных SQL в XML. В приведенном ниже примере мы обсудим предложение FOR XML AUTO.

Мы создадим таблицу членов и вставим несколько строк, чтобы показать демонстрацию в этой статье.

 Член CREATE TABLE
(
  MemberId int identity(1,1) первичный ключ,
  Имя varchar(100),
  Фамилия varchar(100),
  Адрес varchar(100),
  Городской варчар(100),
  Состояние varchar(100),
  Номер телефона, целое число
)
 

Теперь вставьте несколько строк,

 INSERT INTO Member
ЗНАЧЕНИЯ («Киртеш», «Шах», «Тестовый адрес», «Вадодара», «Гуджарат», «1234567890»),
(«Раджеш», «Шах», «Адрес Сурат», «Сурат», «Гуджарат», «2134567894»),
(«Радж», «Патель», «TestAddress2», «Вадодара», «Гуджарат», «176347896»),
(«Мегха», «Шах», «SuratAddress3», «Куча», «Гуджарат», «457896321»),
(«Виушал», «Патель», «TestAddress4», «Бхудж», «Гуджарат», «325468796»),
(«Махеш», «Шах», «SuratAddress5», «Сура», «Гуджарат», «186347896»)
 

Сначала мы выполним приведенный ниже запрос SELECT и увидим результат,

 SELECT * FROM Member 

OUTPUT

Теперь мы обсудим пример FOR XML AUTO,

 SELECT * FROM Member FOR XML AUTO 

9005 1 ВЫВОД

Щелкнем по XML, показанному выше. ,

ВЫВОД XML

Режим PATH с FOR XML

Давайте воспользуемся тем же запросом с FOR XML PATH.

 SELECT * FROM Member FOR XML PATH 

ВЫВОД

XML ВЫВОД

 <строка>
  22
  Киртеш
  Шах
  
TestAddress
  Вадодара
  <Штат>Гуджарат
  <номер телефона>1234567890

<строка>
  23
  Раджеш
  Шах
  
SuratAddress
  Сурат
  <Штат>Гуджарат
  2134567894

<строка>
  24
  Радж
  Патель
  
TestAddress2
  Вадодара
  <Штат>Гуджарат
  <номер телефона>176347896

<строка>
  25
  Мега
  Шах
  
SuratAddress3
  Куча
  <Штат>Гуджарат
  457896321

<строка>
  26
  Виушал
  Патель
  
TestAddress4
  <Город>Бхудж
  <Штат>Гуджарат
  <Номер телефона>325468796

<строка>
  27
  Махеш
  Шах
  
SuratAddress5
  Сура
  <Штат>Гуджарат
  186347896

 

Разница между FOR XML и XML Path

Давайте сравним вывод FOR XML AUTO и FOR XML PATH,

Как показано на изображении выше,

• FOR XML AUTO — Структурированный XML, где каждый столбец является атрибутом.
• FOR XML PATH — Структурированный XML, где каждая запись является элементом, а столбец становится вложенным элементом.

Директива элементов с FOR XML

В приведенном выше запросе добавлено имя элемента, как показано ниже

 SELECT * FROM Member FOR XML PATH («Член») 

ВЫВОД

ВЫВОД XML

 <Член>
  22
  Киртеш
  Шах
  
TestAddress
  Вадодара
  <Штат>Гуджарат
  1234567890

<Член>
  23
  Раджеш
  Шах
  
SuratAddress
  Сурат
  <Штат>Гуджарат
  <Номер телефона>2134567894

<Член>
  24
  Радж
  Патель
  
TestAddress2
  Вадодара
  <Штат>Гуджарат
  176347896

<Член>
  25
  Мега
  Шах
  
SuratAddress3
  Куча
  <Штат>Гуджарат
  <номер телефона>457896321

<Член>
  26
  Виушал
  Патель
  
TestAddress4
  <Город>Бхудж
  <Штат>Гуджарат
  325468796

<Член>
  27
  Махеш
  Шах
  
SuratAddress5
  Сура
  <Штат>Гуджарат
  <номер телефона>186347896

 

Обратите внимание, что вместо отображается .

Режим RAW с FOR XML

Давайте посмотрим ниже запрос и вывод XML, чтобы лучше понять.

 SELECT * FROM Member FOR XML RAW 

ВЫВОД

XML ВЫВОД

ЯВНЫЙ РЕЖИМ с FOR XML

Предположим, вам нужны результаты XML в вашем формате; EXPLICIT – правильный выбор. Это даст вам гибкость для создания результата в вашем формате.

Синтаксис

 <ЭЛЕМЕНТ>!<ТЕГ>!<АТРИБУТ>[!<ДИРЕКТИВА>] 

Мы можем использовать приведенный выше синтаксис для генерации XML в желаемом формате.

Это все для этой статьи. Я надеюсь, что вы научитесь и найдете это полезным.

Создание списка, разделенного запятыми, с помощью FOR XML PATH и STUFF в SQL Server

Я не могу придумать историю или совет, который связан с созданием списка, разделенного запятыми, поэтому я просто использую тот, который совершенно не связан. Делиться опытом с близкими — это весело, и рыбалка — не исключение. Многие из моих недавних любимых рыболовных воспоминаний связаны с рыбалкой с моим четырехлетним сыном. Два лета назад, когда ему было два года, мы ловили морского окуня у пристани в хижине моего дедушки на севере Висконсина. Я цеплял их и позволял моему сыну ловить их маленькой сачком для бабочек, прежде чем отцепить их и отпустить. Я только что поймал на крючок еще одну и пытался подтянуть ее достаточно близко, чтобы мой сын мог поймать сеть, когда вдруг ворвалась крупная северная щука и полностью проглотила пойманного на крючок синежаберника. Увидев такую ​​большую рыбу, мой сын быстро отступил. Северная щука вообще не попалась на крючок, но все же дралась со мной где-то полминуты, прежде чем выплюнуть синежабру. Это было то, чего большинство людей не увидит, и было весело испытать это на себе с моим сыном, который только начал заниматься рыбалкой.

Если вам нужно создать список, разделенный запятыми, в SQL Server, лучший способ сделать это — использовать предложение FOR XML PATH. Это берет набор данных и помещает его в формат, который можно использовать в XML. Синтаксис может быть немного сложным, поэтому давайте рассмотрим его поближе. Сначала я создам временную таблицу и заполню ее некоторыми значениями — мы будем использовать мускусные приманки:

 CREATE TABLE #LureNames
(
ID INT IDENTITY(1, 1) ,
Приманка VARCHAR(25)
)
ВСТАВЬТЕ  В #LureNames
( Приманка )
ЦЕННОСТИ  ( "Убийца Меппса Маски"),
(«Гарассер»),
("Бухертейл 700"),
("Рифовый бегун"),
(«Крик Голавль Пайк Минноу»),
(«Бабушка»),
(«Джейк»),
(«Мелкий рейдер»)
("Бульдог"),
(«Сумасшедший триллер»),
(«Верующий»),
(«Плавательный свист»),
(«Двойная ковбойша»),
(«Медусса»),
("Двойник"),
("Мелкий захватчик"),
(«Лучший рейдер»)
(«Глубинный рейдер»)
(«Кардиостимулятор»),
(«Богатый воблер»),
(«Мягкий фантом») 

Если мы выполним простой выбор из этой таблицы, мы получим ожидаемое:

Если мы хотим получить список, разделенный запятыми, мы используем этот синтаксис:

 SELECT  STUFF(( SELECT  ', ' + Приманка
ОТ    ( ВЫБЕРИТЕ ОТЛИЧНЫЕ
Приманка
ОТ      #LureNames
) Икс
ДЛЯ
ПУТЬ XML('')
), 1, 2, '') AllLures
 

Чтобы понять, что это делает, давайте начнем изнутри и пройдем наружу. В самой внутренней части довольно простой SELECT DISTINCT:

 SELECT DISTINCT
Приманка
ОТ      #LureNames
 

Это просто даст нам имя каждой приманки один раз:
На следующем шаге мы добавим запятые и вставим предложение FOR XML PATH.

 ВЫБОР    ', ' + Приманка
ОТ    ( ВЫБЕРИТЕ ОТЛИЧНЫЕ
Приманка
ОТ      #LureNames
) Икс
ДЛЯ     XML-ПУТЬ
 

Вы можете видеть, что результаты теперь представлены в формате xml. Поскольку мы не указали имя столбца, записи называются просто . Теперь у нас есть основной список, разделенный запятыми, но там есть дополнительные символы. Прежде всего, это теги xml, и, кроме того, самая первая запись начинается с запятой и пробела. Чтобы избавиться от первой проблемы, мы просто изменим FOR XML PATH на FOR XML PATH(). Это говорит SQL Server, что нужно подавить теги строк.

 ВЫБОР    ', ' + Приманка
ОТ    ( ВЫБЕРИТЕ ОТЛИЧНЫЕ
Приманка
ОТ      #LureNames
) Икс
FOR     XML PATH('')
 

Теперь единственное, что нужно исправить, это ведущую запятую и пробел.