CODEWITHSUNDEEP
oracle-database
Kunal Vohra
Kunal Vohra

Reputation: 2846

Oracle | XML Data Exported Out with Regex

I have created a query to pull data from XML column of table however I am having issue when XML has list of data i.e.

Cola Brand has multiple code inside

See My query

select extract(cola_info, '/ns2:cola/coke/bottleCode', 'xmlns:ns2="http://myColaStaticInfo.com"') as op;

Output is something like

enter image description here

I would like to have highlighted content one after another and not together in one column.

Any idea!!!

MY XML look like this

<?xml version="1.0" encoding="ISO-8859-15" standalone="yes"?>
<ns2:cola xmlns:ns2="http://myColaStaticInfo.com">
    <coke>
        <code>Bottle_2829</code>
        <label>Mirinda</label>
    </coke>
    <coke>
        <code>Bottle_2830</code>
        <label>Mirinda</label>
    </coke>
    <coke>
        <code>Bottle_2831</code>
        <label>Mirinda</label>
    </coke>
</ns2:cola>

Upvotes: 0

Views: 72

Answers (1)

kfinity
kfinity

Reputation: 9091

XMLTable is probably your best choice for this. Especially since extract has been deprecated.

with example_data as (select xmltype('<?xml version="1.0" encoding="ISO-8859-15" standalone="yes"?>
<ns2:cola xmlns:ns2="http://myColaStaticInfo.com">
    <coke>
        <code>Bottle_2829</code>
        <label>Mirinda</label>
    </coke>
    <coke>
        <code>Bottle_2830</code>
        <label>Mirinda</label>
    </coke>
    <coke>
        <code>Bottle_2831</code>
        <label>Mirinda</label>
    </coke>
</ns2:cola>') as xml from dual)
-- query
select bottle_coke
from example_data e
cross join XMLTABLE(XMLNAMESPACES('http://myColaStaticInfo.com' as "ns2"),
        '/ns2:cola/coke'
         PASSING e.xml
         COLUMNS 
           bottle_coke XMLTYPE PATH 'code'
         ) xt;

Output:

<code>Bottle_2829</code>
<code>Bottle_2830</code>
<code>Bottle_2831</code>

Upvotes: 1

Related Questions