Oracle XMLTABLE - how to get following result?-CodePudding

I need to fetch following result using XMLTABLE function in Oracle 19c database:

HEADER_ATTR2   ITEM_ATTR1   ITEM_ATTR2
attr2          attr11       attr12 
attr2          attr21       attr22 
attr2          attr31       attr32

from this xml:

<body>
  <header>
    <header_attr1>attr1</header_attr1>
    <header_attr2>attr2</header_attr2>
  </header>
  <item>
    <item_attr1>attr11</item_attr1>
    <item_attr2>attr12</item_attr2>
  </item>
  <item>
    <item_attr1>attr21</item_attr1>
    <item_attr2>attr22</item_attr2>
  </item>
  <item>
    <item_attr1>attr31</item_attr1>
    <item_attr2>attr32</item_attr2>
  </item>
</body>

What is the best way to do that?

CodePudding user response：

You can get your output using:

SELECT *
FROM   XMLTABLE(
         '/body/item'
         PASSING XMLTYPE('<body>
  <header>
    <header_attr1>attr1</header_attr1>
    <header_attr2>attr2</header_attr2>
  </header>
  <item>
    <item_attr1>attr11</item_attr1>
    <item_attr2>attr12</item_attr2>
  </item>
  <item>
    <item_attr1>attr21</item_attr1>
    <item_attr2>attr22</item_attr2>
  </item>
  <item>
    <item_attr1>attr31</item_attr1>
    <item_attr2>attr32</item_attr2>
  </item>
</body>')
         COLUMNS
           header_attr2 VARCHAR2(20) PATH './../header/header_attr2',
           item_attr1 VARCHAR2(20) PATH 'item_attr1',
           item_attr2 VARCHAR2(20) PATH 'item_attr2'
       )

HEADER_ATTR2 ITEM_ATTR1 ITEM_ATTR2

attr2 attr11 attr12

attr2 attr21 attr22

attr2 attr31 attr32

HEADER_ATTR2	ITEM_ATTR1	ITEM_ATTR2
attr2	attr11	attr12
attr2	attr21	attr22
attr2	attr31	attr32

db<>fiddle here

CodePudding user response：

The standard way to extract data from multi-level XML (data nested at different levels) is to cascade the calls to xmltable. Something like this; notice how the nested "document" <item> is extracted as xmltype in the first pass, and then passed on as the basis for the second call to xmltable.

select t.id, x1.header_attr1, x1.header_attr2, x2.item_attr1, x2.item_attr2
from   t
       cross apply
       xmltable('/body' passing xmltype(xmlstr)
           columns header_attr1 varchar2(20) path 'header/header_attr1',
                   header_attr2 varchar2(20) path 'header/header_attr2',
                   item         xmltype      path 'item'
       ) x1
       cross apply
       xmltable('/item' passing item
           columns item_attr1 varchar2(20) path 'item_attr1',
                   item_attr2 varchar2(20) path 'item_attr2'
       ) x2
;

This will produce the following output:

    ID HEADER_ATTR1     HEADER_ATTR2     ITEM_ATTR1       ITEM_ATTR2      
------ ---------------- ---------------- ---------------- --------------
  3001 attr1            attr2            attr11           attr12          
  3001 attr1            attr2            attr21           attr22          
  3001 attr1            attr2            attr31           attr32          
  2048 attr201          attr202          attr--11         attr--12        
  2048 attr201          attr202          attr--21         attr--22        
  2048 attr201          attr202          attr--31         attr--32

from the following sample data:

create table t (id, xmlstr) as
select 3001,
'<body>
  <header>
    <header_attr1>attr1</header_attr1>
    <header_attr2>attr2</header_attr2>
  </header>
  <item>
    <item_attr1>attr11</item_attr1>
    <item_attr2>attr12</item_attr2>
  </item>
  <item>
    <item_attr1>attr21</item_attr1>
    <item_attr2>attr22</item_attr2>
  </item>
  <item>
    <item_attr1>attr31</item_attr1>
    <item_attr2>attr32</item_attr2>
  </item>
</body>'
from dual union all
select 2048,
'<body>
  <header>
    <header_attr1>attr201</header_attr1>
    <header_attr2>attr202</header_attr2>
  </header>
  <item>
    <item_attr1>attr--11</item_attr1>
    <item_attr2>attr--12</item_attr2>
  </item>
  <item>
    <item_attr1>attr--21</item_attr1>
    <item_attr2>attr--22</item_attr2>
  </item>
  <item>
    <item_attr1>attr--31</item_attr1>
    <item_attr2>attr--32</item_attr2>
  </item>
</body>'
from dual;