SQL - Как извлечь несколько атрибутов из поля XML в таблице

ProgramBox

SQL — Как извлечь несколько атрибутов из поля XML в таблице

Post author:admin
Запись опубликована:6 апреля, 2023
Post category:Вопросы по программированию

#sql #sql-server #xml #tsql #pivot

#sql #sql-сервер #xml #tsql #сводная

Вопрос:

Я пытаюсь выполнить SQL-запрос к таблице, в которой есть одно поле, содержащее XML-данные, но этот XML содержит несколько значений, которые необходимо преобразовать в одно поле. Пожалуйста, обратите внимание, что поле представляет собой содержимое XML, но фактический тип поля установлен в nvarchar (max), а не в xml.

Редактировать: Версия — SQL Server 2014 Express Edition

У меня есть таблица, подобная этой: [Список клиентов с рынками]

И я хочу извлечь значения ‘marketCode’ в той же строке (через запятую):

 |CompanyCode|CompanyName|MarketCode,MarketCode,MarketCode,etc.|Phone|

Ожидаемый результат из примера (см. Скриншот):

 |ABC123|JOHN DEERE|AA,BB,CC,DD|555-123-000|
|DEF456|NEW HOLLLAND|AA,FF,GG,HH,KK|555-456-0000|

1. Поддержка XML сильно зависит от поставщика , поэтому, пожалуйста, добавьте тег, чтобы указать, используете ли вы mysql , postgresql , sql-server oracle или db2 — или что-то совсем другое.

2. Пожалуйста, покажите нам, что вы пробовали…

Ответ №1:

Пример данных

Пожалуйста, в следующий раз предоставьте текст вместо изображений;-)

Столбец Markets определяется как nvarchar(max) . Данные вставляются в формате Unicode (с N префиксом).

 create table Company2
(
    Code nvarchar(6),
    Name nvarchar(11),
    Markets nvarchar(max),
    Phone nvarchar(12)
);

insert into Company2 (Code, Name, Markets, Phone) values
(N'ABC123',
 N'JOHN DEERE',
 N'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <license>
    <company companyCode="ABC123">
      <markets>
        <market marketCode="AA"/>
        <market marketCode="BB"/>
        <market marketCode="CC"/>
        <market marketCode="DD"/>
      </markets>
    </company>
  </license>',
 N'555-123-0000'),
(N'DEF456',
 N'NEW HOLLAND',
 N'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <license>
    <company companyCode="DEF456">
      <markets>
        <market marketCode="AA"/>
        <market marketCode="FF"/>
        <market marketCode="GG"/>
        <market marketCode="HH"/>
        <market marketCode="KK"/>
      </markets>
    </company>
  </license>',
 N'555-456-0000');

Решение

Приведение Markets непосредственно к XML невозможно, поскольку nvarchar(max) кодировка конфликтует с «utf-8» в данных. Я перенес преобразование в отдельное общее табличное выражение (CTE, cte_convert ), перейдя от nvarchar(max) к varchar(max) XML .

Теперь следующий CTE ( cte_parse ) можно использовать c.MarketsXML.nodes() для извлечения <market> узлов из XML в новый столбец m.Market . Из этого столбца @marketCode атрибут извлекается как желаемое значение.

Затем для объединения значений используется подзапрос с for xml path('') .

 with cte_convert as
(
    select c.Code, c.Name, convert(XML, convert(varchar(max), c.Markets)) as MarketsXML, c.Phone
    from Company2 c
),
cte_parse as
(
    select c.Code, c.Name, m.Market.value('@marketCode', 'nvarchar(10)') as MarketCode, c.Phone
    from cte_convert c
    outer apply c.MarketsXML.nodes('/license/company/markets/market') as m(Market)
)
select  cp.Code,
        cp.Name,
        stuff(( select ','   cp2.MarketCode as MC
                from cte_parse cp2
                where cp2.Code = cp.Code
                for xml path(''), type).value('.', 'nvarchar(max)'),1,1,'') as MarketCodes,
        cp.Phone
from cte_parse cp
group by cp.Code, cp.Name, cp.Phone;

Результат

 Code   Name        MarketCodes    Phone
------ ----------- -------------- ------------
ABC123 JOHN DEERE  AA,BB,CC,DD    555-123-0000
DEF456 NEW HOLLAND AA,FF,GG,HH,KK 555-456-0000

Это оригинальное решение использует функцию string_agg(), которая доступна начиная с SQL Server 2017.

Пример данных

Примечание: столбец Markets определяется как XML для отражения его содержимого.

 create table Company
(
    Code nvarchar(6),
    Name nvarchar(11),
    Markets XML,
    Phone nvarchar(12)
);

insert into Company (Code, Name, Markets, Phone) values
('ABC123',
 'JOHN DEERE',
 '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <license>
    <company companyCode="ABC123">
      <markets>
        <market marketCode="AA"/>
        <market marketCode="BB"/>
        <market marketCode="CC"/>
        <market marketCode="DD"/>
      </markets>
    </company>
  </license>',
  '555-123-0000'),
('DEF456',
 'NEW HOLLAND',
 '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <license>
    <company companyCode="DEF456">
      <markets>
        <market marketCode="AA"/>
        <market marketCode="FF"/>
        <market marketCode="GG"/>
        <market marketCode="HH"/>
        <market marketCode="KK"/>
      </markets>
    </company>
  </license>',
  '555-456-0000');

Решение

 with cte_parse as
(
    select c.Code, c.Name, m.Market.value('@marketCode', 'nvarchar(10)') as MarketCode, c.Phone
    from Company c
    outer apply c.Markets.nodes('/license/company/markets/market') as m(Market)
)
select cp.Code, cp.Name, string_agg(cp.MarketCode, ',') as MarketCodes, cp.Phone
from cte_parse cp
group by cp.Code, cp.Name, cp.Phone;

Скрипка

1. если столбец market является nvarchar, но содержит строку XML, оберните его с помощью convert(xml, market).value … кроме этого, неплохо использовать string_agg !

2. @Mitz, как есть encoding="UTF-8" в объявлении xml ( <?xml blah blah ?> ), это не сработает… Либо используйте VARCHAR (1-байтовая кодировка) вместе с utf-8 или NVARCHAR (2-байтовая кодировка) с utf-16 (или ucs-2 ). Лучше всего было 1) сохранить XML в собственном типе или, по крайней мере, 2) сохранить XML без объявления…

3. @Shnugo вы правы. Если вставкой можно управлять, это лучший выбор. У меня было несколько случаев, когда у меня не было контроля над вставками, и мне приходилось вручную удалять объявление с помощью replace(…); но все мои XML-файлы были < 4k символов, поэтому это было не очень дорого… Это жизнь при взаимодействии с некоторыми незаинтересованными государственными служащими ^^

4. @Sander, спасибо за ваш подробный ответ. Похоже, что версия SQL, которая у меня есть, — это SQL Server 2014 Express Edition, а STRING_AGG работает только на SQL 2017 и более поздних версиях. Существует ли другой способ выполнения функции STRING_AGG? Кроме того, собственное поле XML на самом деле является NVARCHAR (max), а не XML, поэтому я думаю, что это тоже нужно использовать?

5. @JustinS, новое решение предоставляется без функции string_agg() , с типом данных nvarchar(max) и требуемыми приведениями.

Ответ №2:

 declare @t table(CompanyMarkets nvarchar(max));
insert into @t(CompanyMarkets)
values(N'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<license>
<company companyCode="ABC123">
<markets>
<market marketCode="AA"/>
<market marketCode="BB"/>
<market marketCode="CC"/>
<market marketCode="DD"/>
</markets>
</company>
</license>');

select
    --remove encoding ...
    try_cast(replace(CompanyMarkets, 'encoding="UTF-8"', '') as xml),
    --... or transform any prolog to a weirdamp;harmless processing instruction 
    try_cast(concat(case when CompanyMarkets like N'<?xml%' then '<?x ' end, CompanyMarkets) as xml) 
from @t;


select *,
    try_cast(concat(case when CompanyMarkets like N'<?xml%' then '<?x ' end, CompanyMarkets) as xml).query('
    for $i in (data(/license/company/markets/market/@marketCode)[1], (for $k in data(/license/company/markets/market/@marketCode)[position()>1] return concat(",", $k)))
    return text {$i}
    ').value('.', 'nvarchar(max)'),
    
    --?? no spaces in marketCodes
    replace(
    try_cast(concat(case when CompanyMarkets like N'<?xml%' then '<?x ' end, CompanyMarkets) as xml).query('data(/license/company/markets/market/@marketCode)').value('.', 'nvarchar(max)'),
    ' ', ','),
    
    stuff(
    try_cast(concat(case when CompanyMarkets like N'<?xml%' then '<?x ' end, CompanyMarkets) as xml).query('
    for $i in data(/license/company/markets/market/@marketCode) return text {concat(",", $i)}
    ').value('.', 'nvarchar(max)'), 1, 1, '')

from @t;