We help IT Professionals succeed at work.

search from CLOB query in oracle

swathi ponna
swathi ponna used Ask the Experts™
on
I have query in oracle as below:

SELECT XMLGEN_DOC FROM  F5G_R_GEN_XML_AUDIT_DTLS WHERE COB_DATE = '30-SEP-2019'

XMLGEN_DOC is of CLOB data type which has content as below:

Can somebody help how to retrieve only <a1> content.

<a1>
<b1>111</b1>
<c1>222</c1>
<d1>333</d1>
</a1>

<a1>
<b1>11</b1>
<c1>22</c1>
<e1>33</e1>
</a1>

<a2>
<b1>1221</b1>
<c1>22</c1>
<e1>323</e1>
</a2>

<a2>
<b1>12221</b1>
<c1>222</c1>
<e1>3232</e1>
</a2>

<a3>
<b1>16221</b1>
<c1>2262</c1>
<e1>32632</e1>
</a3>

<a4>
<b1>1421</b1>
<c1>2242</c1>
<e1>32432</e1>
</a4>
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
What was posted isn't valid XML.  There is no root node.  Is that a simplified example?

Should be easy enough using XMLTABLE to extract from the XML.

Can you post the actual expected results you want from that data?

Also provide your Oracle version.

Author

Commented:
<fiveGSubmission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="5GSchemaCS.xsd">

    <reporter listingExchange="XSWX">CS</reporter>
    <reportingDate>2019-09-30</reportingDate>
  </header>
  <assetInflowReport treasuryControl="false" reportID="1">
    <reportScope>CS - Cayman</reportScope>
    <assetProduct>Unencumbered Assets</assetProduct>
    <marketValue unit="USD" unitMultiplier="6" decimals="2" converted="true">10.10</marketValue>
    <lendableValue unit="USD" unitMultiplier="6" decimals="2" converted="true">0</lendableValue>
    <maturityBucket>
      <maturityStart>P30D</maturityStart>
      <maturityEnd>P30D</maturityEnd>
    </maturityBucket>
    <collateralClass>N-2</collateralClass>
  </assetInflowReport>
  <assetInflowReport treasuryControl="false" reportID="2">
    <reportScope>CS - Cayman</reportScope>
    <assetProduct>Unencumbered Assets</assetProduct>
    <marketValue unit="USD" unitMultiplier="6" decimals="2" converted="false">174</marketValue>
    <lendableValue unit="USD" unitMultiplier="6" decimals="2" converted="false">0</lendableValue>
    <maturityBucket>
      <maturityStart>P91D</maturityStart>
      <maturityEnd>P120D</maturityEnd>
    </maturityBucket>
    <collateralClass>L-10</collateralClass>
  </assetInflowReport>
  <assetInflowReport treasuryControl="false" reportID="3">
    <reportScope>CS - Cayman</reportScope>
    <assetProduct>Unencumbered Assets</assetProduct>
    <marketValue unit="USD" unitMultiplier="6" decimals="2" converted="false">9.14</marketValue>
    <lendableValue unit="USD" unitMultiplier="6" decimals="2" converted="false">0</lendableValue>
    <maturityBucket>
      <maturityStart>P121D</maturityStart>
      <maturityEnd>P150D</maturityEnd>
    </maturityBucket>
    <collateralClass>L-10</collateralClass>
  </assetInflowReport>
<foreignExchangeSupplementalReport internal="true" primeBrokerage="false" reportID="75">
    <reportScope>Credit Suisse Securities (USA) LLC</reportScope>
    <foreignExchangeProduct>Forwards and Futures</foreignExchangeProduct>
    <baseMaturityValue unit="CHF" unitMultiplier="6" decimals="2" converted="false">44.99</baseMaturityValue>
    <targetMaturityValue unit="USD" unitMultiplier="6" decimals="2" converted="false">45.11</targetMaturityValue>
    <maturityBucket>
      <maturityStart>P3D</maturityStart>
      <maturityEnd>P3D</maturityEnd>
    </maturityBucket>
    <settlementMechanism>CLS</settlementMechanism>
    <internalCounterparty>CS - EMEA (ex Switzerland)</internalCounterparty>
  </foreignExchangeSupplementalReport>
  <foreignExchangeSupplementalReport internal="true" primeBrokerage="false" reportID="76">
    <reportScope>Credit Suisse Securities (USA) LLC</reportScope>
    <foreignExchangeProduct>Forwards and Futures</foreignExchangeProduct>
    <baseMaturityValue unit="USD" unitMultiplier="6" decimals="2" converted="true">16.73</baseMaturityValue>
    <targetMaturityValue unit="USD" unitMultiplier="6" decimals="2" converted="false">16.77</targetMaturityValue>
    <maturityBucket>
      <maturityStart>P3D</maturityStart>
      <maturityEnd>P3D</maturityEnd>
    </maturityBucket>
    <settlementMechanism>CLS</settlementMechanism>
    <internalCounterparty>CS - EMEA (ex Switzerland)</internalCounterparty>
  </foreignExchangeSupplementalReport>
  <foreignExchangeSupplementalReport internal="true" primeBrokerage="false" reportID="77">
    <reportScope>Credit Suisse Securities (USA) LLC</reportScope>
    <foreignExchangeProduct>Forwards and Futures</foreignExchangeProduct>
    <baseMaturityValue unit="USD" unitMultiplier="6" decimals="2" converted="false">293.85</baseMaturityValue>
    <targetMaturityValue unit="USD" unitMultiplier="6" decimals="2" converted="true">293.83</targetMaturityValue>
    <maturityBucket>
      <maturityStart>P3D</maturityStart>
      <maturityEnd>P3D</maturityEnd>
    </maturityBucket>
    <settlementMechanism>CLS</settlementMechanism>
    <internalCounterparty>CS - EMEA (ex Switzerland)</internalCounterparty>
  </foreignExchangeSupplementalReport>
</fiveGSubmission>

Yes, its simplified example.
I need to retrieve <assetInflowReport> data.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I assume that data is safe to post on a Public website?

I can work with that data but I need your expected results.

I'm assuming you just don't want the assetInflowReport itself in XML format.

Author

Commented:
I want the assetInflowReport itself in XML format
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
First, the XML you posted still isn't valid.  There is not beginning header node but there is an end.  I added one but it may have been in the wrong place.

Still don't have your expected results as you want them so I have to guess.

You also didn't post your Oracle version so I have to assume 12c or above.

I'm guessing you want three rows from that XML?

If so try this:
select assetInflowReportRow
from F5G_R_GEN_XML_AUDIT_DTLS,
	xmltable(
		'/fiveGSubmission'
		passing xmltype(XMLGEN_DOC )
		columns
			assetInflowReport xmltype path 'assetInflowReport'
	)
	,xmltable(
		'assetInflowReport'
		passing assetInflowReport
		columns
			assetInflowReportRow xmltype path '.'
	)
/

Open in new window


My test output is:
ASSETINFLOWREPORTROW
--------------------------------------------------------------------------------
<assetInflowReport treasuryControl="false" reportID="1" xmlns:xsi="http://www.w3
.org/2001/XMLSchema-instance"><reportScope>CS - Cayman</reportScope><assetProduc
t>Unencumbered Assets</assetProduct><marketValue unit="USD" unitMultiplier="6" d
ecimals="2" converted="true">10.10</marketValue><lendableValue unit="USD" unitMu
ltiplier="6" decimals="2" converted="true">0</lendableValue><maturityBucket><mat
urityStart>P30D</maturityStart><maturityEnd>P30D</maturityEnd></maturityBucket><
collateralClass>N-2</collateralClass></assetInflowReport>

<assetInflowReport treasuryControl="false" reportID="2" xmlns:xsi="http://www.w3
.org/2001/XMLSchema-instance"><reportScope>CS - Cayman</reportScope><assetProduc
t>Unencumbered Assets</assetProduct><marketValue unit="USD" unitMultiplier="6" d
ecimals="2" converted="false">174</marketValue><lendableValue unit="USD" unitMul
tiplier="6" decimals="2" converted="false">0</lendableValue><maturityBucket><mat
urityStart>P91D</maturityStart><maturityEnd>P120D</maturityEnd></maturityBucket>
<collateralClass>L-10</collateralClass></assetInflowReport>

<assetInflowReport treasuryControl="false" reportID="3" xmlns:xsi="http://www.w3
.org/2001/XMLSchema-instance"><reportScope>CS - Cayman</reportScope><assetProduc
t>Unencumbered Assets</assetProduct><marketValue unit="USD" unitMultiplier="6" d
ecimals="2" converted="false">9.14</marketValue><lendableValue unit="USD" unitMu
ltiplier="6" decimals="2" converted="false">0</lendableValue><maturityBucket><ma
turityStart>P121D</maturityStart><maturityEnd>P150D</maturityEnd></maturityBucke
t><collateralClass>L-10</collateralClass></assetInflowReport>


3 rows selected.

Open in new window

Author

Commented:
thank you very much. its working perfect

Author

Commented:
Is there any way to get below output from same xml file:  Is there any way to get top root tag?

<fiveGSubmission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="5GSchemaCS.xsd">

    <reporter listingExchange="XSWX">CS</reporter>
    <reportingDate>2019-09-30</reportingDate>
  </header>
  <assetInflowReport treasuryControl="false" reportID="1">
    <reportScope>CS - Cayman</reportScope>
    <assetProduct>Unencumbered Assets</assetProduct>
    <marketValue unit="USD" unitMultiplier="6" decimals="2" converted="true">10.10</marketValue>
    <lendableValue unit="USD" unitMultiplier="6" decimals="2" converted="true">0</lendableValue>
    <maturityBucket>
      <maturityStart>P30D</maturityStart>
      <maturityEnd>P30D</maturityEnd>
    </maturityBucket>
    <collateralClass>N-2</collateralClass>
  </assetInflowReport>
</fiveGSubmission>
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>Is there any way to get top root tag?

Yes.  Everything in XML is retrievable.

What specifically do you want from the root node?

Do you want this in addition to the assetInflowReport?

I would still like the expected results, as you wish to see them.  That will give me a clear picture of exactly what you want so I don't have to guess.

I want to give you the results you want in the way you want them.

Author

Commented:
Yes, along with assetInflowReport, i need root element.

Expected Result as below:

<?xml version="1.0" encoding="UTF-8"?>
<fiveGSubmission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="5GSchemaCS.xsd">
  <header version="1">
    <reporter listingExchange="XSWX">CS</reporter>
    <reportingDate>2019-09-30</reportingDate>
  </header>
<assetInflowReport treasuryControl="false" reportID="1" xmlns:xsi="http://www.w3
.org/2001/XMLSchema-instance"><reportScope>CS - Cayman</reportScope><assetProduc
t>Unencumbered Assets</assetProduct><marketValue unit="USD" unitMultiplier="6" d
ecimals="2" converted="true">10.10</marketValue><lendableValue unit="USD" unitMu
ltiplier="6" decimals="2" converted="true">0</lendableValue><maturityBucket><mat
urityStart>P30D</maturityStart><maturityEnd>P30D</maturityEnd></maturityBucket><
collateralClass>N-2</collateralClass></assetInflowReport>

<assetInflowReport treasuryControl="false" reportID="2" xmlns:xsi="http://www.w3
.org/2001/XMLSchema-instance"><reportScope>CS - Cayman</reportScope><assetProduc
t>Unencumbered Assets</assetProduct><marketValue unit="USD" unitMultiplier="6" d
ecimals="2" converted="false">174</marketValue><lendableValue unit="USD" unitMul
tiplier="6" decimals="2" converted="false">0</lendableValue><maturityBucket><mat
urityStart>P91D</maturityStart><maturityEnd>P120D</maturityEnd></maturityBucket>
<collateralClass>L-10</collateralClass></assetInflowReport>

<assetInflowReport treasuryControl="false" reportID="3" xmlns:xsi="http://www.w3
.org/2001/XMLSchema-instance"><reportScope>CS - Cayman</reportScope><assetProduc
t>Unencumbered Assets</assetProduct><marketValue unit="USD" unitMultiplier="6" d
ecimals="2" converted="false">9.14</marketValue><lendableValue unit="USD" unitMu
ltiplier="6" decimals="2" converted="false">0</lendableValue><maturityBucket><ma
turityStart>P121D</maturityStart><maturityEnd>P150D</maturityEnd></maturityBucke
t><collateralClass>L-10</collateralClass></assetInflowReport>

</fiveGSubmission>

Author

Commented:
actually XML has other tags after assetinflowreport such as <securedInflowReport> and others which we don't want in output. Thank you
Please help to post query.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
So the question really isn't selecting nodes, it is removing everything except a specific node.

There are a couple of ways.

First is deleteXML:
select deletexml(xmltype(XMLGEN_DOC),
   '/fiveGSubmission/*[not(self::header or self::assetInflowReport)]')
from F5G_R_GEN_XML_AUDIT_DTLS;

Open in new window


Another way is XSLT transformation.  I was working on that when you posted your new question so I don't have that example.

Author

Commented:
select deletexml(xmltype(XMLGEN_DOC),''/fiveGSubmission/[not(self::header or self::assetInflowReport)]'') from F5G_R_GEN_XML_AUDIT_DTLS WHERE COB_DATE = '30-sep-2019'   and XMLGEN_VER = 5;

it is showing missing expression at not .
can you send me exact query?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I did provide the exact query based on my test case using the table tab1.

You have two single quotes where mine didn't.
You are also missing a '*' before the [.

You also shouldn't rely on implicit data type conversions.  If COB_DATE  is a date data type, you should explicitly convert the string to a date:  COB_DATE = to_date('30-sep-2019','DD-mon-yyyy')

Try this:
select deletexml(xmltype(XMLGEN_DOC),'/fiveGSubmission/*[not(self::header or self::assetInflowReport)]')
from F5G_R_GEN_XML_AUDIT_DTLS WHERE COB_DATE = '30-sep-2019' and XMLGEN_VER = 5;

Open in new window

Author

Commented:
It is showing below error message:
ORA-21500: internal error code, arguments [%s],[%s],[%s],[%s],[%s],[%s]

Please help on this to proceed
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
What is your Oracle version?  Please provide all 4 numbers (ex. 12.2.0.1).

Author

Commented:
My oracle version is
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I don't have anything that old to test with.

As far back as I can go is 12.2.0.1.

Here is my complete test case.  See if it runs for you as-is:
create table tab1(xmlgen_ver number, cob_date date, xmlgen_doc clob);

declare
	mylob clob;
begin
	mylob := mylob || '<fiveGSubmission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="5GSchemaCS.xsd">';
	mylob := mylob || '  <header>';
	mylob := mylob || '    <reporter listingExchange="XSWX">CS</reporter>';
	mylob := mylob || '    <reportingDate>2019-09-30</reportingDate>';
	mylob := mylob || '  </header>';
	mylob := mylob || '  <assetInflowReport treasuryControl="false" reportID="1">';
	mylob := mylob || '    <reportScope>CS - Cayman</reportScope>';
	mylob := mylob || '    <assetProduct>Unencumbered Assets</assetProduct>';
	mylob := mylob || '    <marketValue unit="USD" unitMultiplier="6" decimals="2" converted="true">10.10</marketValue>';
	mylob := mylob || '    <lendableValue unit="USD" unitMultiplier="6" decimals="2" converted="true">0</lendableValue>';
	mylob := mylob || '    <maturityBucket>';
	mylob := mylob || '      <maturityStart>P30D</maturityStart>';
	mylob := mylob || '      <maturityEnd>P30D</maturityEnd>';
	mylob := mylob || '    </maturityBucket>';
	mylob := mylob || '    <collateralClass>N-2</collateralClass>';
	mylob := mylob || '  </assetInflowReport>';
	mylob := mylob || '  <assetInflowReport treasuryControl="false" reportID="2">';
	mylob := mylob || '    <reportScope>CS - Cayman</reportScope>';
	mylob := mylob || '    <assetProduct>Unencumbered Assets</assetProduct>';
	mylob := mylob || '    <marketValue unit="USD" unitMultiplier="6" decimals="2" converted="false">174</marketValue>';
	mylob := mylob || '    <lendableValue unit="USD" unitMultiplier="6" decimals="2" converted="false">0</lendableValue>';
	mylob := mylob || '    <maturityBucket>';
	mylob := mylob || '      <maturityStart>P91D</maturityStart>';
	mylob := mylob || '      <maturityEnd>P120D</maturityEnd>';
	mylob := mylob || '    </maturityBucket>';
	mylob := mylob || '    <collateralClass>L-10</collateralClass>';
	mylob := mylob || '  </assetInflowReport>';
	mylob := mylob || '  <assetInflowReport treasuryControl="false" reportID="3">';
	mylob := mylob || '    <reportScope>CS - Cayman</reportScope>';
	mylob := mylob || '    <assetProduct>Unencumbered Assets</assetProduct>';
	mylob := mylob || '    <marketValue unit="USD" unitMultiplier="6" decimals="2" converted="false">9.14</marketValue>';
	mylob := mylob || '    <lendableValue unit="USD" unitMultiplier="6" decimals="2" converted="false">0</lendableValue>';
	mylob := mylob || '    <maturityBucket>';
	mylob := mylob || '      <maturityStart>P121D</maturityStart>';
	mylob := mylob || '      <maturityEnd>P150D</maturityEnd>';
	mylob := mylob || '    </maturityBucket>';
	mylob := mylob || '    <collateralClass>L-10</collateralClass>';
	mylob := mylob || '  </assetInflowReport>';
	mylob := mylob || '<foreignExchangeSupplementalReport internal="true" primeBrokerage="false" reportID="75">';
	mylob := mylob || '    <reportScope>Credit Suisse Securities (USA) LLC</reportScope>';
	mylob := mylob || '    <foreignExchangeProduct>Forwards and Futures</foreignExchangeProduct>';
	mylob := mylob || '    <baseMaturityValue unit="CHF" unitMultiplier="6" decimals="2" converted="false">44.99</baseMaturityValue>';
	mylob := mylob || '    <targetMaturityValue unit="USD" unitMultiplier="6" decimals="2" converted="false">45.11</targetMaturityValue>';
	mylob := mylob || '    <maturityBucket>';
	mylob := mylob || '      <maturityStart>P3D</maturityStart>';
	mylob := mylob || '      <maturityEnd>P3D</maturityEnd>';
	mylob := mylob || '    </maturityBucket>';
	mylob := mylob || '    <settlementMechanism>CLS</settlementMechanism>';
	mylob := mylob || '    <internalCounterparty>CS - EMEA (ex Switzerland)</internalCounterparty>';
	mylob := mylob || '  </foreignExchangeSupplementalReport>';
	mylob := mylob || '  <foreignExchangeSupplementalReport internal="true" primeBrokerage="false" reportID="76">';
	mylob := mylob || '    <reportScope>Credit Suisse Securities (USA) LLC</reportScope>';
	mylob := mylob || '    <foreignExchangeProduct>Forwards and Futures</foreignExchangeProduct>';
	mylob := mylob || '    <baseMaturityValue unit="USD" unitMultiplier="6" decimals="2" converted="true">16.73</baseMaturityValue>';
	mylob := mylob || '    <targetMaturityValue unit="USD" unitMultiplier="6" decimals="2" converted="false">16.77</targetMaturityValue>';
	mylob := mylob || '    <maturityBucket>';
	mylob := mylob || '      <maturityStart>P3D</maturityStart>';
	mylob := mylob || '      <maturityEnd>P3D</maturityEnd>';
	mylob := mylob || '    </maturityBucket>';
	mylob := mylob || '    <settlementMechanism>CLS</settlementMechanism>';
	mylob := mylob || '    <internalCounterparty>CS - EMEA (ex Switzerland)</internalCounterparty>';
	mylob := mylob || '  </foreignExchangeSupplementalReport>';
	mylob := mylob || '  <foreignExchangeSupplementalReport internal="true" primeBrokerage="false" reportID="77">';
	mylob := mylob || '    <reportScope>Credit Suisse Securities (USA) LLC</reportScope>';
	mylob := mylob || '    <foreignExchangeProduct>Forwards and Futures</foreignExchangeProduct>';
	mylob := mylob || '    <baseMaturityValue unit="USD" unitMultiplier="6" decimals="2" converted="false">293.85</baseMaturityValue>';
	mylob := mylob || '    <targetMaturityValue unit="USD" unitMultiplier="6" decimals="2" converted="true">293.83</targetMaturityValue>';
	mylob := mylob || '    <maturityBucket>';
	mylob := mylob || '      <maturityStart>P3D</maturityStart>';
	mylob := mylob || '      <maturityEnd>P3D</maturityEnd>';
	mylob := mylob || '    </maturityBucket>';
	mylob := mylob || '    <settlementMechanism>CLS</settlementMechanism>';
	mylob := mylob || '    <internalCounterparty>CS - EMEA (ex Switzerland)</internalCounterparty>';
	mylob := mylob || '  </foreignExchangeSupplementalReport>';
	mylob := mylob || '</fiveGSubmission>';

	insert into tab1 values(5, to_date('30-sep-2019','DD-mon-yyyy'), mylob);
	commit;
end;
/


select deletexml(xmltype(XMLGEN_DOC),'/fiveGSubmission/*[not(self::header or self::assetInflowReport)]')
from tab1 WHERE COB_DATE = to_date('30-sep-2019','DD-mon-yyyy') and XMLGEN_VER = 5;

Open in new window

Author

Commented:
It is showing same error message for this too...

ORA-21500: internal error code, arguments [%s],[%s],[%s],[%s],[%s],[%s]

Please help on this to proceed
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
How are you executing the test code?  sqlplus, SQL Developer, ???

Can you try using sqlplus?

Author

Commented:
I have only PL/SQL developer in my system.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
It might be a bug in the 12.1 database but I would like to eliminate any tool.  Since I don't have 12.1 available, I cannot test on my end to see.

Can you get the DBA to try the test from sqlplus on the server?

If not, can you grab the Instant Client and sqlplus and try?

You will need two downloads:
https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html

Basic Package
SQL*Plus Package

Unzip both to the same folder.  Manually create a network/admin folder under that and copy a tnsnames.ora file in there and run.  Nothing to install.

Author

Commented:
Tried in SQL Plus and its working fine. but when I added this query in procedure it is treating all the lines after * as commented lines. Please find attached file on this.
2019-12-18_14-43-10.jpg

Author

Commented:
select deletexml(xmltype(XMLGEN_DOC),'/fiveGSubmission/*[not(self::header or self::assetInflowReport)]').getclobval() from tab3 WHERE COB_DATE = to_date('30-sep-2019','DD-mon-yyyy') and XMLGEN_VER = 5;

above query is working fine in PL/SQL Developer. I added getclobval() then working fine in current oracle version.

but when I added in stored proc it is treating all the lines after * as commented lines. Please find attached file on this.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
You need to either use "q" syntax or add the two single quotes when a string literal has a single quote.

	v_selectSQL := 'select deletexml(xmltype(XMLGEN_DOC),''/fiveGSubmission/*[not(self::header or self::assetInflowReport)]'').getclobval()
	from tab1 WHERE COB_DATE = to_date(''30-sep-2019'',''DD-mon-yyyy'') and XMLGEN_VER = 5';

Open in new window

Author

Commented:
IT worked fine. Thank you very much !!!