SQL XML список узлов, разделенных запятой

Question

У меня есть нормальная таблица SQL, один из столбцов XML, пример:

...
<Element><id>first</id></Element>
<Element><id>second</id></Element>
...

Мне нужно получить список идентификаторов, разделенных запятыми:

id_list
---
first,second

На данный момент я добился этого, создав XMLTABLE с идентификаторами:

id
----
first
second

а затем с помощью функции LISTAGG Oracle. Интересно, существует ли какая-нибудь функция / цикл (возможно, FLWOR?), Чтобы получить тот же результат, но не преобразовывать входные данные XML в XMLTABLE.

Большое спасибо за помощь

Answer 1

Вы можете сделать это с помощью функции XMLDB, используя XPath. string-join функция

Как XMLQuery, если у вас есть одно значение для обработки (с вашими примерами данных, предоставленными через CTE и фиктивный корневой узел):

with t (xml) as (
  select xmltype('<root>
<Element><id>first</id></Element>
<Element><id>second</id></Element>
</root>') from dual
)
select xmlquery('
  for $i in /root
    return <e>{ fn:string-join($i/Element/id, ",") }</e>/text()'
  passing xml
  returning content
) as result
from t;

RESULT                                                                          
--------------------------------------------------------------------------------
first,second

Или с XMLTable:

with t (xml) as (
  select xmltype('<root>
<Element><id>first</id></Element>
<Element><id>second</id></Element>
</root>') from dual
)
select x.*
from t
cross join xmltable('
  for $i in /root
    return <e>{ fn:string-join($i/Element/id, ",") }</e>'
  passing xml
  columns result varchar2(4000) path '.'
) x;

RESULT                                                                          
--------------------------------------------------------------------------------
first,second

Я не уверен, что есть много преимуществ в этом listagg(), но может быть интересно сравнить производительность обоих с вашими реальными данными, особенно если есть много узлов. За исключением того, что, изменив тип столбца XMLTable на CLOB, вы можете получить большее значение из той версии, которое вы можете использовать с listagg(),

Answer 2

Если вы должны, вы можете сделать что-то вроде того, что я показываю в запросе ниже.

Однако я не считаю это хорошим подходом; что вы делаете в настоящее время, создав таблицу XML и затем используя LISTAGGкажется лучше.

with inputs ( xml_str ) as (
       select '...
               <Element><id>first</id></Element>
               <Element><id>second</id></Element>
               ...'
       from dual
     )
-- End of test data (not part of the solution); SQL query begins below this line
select rtrim( regexp_replace( xml_str, '.*?(<Element><id>(.*?)</id></Element>|$)'
                            , '\2,', 1, 0, 'n')
            , ',') as id_list
from   inputs
;



ID_LIST
------------
first,second