asked on

ORACLE PULLING VALUES OUT OF XML DATA

Hi Experts, I'm new to Oracle. I've got xml that I need to grab the ID values out of. Something like this...

with cte(xml_val) as (
select xmltype(
'<?xml version="1.0" encoding="UTF-8"?>
<report id="7" >
<title>TEST</title>
<fact>
    <field ID="12041346"/>
    <field ID="12041193"/>
    <field ID="12041186"/>
    <field ID="12041220"/>
    <field ID="12041268"/>
    <field ID="12041275"/>
    <field ID="12041276"/>
    <field ID="12041278"/>
    <field ID="12041279"/>
    <field ID="12041280"/>
    <field ID="12041350"/>
</fact>
</report>') from dual
)
select xmlcast(xmlquery('/report/fact/field/@ID' passing xml_val returning content) as varchar2(255)) as colid from cte

Open in new window

The above query returns this long, concatenated string:

COLID
----------------------------------------------------------------------------------------
1204134612041193120411861204122012041268120412751204127612041278120412791204128012041350

Open in new window

I would prefer a list of IDs like this:

Open in new window

Any thoughts???

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

this one

select * from XMLTABLE('/report/fact/field'
passing 
xmltype(
'<?xml version="1.0" encoding="UTF-8"?>
<report id="7">
<title>TEST</title>
<fact>
    <field ID="12041346"/>
    <field ID="12041193"/>
    <field ID="12041186"/>
    <field ID="12041220"/>
    <field ID="12041268"/>
    <field ID="12041275"/>
    <field ID="12041276"/>
    <field ID="12041278"/>
    <field ID="12041279"/>
    <field ID="12041280"/>
    <field ID="12041350"/>
</fact>
</report>')) xt;

Open in new window

returns

HainKurt

and this is what you need

select * from XMLTABLE('/report/fact/field'
passing 
xmltype(
'<?xml version="1.0" encoding="UTF-8"?>
<report id="7">
<title>TEST</title>
<fact>
    <field ID="12041346"/>
    <field ID="12041193"/>
    <field ID="12041186"/>
    <field ID="12041220"/>
    <field ID="12041268"/>
    <field ID="12041275"/>
    <field ID="12041276"/>
    <field ID="12041278"/>
    <field ID="12041279"/>
    <field ID="12041280"/>
    <field ID="12041350"/>
</fact>
</report>')
columns idno VARCHAR2(10) path '@ID') xt;

Open in new window

slightwv (䄆 Netminder)

The accepted answer doesn't give the results you stated you wanted. If gives you the XML nodes themselves.

>>and this is what you need

and you end up with exactly what I posted...

HainKurt

first of all, I dıd not see your post...
second, I checked the queries and I dont select "FROM DUAL"

slightwv (䄆 Netminder)

By the second post, you should have seen the first.

As for selecting from dual, the important piece was extracting the data from the XML and not how you get the xml into the XMLTABLE call.

No one hard codes XML that way. It was a simple mock up example.

It's the same solution no matter how you want to spin it and the accepted solution doesn't meet the stated expected results.

HainKurt

By the second post, you should have seen the first

my first and second post is almost at the same time
when I posted first, I was writing/testing the second one

anyways, it was my solution and it looks like yours
ask this question to 100 people and they will come up with 3-5 different ways...

so what's your point here?

Alex [***Alex140181***]

Just out of interest: might the XML be created as JSON?
FYI: it would be far less overhead (means better performance) and clumsy (means easier to write & understand) then (provided you're running an adequate Oracle version)...

MariaHalt

ASKER

The first response from slightwv answered my question.

slightwv (䄆 Netminder)

Thank you for the correction and sorry about all the off topic commentary.