Trying to pull specific clob data from oracle database

Question

I have this CLOB column which basically contains fake XML in it. I call it fake because it doesn't follow the same parent child structure as a normal XML document.

Heres a sample from my clob:

 <retryDetails>
    <numberOfRetries>0</numberOfRetries>
    <isToDoEntrySuppressed>false</isToDoEntrySuppressed>
 </retryDetails>
    <interimStatus>D1DS</interimStatus>
    <completionDetail>
    <updateStatusDateTime>2017-07-06-15.24.14</updateStatusDateTime>
    <completionEventDataArea>false</completionEventDataArea>
    <servicePointCompletionDetails>
    <servicePointDataDetails/>
    </servicePointCompletionDetails>
    <messageCompletionInformation/>
    <installDataDetails/>
    <ctCheck>
    <currentTransformerDetails/>
    </ctCheck>
    <vtCheck>
    <voltageTransformerDetails/>
    </vtCheck>
    <existingDevice>
    <deviceCompletionDetails>
    <deviceDataDetails/>
    <readingCompletionDetails>
    <readingDetails/>
    </readingCompletionDetails>
    </deviceCompletionDetails>
    <itemCompletionDetails>
    <itemDataDetails/>
    </itemCompletionDetails>
    <meterTestDataDetails/>
    <meterDataDetails/>
    </existingDevice>
    <newDevice>
    <deviceCompletionDetails>
    <deviceDataDetails/>
    <readingCompletionDetails>
    <readingDetails/>
    </readingCompletionDetails>
    </deviceCompletionDetails>
    <itemCompletionDetails>
    <itemDataDetails/>
    </itemCompletionDetails>
    <meterDataDetails/>
    <currentTransformerDetails/>
    <voltageTransformerDetails/>
    </newDevice>
    <completionInformation>
    <customerContactDetails/>
    <remarkTypes/>
    <grantExtensionDetails/>
    <paymentDetails/>
    </completionInformation>
    </completionDetail>
    <responseDetail>
    <message>
    <taskId>01327329221115</taskId>
    <hostExternalId>01327329221115</hostExternalId>
    <taskType>D1-ServiceInvestigation</taskType>
    <completionDateTime>2017-07-06-15.24.14</completionDateTime>
    <completedByCrew>Successfully Issued a work to CREW=E04393</completedByCrew>
    <messageId>01327329221115</messageId>
    <completionInformation>
    <dispatchDateTime>2017-07-06-15.24.14</dispatchDateTime>
    <customerContactDetails/>
    <remarkTypes/>
    </completionInformation>
    <utilityCompletionInformation>
    <servicePointCompletionDetails>
    <servicePointDataDetails>
    <servicePointId>741788842119</servicePointId>
    </servicePointDataDetails>
    </servicePointCompletionDetails>
    <existingDevice>
    <meterCompletionDetails>
    <verificationDetails>
    <response>
    <readingDetails/>
    </response>
    </verificationDetails>
    <meterDataDetails/>
    <readingCompletionDetails>
    <readingDetails/>
    </readingCompletionDetails>
    </meterCompletionDetails>
    <itemCompletionDetails>
    <verificationDetails>
    <response>
    <readingDetails/>
    </response>
    </verificationDetails>
    <itemDataDetails/>
    </itemCompletionDetails>
    </existingDevice>
    <newDevice>
    <meterCompletionDetails>
    <verificationDetails>
    <response>
    <readingDetails/>
    </response>
    </verificationDetails>
    <meterDataDetails/>
    <readingCompletionDetails>
    <readingDetails/>
    </readingCompletionDetails>
    </meterCompletionDetails>
    <itemCompletionDetails>
    <verificationDetails>
    <response>
    <readingDetails/>
    </response>
    </verificationDetails>
    <itemDataDetails/>
    </itemCompletionDetails>
    </newDevice>
    </utilityCompletionInformation>
    </message>
    <fault/>
    </responseDetail>

What I need to do is figure out how, in a select statement, to pull out a specific node in this data mess, specifically the "completedByCrew" node.

I tried this method -

SELECT XMLQUERY('/root/retryDetails/numberOfRetries/text()' 
       PASSING xmltype(to_clob('<root>') || 
                       A.BO_DATA_AREA    ||
                      '</root>') RETURNING CONTENT) AS RESULT
  FROM CISADM.D1_COMM_IN A

But when I go past the number of Retries node, the information stops returning and just goes null. Any ideas?

Answer 1

I can't get your example to work, but I think you could just put your raw text in a root element (named "root" in this example) before parsing it, and Oracle's XML engine will be much happier.

select XMLQUERY('/root/responseDetail/message/completedByCrew/text()' 
    PASSING xmltype( to_clob('<root>') || A.BO_DATA_AREA || '</root>' ) 
    RETURNING CONTENT) AS RESULT
FROM CISADM.D1_COMM_IN A