Skip to Main Content
  • Questions

  • Retrieve fields from clob field with envelope and HTML chacaters

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Raphael.

Asked: October 18, 2017 - 8:35 pm UTC

Last updated: November 03, 2017 - 7:22 am UTC

Version: Oracle 11

Viewed 1000+ times

You Asked

Dear

I have tried to parse a clob field located in a Oracle table that contains a envelope and also some HTML characters (&quot, &lt, &gt), as shown below:

<?xml version="1.0" encoding="utf-8"?><soapenv:Envelope xmlns:soapenv=" http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd=" http://www.w3.org/2001/XMLSchema" xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" ><soapenv:Body><inputXmlReturn xmlns=" http://invoker.ps.eos.fairisaac.com" >&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;&lt;Application APPLICATIONCROSSREFERENCEID=&quot;111222&quot; DeliveryOptionCode=&quot;Test&quot;&gt;&lt;MessageList StatusCode=&quot;0&quot; StatusDescription=&quot;Successful&quot;/&gt;&lt;CreditApprovalProcess AmountFinanced=&quot;1000&quot;TransactionCnt=&quot;1&quot;&gt;&lt;ApList/&gt;&lt;RevenueSources&gt;&lt;RevenueSource RevenueSourceType=&quot;Cana&quot;/&gt; &lt;/RevenueSources&gt;&lt;EquipmentList&gt; &lt;Equipment EquipLoanTerm=&quot;60&quot; EquipModel=&quot;AAAAA&quot; EquipNewOrUsed=&quot;N&quot; EquipSellingPrice=&quot;1000&quot; EquipType=&quot;Test&quot;/&gt; &lt;/EquipmentList&gt;&lt;ApplicantList&gt; &lt;Applicant StartDtBusiness=&quot;1900-01-01&quot;&gt;&lt;InstallmentList/&gt; &lt;/Applicant&gt; &lt;Applicant StartDtTest=&quot;1900-01-01&quot;&gt;&lt;InstallmentList/&gt; &lt;/Applicant&gt; &lt;/ApplicantList&gt; &lt;/CreditApprovalProcess&gt;&lt;CreditRequest ProductCategory=&quot;cat_Generic&quot; ProductCode=&quot;prod_XXX&quot;/&gt;&lt;DecisionResponse&gt; &lt;Product DecisionFlowName=&quot;df_test_Ag&quot; LastStepName=&quot;rs_Test&quot; OriginType=&quot;Request&quot; ProductCategory=&quot;cat_Generic&quot; ProductCode=&quot;prod_XXX&quot; ProductStatusIndicator=&quot;Complete&quot; StrategyName=&quot;&quot;&gt; &lt;Decision DecisionIndex=&quot;0&quot; DecisionResult=&quot;Investigate&quot; DecisionStatusIndicator=&quot;Final&quot; ImplementationName=&quot;rs_Test&quot; ImplementationType=&quot;Ruleset&quot;&gt; &lt;Reason RankOrderNumber=&quot;2&quot;&gt; &lt;ReasonText&gt;Test&lt;/ReasonText&gt; &lt;/Reason&gt; &lt;Reason RankOrderNumber=&quot;3&quot;&gt; &lt;ReasonText&gt;Test&lt;/ReasonText&gt; &lt;/Reason&gt; &lt;/Decision&gt; &lt;/Product&gt; &lt;/DecisionResponse&gt;&lt;ProcessingHistory ProcessingTimestamp=&quot;2017-04-25T08:53:37.202-03:00&quot; SystemId=&quot;Test&quot;&gt; &lt;DecisionFlowHistory DecisionFlowName=&quot;df_Test_Ag&quot; ProductCategory=&quot;cat_Generic&quot; ProductCode=&quot;prod_XXX&quot; StartTimestamp=&quot;2017-04-25T08:53:37.204-03:00&quot; StopTimestamp=&quot;2017-04-25T08:53:37.205-03:00&quot; SystemDecisionResult=&quot;Investigate&quot;&gt; &lt;DecisionFlowStepHistory ImplementationName=&quot;dms_InitialDefaults&quot; ImplementationType=&quot;Data Method Sequence&quot; StartTimestamp=&quot;2017-04-25T08:53:37.204-03:00&quot; StepName=&quot;dms_InitialDefaults&quot; StopTimestamp=&quot;2017-04-25T08:53:37.204-03:00&quot;&gt; &lt;DataMethodSequenceHistory DataMethodSequenceName=&quot;dms_InitialDefaults&quot;/&gt; &lt;/DecisionFlowStepHistory&gt; &lt;DecisionFlowStepHistory ImplementationName=&quot;rs_Test&quot; ImplementationType=&quot;Ruleset&quot; StartTimestamp=&quot;2017-04-25T08:53:37.204-03:00&quot; StepName=&quot;rs_Test&quot; StopTimestamp=&quot;2017-04-25T08:53:37.205-03:00&quot;&gt; &lt;RulesetHistory RulesFiredTotalSeverity=&quot;2&quot; RulesetName=&quot;rs_Test&quot; RulesetResult=&quot;RuleFails&quot;&gt; &lt;RuleHistory ReasonText=&quot;Test&quot; RuleName=&quot;rule_SerasaBureauCheck&quot;/&gt;&lt;RuleHistory ReasonText=&quot;Test&quot; RuleName=&quot;rule_BacenBureauCheck&quot;/&gt; &lt;DecisionHistory DecisionResult=&quot;Investigate&quot;/&gt; &lt;/RulesetHistory&gt; &lt;/DecisionFlowStepHistory&gt; &lt;/DecisionFlowHistory&gt; &lt;/ProcessingHistory&gt;&lt;DataMethodHistory Timestamp=&quot;2017-04-25T08:53:37.205-03:00&quot;&gt; &lt;Name&gt;dm_FinancialDataCheck&lt;/Name&gt; &lt;DataType&gt;boolean&lt;/DataType&gt; &lt;Value&gt;false&lt;/Value&gt; &lt;/DataMethodHistory&gt; &lt;/Application&gt;</inputXmlReturn></soapenv:Body></soapenv:Envelope>

Basically, I'd like to extract all data stored in every single attribute value, for instance:
APPLICATIONCROSSREFERENCEID=&quot;111222&quot;

It should result in:
APPLICATIONCROSSREFERENCEID 111222

Could you please provide me some example to do that in a Select Oracle?

Regards,
Raphael

and Connor said...

The following will extract the input and return an xmltype

SQL>
SQL> set define off
SQL>
SQL> select xmltype(p.field1)
  2  from (
  3  select xmltype(
  4  '<?xml version="1.0" encoding="utf-8"?>'
  5  ||'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope">'
  6  ||'<soapenv:Body>'
  7  ||'<inputXmlReturn xmlns="http://invoker.ps.eos.fairisaac.com">'
  8  ||'&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;&lt;Application APPLICATIONCROSSREFERENCEID=&quot;111222&quot; DeliveryOptionCode=&quot;Test&quot;&gt;&lt;MessageList StatusCode=&quot;0'
  9  ||'&quot; StatusDescription=&quot;Successful&quot;/&gt;&lt;CreditApprovalProcess AmountFinanced=&quot;1000&quot; TransactionCnt=&quot;1&quot;&gt;&lt;ApList/&gt;&lt;RevenueSources&gt;&lt;RevenueSource RevenueSourceType=&quot;Cana&quot;'
 10  ||'/&gt; &lt;/RevenueSources&gt;&lt;EquipmentList&gt; &lt;Equipment EquipLoanTerm=&quot;60&quot; EquipModel=&quot;AAAAA&quot; EquipNewOrUsed=&quot;N&quot; EquipSellingPrice=&quot;1000&quot; EquipType=&quot;Test&quot;/&gt; &lt;/'
 11  ||'EquipmentList&gt;&lt;ApplicantList&gt; &lt;Applicant StartDtBusiness=&quot;1900-01-01&quot;&gt;&lt;InstallmentList/&gt; &lt;/Applicant&gt; &lt;Applicant StartDtTest=&quot;1900-01-01&quot;&gt;&lt;InstallmentList/&gt; &lt;/Applicant&gt; &lt;/'
 12  ||'ApplicantList&gt; &lt;/CreditApprovalProcess&gt;&lt;CreditRequest ProductCategory=&quot;cat_Generic&quot; ProductCode=&quot;prod_XXX&quot;/&gt;&lt;DecisionResponse&gt; &lt;Product DecisionFlowName=&quot;df_test_Ag&quot; LastStepName='
 13  ||'&quot;rs_Test&quot; OriginType=&quot;Request&quot; ProductCategory=&quot;cat_Generic&quot; ProductCode=&quot;prod_XXX&quot; ProductStatusIndicator=&quot;Complete&quot; StrategyName=&quot;&quot;&gt; &lt;Decision DecisionIndex='
 14  ||'&quot;0&quot; DecisionResult=&quot;Investigate&quot; DecisionStatusIndicator=&quot;Final&quot; ImplementationName=&quot;rs_Test&quot; ImplementationType=&quot;Ruleset&quot;&gt; &lt;Reason RankOrderNumber=&quot;2&quot;&gt; &lt;'
 15  ||'ReasonText&gt;Test&lt;/ReasonText&gt; &lt;/Reason&gt; &lt;Reason RankOrderNumber=&quot;3&quot;&gt; &lt;ReasonText&gt;Test&lt;/ReasonText&gt; &lt;/Reason&gt; &lt;/Decision&gt; &lt;/Product&gt; &lt;/DecisionResponse&gt;&lt;'
 16  ||'ProcessingHistory ProcessingTimestamp=&quot;2017-04-25T08:53:37.202-03:00&quot; SystemId=&quot;Test&quot;&gt; &lt;DecisionFlowHistory DecisionFlowName=&quot;df_Test_Ag&quot; ProductCategory=&quot;cat_Generic&quot; ProductCode=&quot;'
 17  ||'prod_XXX&quot; StartTimestamp=&quot;2017-04-25T08:53:37.204-03:00&quot; '
 18  ||'StopTimestamp=&quot;2017-04-25T08:53:37.205-03:00&quot; SystemDecisionResult=&quot;Investigate&quot;&gt; &lt;DecisionFlowStepHistory ImplementationName=&quot;dms_InitialDefaults&quot; ImplementationType='
 19  ||'&quot;Data Method Sequence&quot; StartTimestamp=&quot;2017-04-25T08:53:37.204-03:00&quot; StepName=&quot;dms_InitialDefaults&quot; StopTimestamp=&quot;2017-04-25T08:53:37.204-03:00&quot;&gt; &lt;DataMethodSequenceHistory DataMethodSequenceName'
 20  ||'=&quot;dms_InitialDefaults&quot;/&gt; &lt;/DecisionFlowStepHistory&gt; &lt;DecisionFlowStepHistory ImplementationName=&quot;rs_Test&quot; ImplementationType=&quot;Ruleset&quot; StartTimestamp=&quot;2017-04-25T08:53:37.204-03:00&quot; StepName'
 21  ||'=&quot;rs_Test&quot; StopTimestamp=&quot;2017-04-25T08:53:37.205-03:00&quot;&gt; &lt;RulesetHistory RulesFiredTotalSeverity=&quot;2&quot; RulesetName=&quot;rs_Test&quot; RulesetResult=&quot;RuleFails&quot;&gt; &lt;RuleHistory ReasonText='
 22  ||'&quot;Test&quot; RuleName=&quot;rule_SerasaBureauCheck&quot;/&gt;&lt;RuleHistory ReasonText=&quot;Test&quot; RuleName=&quot;rule_BacenBureauCheck&quot;/&gt; &lt;DecisionHistory DecisionResult=&quot;Investigate&quot;/&gt; &lt;/'
 23  ||'RulesetHistory&gt; &lt;/DecisionFlowStepHistory&gt; &lt;/DecisionFlowHistory&gt; &lt;/ProcessingHistory&gt;&lt;DataMethodHistory Timestamp=&quot;2017-04-25T08:53:37.205-03:00&quot;&gt; &lt;Name&gt;dm_FinancialDataCheck&lt;/Name&gt; &lt;'
 24  ||'DataType&gt;boolean&lt;/DataType&gt; &lt;Value&gt;false&lt;/Value&gt; &lt;/DataMethodHistory&gt; &lt;/Application&gt;'
 25  ||'</inputXmlReturn>'
 26  ||'</soapenv:Body>'
 27  ||'</soapenv:Envelope>') as xml
 28  from dual ) t,
 29      xmltable(
 30        xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope' as "soapenv",
 31        DEFAULT 'http://invoker.ps.eos.fairisaac.com'
 32        ),
 33      '/soapenv:Envelope/soapenv:Body' passing t.xml
 34                      columns
 35                          field1 varchar2(4000) path 'inputXmlReturn') p;

XMLTYPE(P.FIELD1)
----------------------------------------------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="WINDOWS-1252"?>
<Application APPLICATIONCROSSREFERENCEID="111222" DeliveryOptionCode="Test">
  <MessageList StatusCode="0" StatusDescription="Successful"/>
  <CreditApprovalProcess AmountFinanced="1000" TransactionCnt="1">
    <ApList/>
    <RevenueSources>
      <RevenueSource RevenueSourceType="Cana"/>
    </RevenueSources>
    <EquipmentList>
      <Equipment EquipLoanTerm="60" EquipModel="AAAAA" EquipNewOrUsed="N" EquipSellingPrice="1000" EquipType="Test"/>
    </EquipmentList>
    <ApplicantList>
      <Applicant StartDtBusiness="1900-01-01">
        <InstallmentList/>
      </Applicant>
      <Applicant StartDtTest="1900-01-01">
        <InstallmentList/>
      </Applicant>
    </ApplicantList>
  </CreditApprovalProcess>
  <CreditRequest ProductCategory="cat_Generic" ProductCode="prod_XXX"/>
  <DecisionResponse>
    <Product DecisionFlowName="df_test_Ag" LastStepName="rs_Test" OriginType="Request" ProductCategory="cat_Generic" ProductCode="
prod_XXX" ProductStatusIndicator="Complete" StrategyName="">
      <Decision DecisionIndex="0" DecisionResult="Investigate" DecisionStatusIndicator="Final" ImplementationName="rs_Test" Implem
entationType="Ruleset">
        <Reason RankOrderNumber="2">
          <ReasonText>Test</ReasonText>
        </Reason>
        <Reason RankOrderNumber="3">
          <ReasonText>Test</ReasonText>
        </Reason>
      </Decision>
    </Product>
  </DecisionResponse>
  <ProcessingHistory ProcessingTimestamp="2017-04-25T08:53:37.202-03:00" SystemId="Test">
    <DecisionFlowHistory DecisionFlowName="df_Test_Ag" ProductCategory="cat_Generic" ProductCode="prod_XXX" StartTimestamp="2017-0
4-25T08:53:37.204-03:00" StopTimestamp="2017-04-25T08:53:37.205-03:00" SystemDecisionResult="Investigate">
      <DecisionFlowStepHistory ImplementationName="dms_InitialDefaults" ImplementationType="Data Method Sequence" StartTimestamp="
2017-04-25T08:53:37.204-03:00" StepName="dms_InitialDefaults" StopTimestamp="2017-04-25T08:53:37.204-03:00">
        <DataMethodSequenceHistory DataMethodSequenceName="dms_InitialDefaults"/>
      </DecisionFlowStepHistory>
      <DecisionFlowStepHistory ImplementationName="rs_Test" ImplementationType="Ruleset" StartTimestamp="2017-04-25T08:53:37.204-0
3:00" StepName="rs_Test" StopTimestamp="2017-04-25T08:53:37.205-03:00">
        <RulesetHistory RulesFiredTotalSeverity="2" RulesetName="rs_Test" RulesetResult="RuleFails">
          <RuleHistory ReasonText="Test" RuleName="rule_SerasaBureauCheck"/>
          <RuleHistory ReasonText="Test" RuleName="rule_BacenBureauCheck"/>
          <DecisionHistory DecisionResult="Investigate"/>
        </RulesetHistory>
      </DecisionFlowStepHistory>
    </DecisionFlowHistory>
  </ProcessingHistory>
  <DataMethodHistory Timestamp="2017-04-25T08:53:37.205-03:00">
    <Name>dm_FinancialDataCheck</Name>
    <DataType>boolean</DataType>
    <Value>false</Value>
  </DataMethodHistory>
</Application>


1 row selected.

SQL>
SQL>
SQL>
SQL>


Now that you have an xmltype, you can use XMLTABLE to extract the columns as if it were normal XML.

Search this site for XMLTABLE for plenty of examples

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Raphael, November 03, 2017 - 2:23 am UTC

Thanks so much Connor!
This is exactly what I was looking for!

Regards,
Raphael
Connor McDonald
November 03, 2017 - 7:22 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library