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>

score 26 · Accepted Answer · answered Nov 28 '12 at 09:22

26

Try

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

Here is a sqlfiddle demo

answered Nov 28 '12 at 09:22

A.B.Cade

16,735
1
37
53

Hi.. I am having similar issue.Can you help me out.The SQL Fiddle doesnt work. – Teja Sep 28 '13 at 08:55
Great it works now.. How can I extract the entire content and right it to a file? – Teja Oct 01 '13 at 15:31
@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](http://stackoverflow.com/questions/27562/oracle-write-to-file) or [here](http://stackoverflow.com/questions/15253440/how-to-output-oracle-sql-result-into-a-file-in-windows) – A.B.Cade Oct 02 '13 at 06:09
How can we read the value of "key" attribute of the first ? – Mehrdad Abdolghafari Feb 28 '19 at 20:13
i try this and it's not work , also SQL Fiddle not worked too . – Mohammad Mirzaeyan Jan 20 '20 at 10:51

score 2 · Answer 2 · answered Nov 28 '12 at 09:10

2

This should work

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

I have assumed the ** were just for highlighting?

answered Nov 28 '12 at 09:10

A. Gilfrin

302
1
8

Not working - getting Ora-00932 error. Solution by A.B.Cade worked perfectly. – mitrabhanu Nov 28 '12 at 12:36

score 0 · Answer 3 · answered Nov 21 '22 at 10:12

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)

Extract data from XML Clob using SQL from Oracle Database

3 Answers3

Linked