Crystal report to show clob xml data from oracle and export to excel

Hi, i have a xml based clob column in an oracle table.i need to fetch multiple xml rows from the table each containing below xml, extract 30-35 tags out and display in crystal report and export it as excel.pls help.primarily i need to be able to export the crystal report in excel format.I can do the extraction of data via query...but is there any ready made option of xml to excel in crystal report? Finally how to export the report to excel format?

Xml:

The xml is as given below:

<?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 of http://www.vvv.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  (TTY/TDD) (855)729-2372 http://www.vvv.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 14999 o www.vvv.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-1112 (TTY/TDD)  </dsc:AddressLine>
                                                       <dsc:Country>
                                                              <dsc:CountryCode>US</dsc:CountryCode>
                                                              <dsc:CountryName> </dsc:CountryName>
                                                       </dsc:Country>
                                                       <dsc:Tel>111141</dsc:Tel>
                                                       <dsc:Url>www.vvv.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>
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.

DevildibAuthor Commented:
Crystal report is required in this case.pls guide with examples if possible.
0
slightwv (䄆 Netminder) Commented:
I posted this in your other question before you closed it so I'll repost it here:
---------------------------------------------------------------------
If the end-game is Excel, why involve Crystal Reports at all?

I use OpenXML with .Net to get data from Oracle into an xlsx file.

https://msdn.microsoft.com/en-us/library/office/bb448854.aspx

As far as parsing, I covered that in your previous questions:
I would probably start with LINQ.

There are MANY LINQ examples out there:
https://msdn.microsoft.com/en-us/library/bb675170.aspx


Since you only want 4 nodes, LINQ might be overkill but the other method is using the older XPATH stuff and probably not any faster.

http://www.csharp-examples.net/xml-nodes-by-name/
0
DevildibAuthor Commented:
This is diff reqmnt.i need to extact 30-35 tags for crystal reportand then export it to excel.crystal is mandatory for our project for reporting.and final result is excel.pls suggest.
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.

mlmccCommented:
Are you asking if Crystal can use XML as a datasource?
The answer is yes provided the XML is in a format it expects.  I used multiple XML files in a project a few years ago.  As far as Crystal was concerned each XML file was a table with columns.  I could even join XML files together just like tables.

I agree with slightwv, unless there is a reason for Crystal such as getting it nicely formatted for viewing, there is no need to use it just to get to Excel.

Crystal unless setup just perfectly for export to Excel, results in an Excel file that is difficult to use since it merges cells, accounts for extra whitespace by adding columns.  The data only export loses all formatting so if you plan to use that there is no reason to go through Crystal.

mlmcc
0
slightwv (䄆 Netminder) Commented:
Can't help with the actual Crystal Reports piece.

We do have a VERY GOOD Crystal Expert here.

I'm sure he will be along later!

[edited]  and he showed up while I was typing!!!
0
DevildibAuthor Commented:
Ok.will wait for him.thanks.it would be appreciated if you could pls pass this question to the crystal report expert you mentioned.sorry for the inconvinience.
0
mlmccCommented:
Thanks slightwv.  You help me with Oracle and I'll help on the Crystal questions.

mlmcc
0
slightwv (䄆 Netminder) Commented:
>> if you could pls pass this question to the crystal report expert you mentioned

Nothing to pass, he arrived when I was typing that post.

That Expert is mlmcc!  When it comes to Crystal Reports, if he can't figure out how to do it, it likely cannot be done...

I seriously doubt Crystal Reports can parsse XML faster than native C# or native Oracle XML calls.

I would do what I suggested in the previous questions:  Extract the values first, report second.

I stand by what I posted before as well:
Extract the nodes as close to the source as possible.

How often is data loaded into the tables versus how often do you need to report on it?

You might be better off extracting the XML and storing it relationally when the XML is inserted.  Then there is no need to extract it when reporting since it has already been done.

You also mentioned the DB extract was too slow.

How slow is it and where do you need it to be?

It it currently takes 2 minutes and you 'want' 1 second, give up now.  I doubt Crystal, LINQ or XPATH will achieve that 'goal'.


You probably want to look at changing the table structure to store the data as the XMLTYPE data type not a CLOB.

It will open up more options for you.

My database is pretty much 90% XML and the fields I report on are stored relationally for efficiency.  My XML is inserted through a Stored Procedure so I can extract the nodes I want stored relationally and commit all inserts at one time.

You might need an insert trigger to populate the relational table.

Basically what you are doing is eating the time to read/parse/report and moving it to time to insert a row.

You can also create XPATH indexes that will allow you to quickly access specific nodes.  Look around for "Function Based Indexes".  Again though, you are adding time to insert to save time to report later.

There is no magic here.

BUT, I bet using a tool (Crystal) to extract data from Oracle to get it into Excel will be your slowest possible alternative.  Unless you need a really pretty formatted 'report' that opens in Excel.

mlmcc mentioned it above, I'll confirm it:, in report format:  Unless everything is 100% PERFECT, the Excel output is pretty much useless!!!
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:
Pls do not merge my questions.pls consider each question as a diff question.i saw that you were still in frame of mind where you remembered extracting 4 columns out of xml from separate question not this one.here i had clearly mentioned 30-35 columns.pls answer specific questions and do not relate other questions.
0
DevildibAuthor Commented:
Request you not to merge my questions while giving expert comments.pls consider each question as a different question.
0
slightwv (䄆 Netminder) Commented:
I considered 4 nodes versus 35 nodes, the advice is still the same.

I provided advice based on 35 nodes.

I'm sorry if that wasn't the advice you wanted to hear.
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.