Parsing XML format with child nodes using plsql

Question

I am new to xml format and sql and I have an issue. I have a SQL query that returns the xml like the one below:

<?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>

and I have to extract some of the values from it (for example: Connected, GH, IJ, KL) using a query so that every AB part of it, no matter how much of them are there (in this example there are 3, but there could be 1-10 or even more), has it's own row. For example (Connected, GH, IJ, KL), first row would be: 1 10162 HG-131120CXD New. And so on, and so on...Thanks in advance.

Do you really mean PL/SQL; or just Oracle-flavour SQL? PL/SQL is a a procedural extension. — Alex Poole
– Alex Poole, Commented Sep 8, 2021 at 9:57

Alex Poole · Accepted Answer · 2021-09-08 10:01:28Z

You can use XMLTable() to extract the data you want from your XML document; something like:

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

where your_query is whatever query currently generates your XML document. If it generates a string value then you can wrap that in XMLType() to convert it.

db<>fiddle showing both, using a CTE to represent your existing query to produce an XML document or string value, to get:

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

You can use that SQL statement within a PL/SQL block like any other SQL; if the XML document is in a variable you can do something like:

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;

ceving · Accepted Answer · 2021-09-08 10:54:54Z

0

You can use two XPath expressions with a Common Table Expressions. With the first you split the AB nodes and with the second you concatenate the relevant elements into a string.

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;

This returns:

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

answered Sep 8, 2021 at 10:54

ceving

24.2k15 gold badges120 silver badges201 bronze badges

2 Comments

Alex Poole Over a year ago

The question is tagged for Oracle, not PostgreSQL, so this syntax won't work for the OP.

ceving Over a year ago

@AlexPoole Indeed I missed it.

Collectives™ on Stack Overflow

Parsing XML format with child nodes using plsql

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related