Extract data from XML Clob using SQL from Oracle Database

Question

I want to extract the value of Decision using sql from table TRAPTABCLOB having column testclob with XML stored as clob.

Sample XML as below.

        <?xml version="1.0" encoding="UTF-8"?>
<DCResponse>
    <Status>Success</Status>
    <Authentication>
        <Status>Success</Status>
    </Authentication>
    <ResponseInfo>
        <ApplicationId>5701200</ApplicationId>
        <SolutionSetInstanceId>
                        63a5c214-b5b5-4c45-9f1e-b839a0409c24
                    </SolutionSetInstanceId>
        <CurrentQueue />
    </ResponseInfo>
    <ContextData>
        <!--Decision Details Start-->
        <Field key="SoftDecision">A</Field>
        <Field key="**Decision**">1</Field>
        <Field key="NodeNo">402</Field>
        <Field key="NodeDescription" />
        <!--Decision Details End-->
        <!--Error Details Start-->
        <Field key="ErrorResponse">
            <Response>
                <Status>[STATUS]</Status>
                <ErrorCode>[ERRORCODE]</ErrorCode>
                <ErrorDescription>[ERRORDESCRIPTION]</ErrorDescription>
                <Segment>[SEGMENT]</Segment>
            </Response>
        </Field>
        <Field key="ErrorCode">0</Field>
        <Field key="ErrorDescription" />
    </ContextData>
</DCResponse>

A.B.Cade · Accepted Answer · 2012-11-28 09:22:42Z

28

Try

SELECT EXTRACTVALUE(xmltype(testclob), '/DCResponse/ContextData/Field[@key="Decision"]') 
FROM traptabclob;

Here is a sqlfiddle demo

answered Nov 28, 2012 at 9:22

A.B.Cade

16.9k1 gold badge39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Teja Over a year ago

Hi.. I am having similar issue.Can you help me out.The SQL Fiddle doesnt work.

Teja Over a year ago

Great it works now.. How can I extract the entire content and right it to a file?

A.B.Cade Over a year ago

@SOaddict, the entire content is in testclob you can extract whatever you want with the right XPATH expression (2nd param of extract function). As to how to write it to a file - this is a whole different post... see here or here

Mehrdad Abdolghafari Over a year ago

How can we read the value of "key" attribute of the first <Field>?

Mohammad Mirzaeyan Over a year ago

i try this and it's not work , also SQL Fiddle not worked too .

A. Gilfrin · Accepted Answer · 2012-11-28 09:10:43Z

2

This should work

SELECT EXTRACTVALUE(column_name, '/DCResponse/ContextData/Decision') FROM traptabclob;

I have assumed the ** were just for highlighting?

answered Nov 28, 2012 at 9:10

A. Gilfrin

3021 silver badge8 bronze badges

1 Comment

mitrabhanu Over a year ago

Not working - getting Ora-00932 error. Solution by A.B.Cade worked perfectly.

futurk · Accepted Answer · 2022-11-21 10:12:49Z

0

If you're having problem with XMLTYPE phrase, then try this:

SELECT EXTRACTVALUE(XMLPARSE(DOCUMENT textClob), '/DCResponse/ContextData/Field[@key="Decision"]') FROM traptabclob;

This is usually because the column textClob has an invalid format of XML. XMLParse parses and generates an XML instance from the evaluated result of value_expr. See XMLPARSE, Oracle Database Online Documentation 11g, Release 2 (11.2)

answered Nov 21, 2022 at 10:12

futurk

313 bronze badges

Collectives™ on Stack Overflow

Extract data from XML Clob using SQL from Oracle Database

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related