Help with XML in Oracle clob

j2911
j2911 used Ask the Experts™
on
Hi Experts,

I have an Oracle clob field that contains xml and I need to be able to pull data from it.

Example of Oracle clob xml data might be:

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<Data>
    <line-item-nbr>
        <A1>missing</A1>
        <A2>3</A2>
    </line-item-nbr>
    <content-description>
        <A1>missing</A1>
        <A2>Books</A2>
    </content-description>
    <code>
        <A1>missing</A1>
        <A2>4901.99.0050</A2>
    </code>
    <country>
        <A1>missing</A1>
        <A2>US</A2>
    </country>
    <value>
        <A1>missing</A1>
        <A2>250.00</A2>
    </value>
    <number>
        <A1>missing</A1>
        <A2>0</A2>
    </number>
    <weight>
        <A1>missing</A1>
        <A2>5.0</A2>
    </weight>
</Data>

The only value I would like to pull from this clob would be from the path /Data/Code/A2.

How can I do that?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
select extractvalue(xmltype(yourclob),'/Data/code/A2') from yourtable

Author

Commented:
Thank you.  I'll run this tomorrow and close it tomorrow.

Author

Commented:
Excellent - I cannot thank you enough.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial