We help IT Professionals succeed at work.
Private
Troubleshooting Question

ORACLE PULLING VALUES OUT OF XML DATA

28 Views
Last Modified: 2020-10-08
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
The above query returns this long, concatenated string:
COLID
----------------------------------------------------------------------------------------
1204134612041193120411861204122012041268120412751204127612041278120412791204128012041350
I would prefer a list of IDs like this:
COLID
--------
12041346
12041193
12041186
12041220
12041268
12041275
12041276
12041278
12041279
12041280
12041350
Any thoughts???  
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
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;
returns


HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
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;

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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...
HainKurtSr. System Analyst
CERTIFIED EXPERT

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

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
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***]Software Developer
CERTIFIED EXPERT

Commented:
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)...

Author

Commented:
The first response from slightwv answered my question.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Thank you for the correction and sorry about all the off topic commentary.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.