asked on

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>

Devildib

ASKER

Crystal report is required in this case.pls guide with examples if possible.

slightwv (䄆 Netminder)

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/

Devildib

ASKER

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.

Mike McCracken

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

slightwv (䄆 Netminder)

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!!!

Devildib

ASKER

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.

Mike McCracken

Thanks slightwv. You help me with Oracle and I'll help on the Crystal questions.

mlmcc

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Devildib

ASKER

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.

Devildib

ASKER

Request you not to merge my questions while giving expert comments.pls consider each question as a different question.

slightwv (䄆 Netminder)

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.