Анализ формата XML с дочерними узлами с использованием plsql

#sql #xml #oracle #xml-parsing

Вопрос:

Я новичок в формате xml и sql, и у меня есть проблема. У меня есть SQL-запрос, который возвращает xml, как показано ниже:

 <?xml version="1.0" encoding="WINDOWS-1250"?>
<AROUND>
  <AB>
    <Connected>1</Connected>
    <EF>1010</EF>
    <GH>10162</GH>
    <IJ>HG-131120CXD</IJ>
    <KL>New</KL>
    <MN>284822</MN>
  </AB>
  <AB>
    <Connected>0</Connected>
    <EF>2123</EF>
    <GH>54321</GH>
    <IJ>HG-131120BRE</IJ>
    <KL>Old</KL>
    <MN>284822</MN>
  </AB>
  <AB>
    <Connected>1</Connected>
    <EF>4321</EF>
    <GH>12345</GH>
    <IJ>HG-131120DYX</IJ>
    <KL>New</KL>
    <MN>284822</MN>
  </AB>
</AROUND>

и я должен извлечь из него некоторые значения (например: Connected, GH, IJ, KL) с помощью запроса, чтобы каждая его часть AB, независимо от того, сколько их там (в этом примере их 3, но может быть 1-10 или даже больше), имела свою собственную строку. Например (Подключен, GH, IJ, KL), первая строка будет: 1 10162 HG-131120CXD Новый. И так далее, и так далее…Заранее спасибо.

Ответ №1:

Вы можете использовать XMLTable() для извлечения нужных данных из вашего XML-документа; что-то вроде:

 select x.connected, x.gh, x.ij, x.kl
from your_query q
cross apply xmltable (
  '/AROUND/AB'
  passing q.xml_doc
  columns
    connected number path 'Connected',
    gh number path 'GH',
    ij varchar2(20) path 'IJ',
    kl varchar2(10) path 'KL'
) x

где your_query находится любой запрос, который в данный момент генерирует ваш XML-документ. Если он генерирует строковое значение, вы можете обернуть его, XMLType() чтобы преобразовать.

db<>fiddle<> показывает и то, и другое, используя CTE для представления существующего запроса для создания XML-документа или строкового значения, чтобы получить:

 CONNECTED |    GH | IJ           | KL 
--------: | ----: | :----------- | :--
        1 | 10162 | HG-131120CXD | New
        0 | 54321 | HG-131120BRE | Old
        1 | 12345 | HG-131120DYX | New

Вы можете использовать эту инструкцию SQL в блоке PL/SQL, как и любой другой SQL; если XML-документ находится в переменной, вы можете сделать что-то вроде:

 for r in (
  select x.connected, x.gh, x.ij, x.kl
  from xmltable (
    '/AROUND/AB'
    passing l_xml_doc -- local XMLType variable
    columns
      connected number path 'Connected',
      gh number path 'GH',
      ij varchar2(20) path 'IJ',
      kl varchar2(10) path 'KL'
  ) x
) loop
  -- do something with r.connected, r.gh etc.
end loop;

Ответ №2:

Вы можете использовать два выражения XPath с общими табличными выражениями. С помощью первого вы разделяете AB узлы, а со вторым объединяете соответствующие элементы в строку.

 with tmp as (
select unnest (xpath ('//AB',
'<?xml version="1.0" encoding="WINDOWS-1250"?>
<AROUND>
  <AB>
    <Connected>1</Connected>
    <EF>1010</EF>
    <GH>10162</GH>
    <IJ>HG-131120CXD</IJ>
    <KL>New</KL>
    <MN>284822</MN>
  </AB>
  <AB>
    <Connected>0</Connected>
    <EF>2123</EF>
    <GH>54321</GH>
    <IJ>HG-131120BRE</IJ>
    <KL>Old</KL>
    <MN>284822</MN>
  </AB>
  <AB>
    <Connected>1</Connected>
    <EF>4321</EF>
    <GH>12345</GH>
    <IJ>HG-131120DYX</IJ>
    <KL>New</KL>
    <MN>284822</MN>
  </AB>
</AROUND>')) ab
)
select unnest (xpath ('concat(//Connected/text()," ",//GH/text()," ",//IJ/text()," ",//KL/text())', tmp.ab)) ab
from tmp;

Это возвращает:

             ab            
--------------------------
 1 10162 HG-131120CXD New
 0 54321 HG-131120BRE Old
 1 12345 HG-131120DYX New