retrieving clob xolumn (containg soap xml reponse) from oracle into c#

I have soap xml data in a clob type column of oracle table.
I need to fetch it in C# asp.net webpage.How to achieve this?
Once received, i am planning to parse the xml to get some specific node values out for further processing.
DevildibAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Just like your last question, the options haven't changed:
http://www.experts-exchange.com/questions/28689344/Retrieving-specific-xml-tags-from-clob-type-oracle-table-column-containing-XML-in-C-gridview.html

It depends on many variables.  Size of stored XML, how many nodes do you need from it, etc...

I again would probably parse on the database side using XMLTABLE and only return the values needed.

Again:  For us to provide a working example, we'll need sample data and expected results.
0
DevildibAuthor Commented:
Hi Slighttwv,

Below is the complete XML i have in oracle table clob type column for one row.
Now i need  4 tags extracted  say,

1] <dsc:TrfrCcyAmt Ccy="USD">12345.00</dsc:TrfrCcyAmt>  ====  to be extracted as  12345.00
2] <dsc:FeeCcyAmt Ccy="USD">0.00</dsc:FeeCcyAmt>    ====  to be extracted as  0.00
3] <dsc:TaxAmt>
             dsc:Amt Ccy="USD">0.00</dsc:Amt>   ====  to be extracted as  0.00
      </dsc:TaxAmt>
4]  <dsc:TotalCcyAmt Ccy="USD">12345.00</dsc:TotalCcyAmt>  ====  to be extracted as  12345.00


Please note that performance should not get hit. I may have to extract the tags from say 2000 such xmls (2000 rows) at one time.I hope you have all data now.Kindly provide a parsing logic in oracle that can manage this amount of data.

COMPLTE XML for one row:
-------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<tns:Envelope xmlns:acc="urn:com:COMPANY:accounts" xmlns:bme="http://ibbus.COMP.com/2009/bus-message-envelope" xmlns:dsc="urn:com:COMPANY:remittance" xmlns:enrich="urn:com:COMPANY:FINALHUB:enrichment" xmlns:fxpm="urn:com:COMPANY:usrouter:fxpm" xmlns:isoPmtStsRpt="urn:iso:std:iso:20022:tech:xsd:pacs.002.001.03" xmlns:ns11="urn:com:COMPANY:FINALHUB:eodp" xmlns:ns7="urn:com:COMPANY:FINALHUB:bankrefdata" xmlns:ns8="urn:com:COMPANY:usrouter:tms" xmlns:pain="urn:iso:std:iso:20022:tech:xsd:pain.001.001.02" xmlns:phub="urn:com:COMPANY:FINALHUB" xmlns:tns="http://www.w3.org/2003/05/soap-envelope" bme:version="1-0">
       <tns:Header>
              <bme:Timestamp>2015-03-18T10:19:20-04:00</bme:Timestamp>
              <bme:MessageId>783351</bme:MessageId>
              <bme:RefToMessageId>A7ETEWLP0012</bme:RefToMessageId>
              <bme:From type="http://ibml.COMP.com/coding-scheme/bmesystem">FINALHUB</bme:From>
              <bme:To type="http://ibml.COMP.com/coding-scheme/bmesystem">PRPC</bme:To>
              <bme:PayloadInfo>
                     <bme:PartInfo>
                           <bme:Schema location="FINALHUB-v1-3.xsd" namespace="urn:com:COMPANY:FINALHUB" version="1-1"/>
                           <bme:MessageStandard>TSS-Legacy</bme:MessageStandard>
                           <bme:MessageType>DSCLARCH</bme:MessageType>
                     </bme:PartInfo>
              </bme:PayloadInfo>
       </tns:Header>
       <tns:Body>
              <phub:Message version="1-3">
                     <phub:msgHdr>
                           <phub:MessageType>
                                  <phub:MajorType>DSCLARCH</phub:MajorType>
                           </phub:MessageType>
                           <phub:MessageFormat>
                                  <phub:Format>XML</phub:Format>
                           </phub:MessageFormat>
                           <phub:Sender>FINALHUB</phub:Sender>
                           <phub:Receiver>PRPC</phub:Receiver>
                           <phub:Originator>eServe</phub:Originator>
                           <phub:SendDateTime>2015-03-18T10:19:20-04:00</phub:SendDateTime>
                     </phub:msgHdr>
                     <phub:Body>
                           <phub:Disclosures version="1-0">
                                  <dsc:disclosure>
                                         <dsc:DsclHdr>
                                                <dsc:RmtRefId>A7ETEWLP0012</dsc:RmtRefId>
                                                <dsc:ValDt>2015-02-25-04:00</dsc:ValDt>
                                                <dsc:XchgRateInf>
                                                       <dsc:XchgRate>1</dsc:XchgRate>
                                                       <dsc:RateTp>AGRD</dsc:RateTp>
                                                </dsc:XchgRateInf>
                                         </dsc:DsclHdr>
                                         <dsc:DsclChrgInf>
                                                <dsc:ChrgBr>SHAR</dsc:ChrgBr>
                                                <dsc:SndChrgs>
                                                       <dsc:TrfrCcyAmt Ccy="USD">12345.00</dsc:TrfrCcyAmt>
                                                       <dsc:FeeCcyAmt Ccy="USD">0.00</dsc:FeeCcyAmt>
                                                       <dsc:TaxAmt>
                                                              <dsc:Amt Ccy="USD">0.00</dsc:Amt>
                                                       </dsc:TaxAmt>
                                                       <dsc:TotalCcyAmt Ccy="USD">12345.00</dsc:TotalCcyAmt>
                                                </dsc:SndChrgs>
                                                <dsc:BeneChrgs>
                                                       <dsc:TrfrCcyAmt Ccy="USD">12345.00</dsc:TrfrCcyAmt>
                                                       <dsc:FeeCcyAmt Ccy="USD" Estimated="false">9.85</dsc:FeeCcyAmt>
                                                       <dsc:TotalCcyAmt Ccy="USD" Estimated="false">12335.15</dsc:TotalCcyAmt>
                                                </dsc:BeneChrgs>
                                         </dsc:DsclChrgInf>
                                         <dsc:BeneInstAddr>
                                                <dsc:Id>
                                                       <dsc:BIC>BARCGB22GCM</dsc:BIC>
                                                </dsc:Id>
                                                <dsc:Nme>BARCLAYS BANK PLC</dsc:Nme>
                                                <dsc:Addr>
                                                       <dsc:City>LONDON</dsc:City>
                                                       <dsc:Country>
                                                              <dsc:CountryCode>GB</dsc:CountryCode>
                                                       </dsc:Country>
                                                </dsc:Addr>
                                         </dsc:BeneInstAddr>
                                         <dsc:DsclmrInfo lang="English">
                                                <dsc:Ustrd><![CDATA[The recipient may receive less due to fees charged by the recipient's bank and foreign taxes.]]></dsc:Ustrd>
                                                <dsc:Ustrd><![CDATA[In the event you provide an incorrect account number or recipient institution identifier, you could lose the transfer amount.                                                                           ]]></dsc:Ustrd>
                                                <dsc:Ustrd><![CDATA[You can cancel for a full refund within 30 minutes of payment, unless the funds have been picked up or deposited. You have a right to dispute errors in your transactions.                              ]]></dsc:Ustrd>
                                                <dsc:Ustrd><![CDATA[If you think there is an error, contact us within 180 days at 212-718-9100 of http://www.bessemertrust.com You can also contact us for a written explanation of your rights.                            ]]></dsc:Ustrd>
                                                <dsc:Ustrd><![CDATA[For questions or complaints about Bessemer Trust contact: Consumer Financial Protection Bureau (855) 411-2372 (TTY/TDD) (855)729-2372 http://www.consumerfinance.gov.]]></dsc:Ustrd>
                                         </dsc:DsclmrInfo>
                                         <dsc:DsclmrInfo lang="Spanish">
                                                <dsc:Ustrd><![CDATA[El beneficiario podría recibir menos dinero debido a las comisiones cobradas por el banco del beneficiario e impuestos extranjeros.]]></dsc:Ustrd>
                                                <dsc:Ustrd><![CDATA[Puede cancelar el envío y recibir un reembolso total dentro de 30 minutos de haber realizado el pago, a no ser que los fondos hayan sido recogidos o depositados.                                       ]]></dsc:Ustrd>
                                                <dsc:Ustrd><![CDATA[Los fondos pueden recibirse previo a la fecha indicada arriba.                                                                                                                                          ]]></dsc:Ustrd>
                                                <dsc:Ustrd><![CDATA[Números de cuenta o código SWIFT BIC incorrectos pueden causar que el remitente pierda la cantidad de la transferencia. Verifique esta divulgación cuidadosamente.                                      ]]></dsc:Ustrd>
                                                <dsc:Ustrd><![CDATA[Usted tiene el derecho de discutir errores en su transacción. Si cree que hay un error, contáctenos dentro de 180 días al 1-877-766-4999 o www.scotiabankpr.com.                                        ]]></dsc:Ustrd>
                                                <dsc:Ustrd><![CDATA[También puede contactarnos para obtener una explicación escrita de sus derechos.                                                                                                                        ]]></dsc:Ustrd>
                                         </dsc:DsclmrInfo>
                                         <dsc:CFPBCntInf>
                                                <dsc:Nme>Consumer Financial Protection Bureau</dsc:Nme>
                                                <dsc:Addr>
                                                       <dsc:AddressLine>1-855-729-2372 (TTY/TDD)  </dsc:AddressLine>
                                                       <dsc:Country>
                                                              <dsc:CountryCode>US</dsc:CountryCode>
                                                              <dsc:CountryName> </dsc:CountryName>
                                                       </dsc:Country>
                                                       <dsc:Tel>855-411-2372</dsc:Tel>
                                                       <dsc:Url>www.consumerfinance.gov</dsc:Url>
                                                </dsc:Addr>
                                         </dsc:CFPBCntInf>
                                         <dsc:RegAgncyCntInf/>
                                         <dsc:InitFinInstInf/>
                                         <dsc:OrgRmtInfo version="0-8">
                                                <dsc:RmtHdr>
                                                       <dsc:CreDtTm>2015-02-18T10:19:19.860-04:00</dsc:CreDtTm>
                                                       <dsc:NbOfTxs>1</dsc:NbOfTxs>
                                                       <dsc:DsclmrLang>Spanish</dsc:DsclmrLang>
                                                       <dsc:PayInstOvrde>
                                                              <dsc:FxRateOvrde>false</dsc:FxRateOvrde>
                                                              <dsc:OurFeeOvrde>FALSE</dsc:OurFeeOvrde>
                                                              <dsc:ValueDteOvrde>false</dsc:ValueDteOvrde>
                                                       </dsc:PayInstOvrde>
                                                </dsc:RmtHdr>
                                                <dsc:SndInf>
                                                       <dsc:DbtAcct>
                                                              <dsc:Id>00000000982492627</dsc:Id>
                                                       </dsc:DbtAcct>
                                                       <dsc:SndNme>BARCGB22GCM</dsc:SndNme>
                                                       <dsc:SndAddr>
                                                              <dsc:AddressLine>BARCGB22GCM</dsc:AddressLine>
                                                              <dsc:City>BARCGB22GCM</dsc:City>
                                                              <dsc:Country>
                                                                     <dsc:CountryCode>US</dsc:CountryCode>
                                                                     <dsc:CountryName>United States</dsc:CountryName>
                                                              </dsc:Country>
                                                              <dsc:Zip>1111111111</dsc:Zip>
                                                       </dsc:SndAddr>
                                                       <dsc:DbtCcyAndAmt Ccy="USD">12345</dsc:DbtCcyAndAmt>
                                                       <dsc:CcyOfTrf>USD</dsc:CcyOfTrf>
                                                </dsc:SndInf>
                                                <dsc:BeneInf>
                                                       <dsc:BeneNme>BARCGB22GCM</dsc:BeneNme>
                                                       <dsc:BeneAddr>
                                                              <dsc:Country>
                                                                     <dsc:CountryCode>US</dsc:CountryCode>
                                                                     <dsc:CountryName>United States</dsc:CountryName>
                                                              </dsc:Country>
                                                       </dsc:BeneAddr>
                                                       <dsc:CrdtCcyAndAmt Ccy="USD">0</dsc:CrdtCcyAndAmt>
                                                       <dsc:XchgRateInf>
                                                              <dsc:RateTp>AGRD</dsc:RateTp>
                                                       </dsc:XchgRateInf>
                                                       <dsc:BeneBank>
                                                              <dsc:BankId>
                                                                     <dsc:BIC>BARCGB22GCM</dsc:BIC>
                                                              </dsc:BankId>
                                                       </dsc:BeneBank>
                                                </dsc:BeneInf>
                                                <dsc:RmtFeeInf>
                                                       <dsc:TrfrFeeCcyAmt Ccy="USD">0</dsc:TrfrFeeCcyAmt>
                                                       <dsc:TrfrTaxInf>
                                                              <dsc:Amt Ccy="USD">0</dsc:Amt>
                                                       </dsc:TrfrTaxInf>
                                                </dsc:RmtFeeInf>
                                                <dsc:AddtlRmtInf>
                                                       <dsc:Ustrd>UserId: U0914-62933 | Corpid: CHASESRV | UserName: QATestone  QAtestone</dsc:Ustrd>
                                                </dsc:AddtlRmtInf>
                                         </dsc:OrgRmtInfo>
                                  </dsc:disclosure>
                           </phub:Disclosures>
                     </phub:Body>
              </phub:Message>
       </tns:Body>
</tns:Envelope>

Open in new window

0
slightwv (䄆 Netminder) Commented:
Thanks for the XML.  Give me a minute...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
Which parent node do you want for these 4 values?

The ones from SndChrgs or BeneChrgs?
0
DevildibAuthor Commented:
To add to the above , oracle table structure that i have for your reference is:

A varchar,B varchar, C varchar, D varchar, E clob(this column includes the xml).

Now, the already in place SP query in oracle that was fetching normal varchar columns until now is something like :

 OPEN RefCursor FOR
 SELECT A,B,C,D
                FROM payment_disclosures a
                WHERE
.... where conditions.......
                AND rownum <  2000
                ORDER BY A;


I tried using extarctvalue function to get clob data extracts along with above query as :

 OPEN RefCursor FOR
 SELECT A,B,C,D, extractvalue(XMLType(E),
      '//dsc:BeneInstAddr/dsc:Id/dsc:BIC/text()',
  'xmlns:dsc="urn:com:Company:remittance"') AS XMLDATA
                FROM payment_disclosures a
                WHERE
.... where conditions.......
                AND rownum <  2000
                ORDER BY A;
But with mere 600 records(rows with 600 diff XML's), this query is taking around 18 secs.
I need an efficient way to query around 2k or 3k records
0
slightwv (䄆 Netminder) Commented:
>>I tried using extarctvalue function

extractvalue is deprecated.  Don't use it.

As far as the 'expected' performance:  It will be what it will be.  I can get you the data but cannot guarantee performance.

People write entire books and make entire careers out of tuning Oracle.

If I don't get an answer about which child nodes, I'll guess and you can tweak it.
0
sdstuberCommented:
Something like this?


SELECT x.*
  FROM yourtable,
       XMLTABLE(
           xmlnamespaces(
               'http://www.w3.org/2003/05/soap-envelope' AS "tns",
               'urn:com:COMPANY:accounts' AS "acc",
               'http://ibbus.COMP.com/2009/bus-message-envelope' AS "bme",
               'urn:com:COMPANY:remittance' AS "dsc",
               'urn:com:COMPANY:FINALHUB:enrichment' AS "enrich",
               'urn:com:COMPANY:usrouter:fxpm' AS "fxpm",
               'urn:iso:std:iso:20022:tech:xsd:pacs.002.001.03' AS "isoPmtStsRpt",
               'urn:com:COMPANY:FINALHUB:eodp' AS "ns11",
               'urn:com:COMPANY:FINALHUB:bankrefdata' AS "ns7",
               'urn:com:COMPANY:usrouter:tms' AS "ns8",
               'urn:iso:std:iso:20022:tech:xsd:pain.001.001.02' AS "pain",
               'urn:com:COMPANY:FINALHUB' AS "phub"
           ),
           '//dsc:SndChrgs'
           PASSING yourxml
           columns
           trfrccamt number path './dsc:TrfrCcyAmt',
           FeeCcyAmt number path './dsc:FeeCcyAmt',
           Amt number path './dsc:TaxAmt/dsc:Amt',
           TotalCcyAmt number path './dsc:TotalCcyAmt'
       ) x;
0
sdstuberCommented:
The xmltable version is more compact syntax, but it may be slightly expensive since it's creates the table structure and then extracts from that.  Alternatively, you can extract the individual pieces directly.  It's more calls, but direct.  So iffy as to which will be best over all.

Test on your own system to confirm.


SELECT XMLQUERY(
           'declare namespace tns="http://www.w3.org/2003/05/soap-envelope"; (: :)
           declare namespace acc="urn:com:COMPANY:accounts"; (: :)
           declare namespace bme="http://ibbus.COMP.com/2009/bus-message-envelope"; (: :)
           declare namespace dsc="urn:com:COMPANY:remittance"; (: :)
           declare namespace enrich="urn:com:COMPANY:FINALHUB:enrichment"; (: :)
           declare namespace fxpm="urn:com:COMPANY:usrouter:fxpm"; (: :)
           declare namespace isoPmtStsRpt="urn:iso:std:iso:20022:tech:xsd:pacs.002.001.03"; (: :)
           declare namespace ns11="urn:com:COMPANY:FINALHUB:eodp"; (: :)
           declare namespace ns7="urn:com:COMPANY:FINALHUB:bankrefdata"; (: :)
           declare namespace ns8="urn:com:COMPANY:usrouter:tms"; (: :)
           declare namespace pain="urn:iso:std:iso:20022:tech:xsd:pain.001.001.02"; (: :)
           declare namespace phub="urn:com:COMPANY:FINALHUB"; (: :)
        //dsc:SndChrgs/dsc:TrfrCcyAmt/text()'
           PASSING yourxml RETURNING CONTENT
       )
           trfrccamt,
       XMLQUERY(
           'declare namespace tns="http://www.w3.org/2003/05/soap-envelope"; (: :)
           declare namespace acc="urn:com:COMPANY:accounts"; (: :)
           declare namespace bme="http://ibbus.COMP.com/2009/bus-message-envelope"; (: :)
           declare namespace dsc="urn:com:COMPANY:remittance"; (: :)
           declare namespace enrich="urn:com:COMPANY:FINALHUB:enrichment"; (: :)
           declare namespace fxpm="urn:com:COMPANY:usrouter:fxpm"; (: :)
           declare namespace isoPmtStsRpt="urn:iso:std:iso:20022:tech:xsd:pacs.002.001.03"; (: :)
           declare namespace ns11="urn:com:COMPANY:FINALHUB:eodp"; (: :)
           declare namespace ns7="urn:com:COMPANY:FINALHUB:bankrefdata"; (: :)
           declare namespace ns8="urn:com:COMPANY:usrouter:tms"; (: :)
           declare namespace pain="urn:iso:std:iso:20022:tech:xsd:pain.001.001.02"; (: :)
           declare namespace phub="urn:com:COMPANY:FINALHUB"; (: :)
        //dsc:SndChrgs/dsc:FeeCcyAmt/text()'
           PASSING yourxml RETURNING CONTENT
       )
           feeccyamt,
       XMLQUERY(
           'declare namespace tns="http://www.w3.org/2003/05/soap-envelope"; (: :)
           declare namespace acc="urn:com:COMPANY:accounts"; (: :)
           declare namespace bme="http://ibbus.COMP.com/2009/bus-message-envelope"; (: :)
           declare namespace dsc="urn:com:COMPANY:remittance"; (: :)
           declare namespace enrich="urn:com:COMPANY:FINALHUB:enrichment"; (: :)
           declare namespace fxpm="urn:com:COMPANY:usrouter:fxpm"; (: :)
           declare namespace isoPmtStsRpt="urn:iso:std:iso:20022:tech:xsd:pacs.002.001.03"; (: :)
           declare namespace ns11="urn:com:COMPANY:FINALHUB:eodp"; (: :)
           declare namespace ns7="urn:com:COMPANY:FINALHUB:bankrefdata"; (: :)
           declare namespace ns8="urn:com:COMPANY:usrouter:tms"; (: :)
           declare namespace pain="urn:iso:std:iso:20022:tech:xsd:pain.001.001.02"; (: :)
           declare namespace phub="urn:com:COMPANY:FINALHUB"; (: :)
        //dsc:SndChrgs/dsc:TaxAmt/dsc:Amt/text()'
           PASSING yourxml RETURNING CONTENT
       )
           amt,
       XMLQUERY(
           'declare namespace tns="http://www.w3.org/2003/05/soap-envelope"; (: :)
           declare namespace acc="urn:com:COMPANY:accounts"; (: :)
           declare namespace bme="http://ibbus.COMP.com/2009/bus-message-envelope"; (: :)
           declare namespace dsc="urn:com:COMPANY:remittance"; (: :)
           declare namespace enrich="urn:com:COMPANY:FINALHUB:enrichment"; (: :)
           declare namespace fxpm="urn:com:COMPANY:usrouter:fxpm"; (: :)
           declare namespace isoPmtStsRpt="urn:iso:std:iso:20022:tech:xsd:pacs.002.001.03"; (: :)
           declare namespace ns11="urn:com:COMPANY:FINALHUB:eodp"; (: :)
           declare namespace ns7="urn:com:COMPANY:FINALHUB:bankrefdata"; (: :)
           declare namespace ns8="urn:com:COMPANY:usrouter:tms"; (: :)
           declare namespace pain="urn:iso:std:iso:20022:tech:xsd:pain.001.001.02"; (: :)
           declare namespace phub="urn:com:COMPANY:FINALHUB"; (: :)
        //dsc:SndChrgs/dsc:TotalCcyAmt/text()'
           PASSING yourxml RETURNING CONTENT
       )
           totalccyamt
  FROM yourtable;
0
sdstuberCommented:
not all of the namespaces are necessary for the parsing
so, you can simplify with


SELECT x.*
FROM yourtable,
    XMLTABLE(
        xmlnamespaces('urn:com:COMPANY:remittance' AS "dsc"), '//dsc:SndChrgs'
        PASSING yourxml
        COLUMNS trfrccamt NUMBER PATH './dsc:TrfrCcyAmt',
                feeccyamt NUMBER PATH './dsc:FeeCcyAmt',
                amt NUMBER PATH './dsc:TaxAmt/dsc:Amt',
                totalccyamt NUMBER PATH './dsc:TotalCcyAmt'
    ) x;

Open in new window


SELECT XMLQUERY(
           'declare namespace dsc="urn:com:COMPANY:remittance";           
        //dsc:SndChrgs/dsc:TrfrCcyAmt/text()'
           PASSING yourxml RETURNING CONTENT
       )
           trfrccamt,
       XMLQUERY(
           'declare namespace dsc="urn:com:COMPANY:remittance";           
        //dsc:SndChrgs/dsc:FeeCcyAmt/text()'
           PASSING yourxml RETURNING CONTENT
       )
           feeccyamt,
       XMLQUERY(
           'declare namespace dsc="urn:com:COMPANY:remittance";           
        //dsc:SndChrgs/dsc:TaxAmt/dsc:Amt/text()'
           PASSING yourxml RETURNING CONTENT
       )
           amt,
       XMLQUERY(
           'declare namespace dsc="urn:com:COMPANY:remittance";           
            //dsc:SndChrgs/dsc:TotalCcyAmt/text()'
           PASSING yourxml RETURNING CONTENT
       )
           totalccyamt
  FROM yourtable;

Open in new window


or another alternate...

SELECT XMLQUERY(
           'declare namespace dsc="urn:com:COMPANY:remittance"; dsc:SndChrgs/dsc:TrfrCcyAmt/text()'
           PASSING sndchrgs RETURNING CONTENT
       )
           trfrccyamt,
       XMLQUERY(
           'declare namespace dsc="urn:com:COMPANY:remittance"; dsc:SndChrgs/dsc:TrfrCcyAmt/text()'
           PASSING sndchrgs RETURNING CONTENT
       )
           feeccyamt,
       XMLQUERY(
           'declare namespace dsc="urn:com:COMPANY:remittance"; dsc:SndChrgs/dsc:TaxAmt/dsc:Amt/text()'
           PASSING sndchrgs RETURNING CONTENT
       )
           amt,
       XMLQUERY(
           'declare namespace dsc="urn:com:COMPANY:remittance"; dsc:SndChrgs/dsc:TotalCcyAmt/text()'
           PASSING sndchrgs RETURNING CONTENT
       )
           totalccyamt
  FROM (SELECT XMLQUERY('declare namespace dsc="urn:com:COMPANY:remittance";          
                          //dsc:SndChrgs'
                   PASSING yourxml RETURNING CONTENT
               )
                   sndchrgs
          FROM yourtable);

Open in new window

0
sdstuberCommented:
and one more option -- instead of using wildcard searching //,  you can use an explicit path if the location is always known.  If the location is known, this is preferable as it saves time on parsing through dead end branches and goes directly to where it needs.

Going directly, the variations above would look something like this...
Note, you do need to specify more namespaces this way


SELECT XMLQUERY(
          'declare namespace tns="http://www.w3.org/2003/05/soap-envelope";         
           declare namespace dsc="urn:com:COMPANY:remittance"; 
           declare namespace phub="urn:com:COMPANY:FINALHUB";
            /tns:Envelope/tns:Body/phub:Message/phub:Body/phub:Disclosures/dsc:disclosure/dsc:DsclChrgInf/dsc:SndChrgs/dsc:TrfrCcyAmt/text()'
           PASSING yourxml RETURNING CONTENT
       )
           trfrccamt,
       XMLQUERY(
           'declare namespace tns="http://www.w3.org/2003/05/soap-envelope";         
           declare namespace dsc="urn:com:COMPANY:remittance"; 
           declare namespace phub="urn:com:COMPANY:FINALHUB";
            /tns:Envelope/tns:Body/phub:Message/phub:Body/phub:Disclosures/dsc:disclosure/dsc:DsclChrgInf/dsc:SndChrgs/dsc:FeeCcyAmt/text()'
           PASSING yourxml RETURNING CONTENT
       )
           feeccyamt,
       XMLQUERY(
          'declare namespace tns="http://www.w3.org/2003/05/soap-envelope";         
           declare namespace dsc="urn:com:COMPANY:remittance"; 
           declare namespace phub="urn:com:COMPANY:FINALHUB";
            /tns:Envelope/tns:Body/phub:Message/phub:Body/phub:Disclosures/dsc:disclosure/dsc:DsclChrgInf/dsc:SndChrgs/dsc:TaxAmt/dsc:Amt/text()'
           PASSING yourxml RETURNING CONTENT
       )
           amt,
       XMLQUERY(
           'declare namespace tns="http://www.w3.org/2003/05/soap-envelope";          
           declare namespace dsc="urn:com:COMPANY:remittance"; 
           declare namespace phub="urn:com:COMPANY:FINALHUB";
            /tns:Envelope/tns:Body/phub:Message/phub:Body/phub:Disclosures/dsc:disclosure/dsc:DsclChrgInf/dsc:SndChrgs/dsc:TotalCcyAmt/text()'
           PASSING yourxml RETURNING CONTENT
       )
           totalccyamt
  FROM yourtable;

Open in new window


SELECT x.*
FROM yourtable,
    XMLTABLE(
        xmlnamespaces(
            'http://www.w3.org/2003/05/soap-envelope' AS "tns",
            'urn:com:COMPANY:remittance' AS "dsc",
            'urn:com:COMPANY:FINALHUB' AS "phub"
        ),
        '/tns:Envelope/tns:Body/phub:Message/phub:Body/phub:Disclosures/dsc:disclosure/dsc:DsclChrgInf/dsc:SndChrgs'
        PASSING yourxml
        COLUMNS trfrccamt NUMBER PATH './dsc:TrfrCcyAmt',
                feeccyamt NUMBER PATH './dsc:FeeCcyAmt',
                amt NUMBER PATH './dsc:TaxAmt/dsc:Amt',
                totalccyamt NUMBER PATH './dsc:TotalCcyAmt'
    ) x;

Open in new window



SELECT XMLQUERY(
           'declare namespace dsc="urn:com:COMPANY:remittance"; dsc:SndChrgs/dsc:TrfrCcyAmt/text()'
           PASSING sndchrgs RETURNING CONTENT
       )
           trfrccyamt,
       XMLQUERY(
           'declare namespace dsc="urn:com:COMPANY:remittance"; dsc:SndChrgs/dsc:TrfrCcyAmt/text()'
           PASSING sndchrgs RETURNING CONTENT
       )
           feeccyamt,
       XMLQUERY(
           'declare namespace dsc="urn:com:COMPANY:remittance"; dsc:SndChrgs/dsc:TaxAmt/dsc:Amt/text()'
           PASSING sndchrgs RETURNING CONTENT
       )
           amt,
       XMLQUERY(
           'declare namespace dsc="urn:com:COMPANY:remittance"; dsc:SndChrgs/dsc:TotalCcyAmt/text()'
           PASSING sndchrgs RETURNING CONTENT
       )
           totalccyamt
  FROM (SELECT XMLQUERY('declare namespace tns="http://www.w3.org/2003/05/soap-envelope";         
           declare namespace dsc="urn:com:COMPANY:remittance"; 
           declare namespace phub="urn:com:COMPANY:FINALHUB";
            /tns:Envelope/tns:Body/phub:Message/phub:Body/phub:Disclosures/dsc:disclosure/dsc:DsclChrgInf/dsc:SndChrgs'

                   PASSING yourxml RETURNING CONTENT
               )
                   sndchrgs
          FROM yourtable);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DevildibAuthor Commented:
getting error with few queries...say the last one which you posted:I simply replaced yourtable with my table name and yourxml with the clob type column name

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00209: PI names starting with XML are reserved
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 254
ORA-06512: at line 1
31011. 00000 -  "XML parsing failed"
*Cause:    XML parser returned an error while trying to parse the document.
*Action:   Check if the document to be parsed is valid.
0
DevildibAuthor Commented:
above error is when i wrap clob column within xmltype like xmltype(yourxml)...if i donot wrap it like that, i get error as below: ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Error at Line: 20 Column: 3   where line 20 points to

          line 19 PASSING yourxml
          line 20 columns
          line 21 trfrccamt number path './dsc:TrfrCcyAmt',
0
DevildibAuthor Commented:
Any update on the error??
0
sdstuberCommented:
Which answer did you try?  There are many variations above.

I took your xml posted above and copied it into a clob for my testing and did XMLTYPE(mycolumn)  

since our tests obviously don't match, what is your table structure?

I will mimic it and test again.
0
slightwv (䄆 Netminder) Commented:
I get that error when my XML has the header that actually makes it XML:
<?xml version="1.0" encoding="UTF-8"?>

I never really tracked down "why".  I think it has to do with the character set my database is running (not UTF8) and the XML claiming it is UTF8.

A quick regexp_replace on the CLOB takes care of things:
regexp_replace(your_clob,'<\?.*\?>')

"quick" is relative...  the regexp functions are expensive to do.

That might cause problems if there is UTF8 data in the xml.  If so, it will error when it goes to parse the XML.
0
DevildibAuthor Commented:
Hi i resolbed it using xmltype over trim.thanks for your assitance
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
XML

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.