Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Extracting data form XML by using XQuery and generating CSV file formate

999234Mar 27 2013 — edited Mar 29 2013
Hi all,

I have created DB Adapter in Jdeveloper for selecting following output.
After this i have imported that Adapter into Eclipse in OSB and generated Business service and created proxy service . in Proxy i was invoked this Businees service through ServiceCallout . after this i got the following output from Database. after that i have to extract data from the following output and need to generate CSV file formate.


<soap-env:Body xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/">
<fet:FetchingGFS_P2P_StatusOutputCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:fet="http://xmlns.oracle.com/pcbpel/adapter/db/FetchingGFS_P2P_Status">
<fet:FetchingGFS_P2P_StatusOutput>
<fet:SERVICE>SyncPayableInvoiceGIPStageReqABCSImpl</fet:SERVICE>
<fet:OPERATION>SyncPayableInvoice</fet:OPERATION>
<fet:TYPE>APINVOICE</fet:TYPE>
<fet:STATUS_TIMESTAMP>2013-03-27T10:58:45.499+11:00</fet:STATUS_TIMESTAMP>
<fet:STATUS_CODE>NEW</fet:STATUS_CODE>
<fet:STATUS_MESSAGE>Interface Table Record Id: 9055</fet:STATUS_MESSAGE>
<fet:STATUS>SUCCESS</fet:STATUS>
<fet:BUSINESS_INFO>
Source Invoice No: COM41281-0197, Source Vendor Site Code: 16021, Voucher Number: 362282
</fet:BUSINESS_INFO>
<fet:BATCH_NAME>CaseWin_APInvoice_GFS_20130321.CSV</fet:BATCH_NAME>
<fet:REPORTED_TO>GFS</fet:REPORTED_TO>
<fet:REPORT_DATE>2013-03-27T10:58:45.499+11:00</fet:REPORT_DATE>
<fet:SOURCE_SYSTEM>CASEWIN</fet:SOURCE_SYSTEM>
<fet:TARGET_SYSTEM>GFS</fet:TARGET_SYSTEM>
<fet:REC_ID>34</fet:REC_ID>
</fet:FetchingGFS_P2P_StatusOutput>
<fet:FetchingGFS_P2P_StatusOutput>
<fet:SERVICE>SyncPayableInvoiceGIPStageReqABCSImpl</fet:SERVICE>
<fet:OPERATION>SyncPayableInvoice</fet:OPERATION>
<fet:TYPE>APINVOICE</fet:TYPE>
<fet:STATUS_TIMESTAMP>2013-03-27T10:58:45.592+11:00</fet:STATUS_TIMESTAMP>
<fet:STATUS_CODE>NEW</fet:STATUS_CODE>
<fet:STATUS_MESSAGE>Interface Table Record Id: 9056</fet:STATUS_MESSAGE>
<fet:STATUS>SUCCESS</fet:STATUS>
<fet:BUSINESS_INFO>
Source Invoice No: COM41281-0197, Source Vendor Site Code: 16021, Voucher Number: 364147
</fet:BUSINESS_INFO>
<fet:BATCH_NAME>CaseWin_APInvoice_GFS_20130321.CSV</fet:BATCH_NAME>
<fet:REPORTED_TO>GFS</fet:REPORTED_TO>
<fet:REPORT_DATE>2013-03-27T10:58:45.592+11:00</fet:REPORT_DATE>
<fet:SOURCE_SYSTEM>CASEWIN</fet:SOURCE_SYSTEM>
<fet:TARGET_SYSTEM>GFS</fet:TARGET_SYSTEM>
<fet:REC_ID>35</fet:REC_ID>
</fet:FetchingGFS_P2P_StatusOutput>
<fet:FetchingGFS_P2P_StatusOutput>
<fet:SERVICE>SyncPayableInvoiceGIPStageReqABCSImpl</fet:SERVICE>
<fet:OPERATION>SyncPayableInvoice</fet:OPERATION>
<fet:TYPE>APINVOICEBATCH</fet:TYPE>
<fet:STATUS_TIMESTAMP>2013-03-27T10:58:45.939+11:00</fet:STATUS_TIMESTAMP>
<fet:STATUS_CODE>NEW</fet:STATUS_CODE>
<fet:STATUS_MESSAGE>Concurrent Program Id: 593206</fet:STATUS_MESSAGE>
<fet:STATUS>SUCCESS</fet:STATUS>
<fet:BUSINESS_INFO>APXIIMPT</fet:BUSINESS_INFO>
<fet:BATCH_NAME>CaseWin_APInvoice_GFS_20130321.CSV</fet:BATCH_NAME>
<fet:REPORTED_TO>GFS</fet:REPORTED_TO>
<fet:REPORT_DATE>2013-03-27T10:58:45.939+11:00</fet:REPORT_DATE>
<fet:SOURCE_SYSTEM>CASEWIN</fet:SOURCE_SYSTEM>
<fet:TARGET_SYSTEM>GFS</fet:TARGET_SYSTEM>
<fet:REC_ID>36</fet:REC_ID>
</fet:FetchingGFS_P2P_StatusOutput>
<fet:FetchingGFS_P2P_StatusOutput>
<fet:SERVICE>SyncPayableInvoiceGIPStageReqABCSImpl</fet:SERVICE>
<fet:OPERATION>SyncPayableInvoice</fet:OPERATION>
<fet:TYPE>APINVOICE</fet:TYPE>
<fet:STATUS_TIMESTAMP>2013-03-27T12:02:28.679+11:00</fet:STATUS_TIMESTAMP>
<fet:STATUS_CODE>NEW</fet:STATUS_CODE>
<fet:STATUS_MESSAGE>Interface Table Record Id: 9057</fet:STATUS_MESSAGE>
<fet:STATUS>SUCCESS</fet:STATUS>
<fet:BUSINESS_INFO>
Source Invoice No: COM41281-0197, Source Vendor Site Code: BRITAX AUTOMOTI, Voucher Number: 362282
</fet:BUSINESS_INFO>
<fet:BATCH_NAME>CaseWin_APInvoice_GFS_20130321.CSV</fet:BATCH_NAME>
<fet:REPORTED_TO>GFS</fet:REPORTED_TO>
<fet:REPORT_DATE>2013-03-27T12:02:28.679+11:00</fet:REPORT_DATE>
<fet:SOURCE_SYSTEM>CASEWIN</fet:SOURCE_SYSTEM>
<fet:TARGET_SYSTEM>GFS</fet:TARGET_SYSTEM>
<fet:REC_ID>37</fet:REC_ID>
</fet:FetchingGFS_P2P_StatusOutput>
<fet:FetchingGFS_P2P_StatusOutput>
<fet:SERVICE>SyncPayableInvoiceGIPStageReqABCSImpl</fet:SERVICE>
<fet:OPERATION>SyncPayableInvoice</fet:OPERATION>
<fet:TYPE>APINVOICE</fet:TYPE>
<fet:STATUS_TIMESTAMP>2013-03-27T12:02:28.711+11:00</fet:STATUS_TIMESTAMP>
<fet:STATUS_CODE>NEW</fet:STATUS_CODE>
<fet:STATUS_MESSAGE>Interface Table Record Id: 9058</fet:STATUS_MESSAGE>
<fet:STATUS>SUCCESS</fet:STATUS>
<fet:BUSINESS_INFO>
Source Invoice No: COM41281-0197, Source Vendor Site Code: BRITAX AUTOMOTI, Voucher Number: 364147
</fet:BUSINESS_INFO>
<fet:BATCH_NAME>CaseWin_APInvoice_GFS_20130321.CSV</fet:BATCH_NAME>
<fet:REPORTED_TO>GFS</fet:REPORTED_TO>
<fet:REPORT_DATE>2013-03-27T12:02:28.711+11:00</fet:REPORT_DATE>
<fet:SOURCE_SYSTEM>CASEWIN</fet:SOURCE_SYSTEM>
<fet:TARGET_SYSTEM>GFS</fet:TARGET_SYSTEM>
<fet:REC_ID>38</fet:REC_ID>
</fet:FetchingGFS_P2P_StatusOutput>
<fet:FetchingGFS_P2P_StatusOutput>
<fet:SERVICE>SyncPayableInvoiceGIPStageReqABCSImpl</fet:SERVICE>
<fet:OPERATION>SyncPayableInvoice</fet:OPERATION>
<fet:TYPE>APINVOICEBATCH</fet:TYPE>
<fet:STATUS_TIMESTAMP>2013-03-27T12:02:28.814+11:00</fet:STATUS_TIMESTAMP>
<fet:STATUS_CODE>NEW</fet:STATUS_CODE>
<fet:STATUS_MESSAGE>Concurrent Program Id: 593212</fet:STATUS_MESSAGE>
<fet:STATUS>SUCCESS</fet:STATUS>
<fet:BUSINESS_INFO>APXIIMPT</fet:BUSINESS_INFO>
<fet:BATCH_NAME>CaseWin_APInvoice_GFS_20130321.CSV</fet:BATCH_NAME>
<fet:REPORTED_TO>GFS</fet:REPORTED_TO>
<fet:REPORT_DATE>2013-03-27T12:02:28.814+11:00</fet:REPORT_DATE>
<fet:SOURCE_SYSTEM>CASEWIN</fet:SOURCE_SYSTEM>
<fet:TARGET_SYSTEM>GFS</fet:TARGET_SYSTEM>
<fet:REC_ID>39</fet:REC_ID>
</fet:FetchingGFS_P2P_StatusOutput>
<fet:FetchingGFS_P2P_StatusOutput>
<fet:SERVICE>SyncPayableInvoiceGIPStageReqABCSImpl</fet:SERVICE>
<fet:OPERATION>SyncPayableInvoice</fet:OPERATION>
<fet:TYPE>APINVOICEBATCH</fet:TYPE>
<fet:STATUS_TIMESTAMP>2013-03-26T20:16:03.78+11:00</fet:STATUS_TIMESTAMP>
<fet:STATUS_CODE>NEW</fet:STATUS_CODE>
<fet:STATUS_MESSAGE>Concurrent Program Id: 593010</fet:STATUS_MESSAGE>
<fet:STATUS>SUCCESS</fet:STATUS>
<fet:BUSINESS_INFO>APXIIMPT</fet:BUSINESS_INFO>
<fet:BATCH_NAME>CaseWin_APInvoice_GFS_20130321.CSV</fet:BATCH_NAME>
<fet:REPORTED_TO>GFS</fet:REPORTED_TO>
<fet:REPORT_DATE>2013-03-26T20:16:03.78+11:00</fet:REPORT_DATE>
<fet:SOURCE_SYSTEM>CASEWIN</fet:SOURCE_SYSTEM>
<fet:TARGET_SYSTEM>GFS</fet:TARGET_SYSTEM>
<fet:REC_ID>33</fet:REC_ID>
</fet:FetchingGFS_P2P_StatusOutput>
<fet:FetchingGFS_P2P_StatusOutput>
<fet:SERVICE>SyncPayableInvoiceGIPStageReqABCSImpl</fet:SERVICE>
<fet:OPERATION>SyncPayableInvoice</fet:OPERATION>
<fet:TYPE>APINVOICE</fet:TYPE>
<fet:STATUS_TIMESTAMP>2013-03-26T20:16:03.6+11:00</fet:STATUS_TIMESTAMP>
<fet:STATUS_CODE>NEW</fet:STATUS_CODE>
<fet:STATUS_MESSAGE>Interface Table Record Id: 9052</fet:STATUS_MESSAGE>
<fet:STATUS>SUCCESS</fet:STATUS>
<fet:BUSINESS_INFO>9052</fet:BUSINESS_INFO>
<fet:BATCH_NAME>CaseWin_APInvoice_GFS_20130321.CSV</fet:BATCH_NAME>
<fet:REPORTED_TO>GFS</fet:REPORTED_TO>
<fet:REPORT_DATE>2013-03-26T20:16:03.6+11:00</fet:REPORT_DATE>
<fet:SOURCE_SYSTEM>CASEWIN</fet:SOURCE_SYSTEM>
<fet:TARGET_SYSTEM>GFS</fet:TARGET_SYSTEM>
<fet:REC_ID>31</fet:REC_ID>
</fet:FetchingGFS_P2P_StatusOutput>
<fet:FetchingGFS_P2P_StatusOutput>
<fet:SERVICE>SyncPayableInvoiceGIPStageReqABCSImpl</fet:SERVICE>
<fet:OPERATION>SyncPayableInvoice</fet:OPERATION>
<fet:TYPE>APINVOICE</fet:TYPE>
<fet:STATUS_TIMESTAMP>2013-03-26T20:16:03.649+11:00</fet:STATUS_TIMESTAMP>
<fet:STATUS_CODE>NEW</fet:STATUS_CODE>
<fet:STATUS_MESSAGE>Interface Table Record Id: 9053</fet:STATUS_MESSAGE>
<fet:STATUS>SUCCESS</fet:STATUS>
<fet:BUSINESS_INFO>9053</fet:BUSINESS_INFO>
<fet:BATCH_NAME>CaseWin_APInvoice_GFS_20130321.CSV</fet:BATCH_NAME>
<fet:REPORTED_TO>GFS</fet:REPORTED_TO>
<fet:REPORT_DATE>2013-03-26T20:16:03.649+11:00</fet:REPORT_DATE>
<fet:SOURCE_SYSTEM>CASEWIN</fet:SOURCE_SYSTEM>
<fet:TARGET_SYSTEM>GFS</fet:TARGET_SYSTEM>
<fet:REC_ID>32</fet:REC_ID>
</fet:FetchingGFS_P2P_StatusOutput>
</fet:FetchingGFS_P2P_StatusOutputCollection>
</soap-env:Body>


The CSV file formate like below formate.
SyncPayableInvoiceGIPStageReqABCSImpl,SyncPayableInvoice,APINVOICE,2013-03-27T10:58:45.499+11:00,NEW,Interface Table Record Id: 9055,SUCCESS, Source Invoice No: COM41281-0197, Source Vendor Site Code: 16021, Voucher Number: 362282 ,CaseWin_APInvoice_GFS_20130321.CSV,GFS,2013-03-27T10:58:45.499+11:00,CASEWIN,GFS,34
and I need to loop for each fet:FetchingGFS_P2P_StatusOutput.

Please help me on this how to write XQuery and How to generate CSV file.
After this I need to send this CSV file to another service by invoking that service.
Please do the needful ASAP.


Thanks,
Kiran.
+91-9742672532.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 26 2013
Added on Mar 27 2013
#xml, #xquery
3 comments
3,614 views