CODEWITHSUNDEEP
sqlxmloracle-databasexml-parsing
OmarLittle
OmarLittle

Reputation: 503

Parsing XML format with child nodes using plsql

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.

Upvotes: 0

Views: 247

Answers (2)

ceving
ceving

Reputation: 23814

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

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191265

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;

Upvotes: 3

Related Questions