Read Columns from XMLTable

Question

I have the below xml in a column as CLOB. How to read the columns of the below XML using XMLTable. I need to retrieve the data for all the nodes. I have shown the nodes in the below.

<?xml version = "1.0" encoding = "UTF-8" standalone = "yes" ?>
   <Supplier>
    <Electronics>
      <Vendor>
        <PoductCode>APP13</PoductCode>
        <Phones>
          <Model>iPhone13</Model>
          <Color>Silver</Color>
          <Storage>128GB</Storage>
        </Phones>   
      </Vendor> 
      <Vendor>
        <PoductCode>APP13P</PoductCode>
        <Phones>
          <Model>iPhone13Pro</Model>
          <Color>Grey</Color>
          <Storage>512GB</Storage>
        </Phones>   
      </Vendor>   
      <Manufacturer>Apple</Manufacturer>
    </Electronics>
    <Electronics>  
      <Vendor>
        <PoductCode>SAMS22</PoductCode>
        <Phones>
          <Model>S22</Model>
          <Color>Black</Color>
          <Storage>256GB</Storage>
        </Phones>   
      </Vendor>      
      <Vendor>
        <PoductCode>SAMS23U</PoductCode>
        <Phones>
          <Model>S23 Ultra</Model>
          <Color>Gold</Color>
          <Storage>512GB</Storage>
        </Phones>   
      </Vendor>   
      <Manufacturer>Samsung</Manufacturer>  
    </Electronics>
    <SupplierName>AlphaLLC</SupplierName>
   </Supplier>

ProdCode--Model--Color --Storage--Manufact--SupplierName

APP13 --iPhone13--Silver --128GB --Apple -- AlphaLLC

APP13P --iPhone13Pro --Grey --512GB --Apple -- AlphaLLC

score 0 · Answer 1 · answered Feb 09 '23 at 07:34

See the below query. The only tricky part is using the ./ to get the ancestor values.

select poductcode, model, color, storage, Manufacturer, Supplier
from test_table t
cross join xmltable
(
    '/Supplier/Electronics/Vendor'
    passing xmltype(t.a_clob)
    columns
        PoductCode   varchar2(4000) path 'PoductCode',
        Model        varchar2(4000) path 'Phones/Model',
        Color        varchar2(4000) path 'Phones/Color',
        Storage      varchar2(4000) path 'Phones/Storage',
        Manufacturer varchar2(4000) path './../Manufacturer',
        Supplier     varchar2(4000) path './../../SupplierName'
) phones;

Read Columns from XMLTable

1 Answers1