[sql] Extract data from XML Clob using SQL from Oracle Database

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>

This question is related to sql xml oracle parsing clob

The answer is


This should work

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

I have assumed the ** were just for highlighting?


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to xml

strange error in my Animation Drawable How do I POST XML data to a webservice with Postman? PHP XML Extension: Not installed How to add a Hint in spinner in XML Generating Request/Response XML from a WSDL Manifest Merger failed with multiple errors in Android Studio How to set menu to Toolbar in Android How to add colored border on cardview? Android: ScrollView vs NestedScrollView WARNING: Exception encountered during context initialization - cancelling refresh attempt

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to parsing

Got a NumberFormatException while trying to parse a text file for objects Uncaught SyntaxError: Unexpected end of JSON input at JSON.parse (<anonymous>) Python/Json:Expecting property name enclosed in double quotes Correctly Parsing JSON in Swift 3 How to get response as String using retrofit without using GSON or any other library in android UIButton action in table view cell "Expected BEGIN_OBJECT but was STRING at line 1 column 1" How to convert an XML file to nice pandas dataframe? How to extract multiple JSON objects from one file? How to sum digits of an integer in java?

Examples related to clob

Difference between CLOB and BLOB from DB2 and Oracle Perspective? Search for a particular string in Oracle clob column Error : ORA-01704: string literal too long Extract data from XML Clob using SQL from Oracle Database ORA-00932: inconsistent datatypes: expected - got CLOB How to convert CLOB to VARCHAR2 inside oracle pl/sql Java: How to insert CLOB into oracle database Disabling contextual LOB creation as createClob() method threw error How to query a CLOB column in Oracle Most efficient solution for reading CLOB to String, and String to CLOB in Java?