Skip to Content
0

How to fetch XML data from data base table field called "TXT_XML" CLOB

Mar 08, 2017 at 01:30 PM

118

avatar image

Hi All,

Scenario is JDBC to Idoc

We have some fields in the oracle data base table, among them there is a field called "txt_xml" (it is a CLOB in the database table). They are inserting the whole XML data into that field "txt_xml".

The requirement is to fetch whole XML data from txt_xml field and map it to the target Idoc structure.

I think we need a java mapping for this, Please someone help me on this requirement.

If possible provide me the java code for this.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Iñaki Vila Mar 08, 2017 at 01:53 PM
0

Hi Tulassiram,

Depending your PI version, you can use a jdbc lookup

https://blogs.sap.com/2008/02/15/sap-pi-71-mapping-enhancements-series-graphical-support-for-jdbc-and-rfc-lookups/

A more efficient way, it is to do it an async-sync bridge, an example

http://saprainbow.com/wordpress/wp-content/uploads/2015/06/AsyncSync_and_SyncAsync_Bridge_without_BPM.pdf

Regards,

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Inaki,

Thanks for the quick response and sorry for the delayed response from my end.

My PI/PO version is 7.4 Single stack and the scenario is JDBC to IDOC.

Is it possible to fetch the whole data from Database CLOB(which is defined as a string) data type through above mentioned procedure ? here the whole data in the CLOB is in the form of target IDOC structure.

Please elaborate bit more on this.

Thanks in advance. Looking for quick response from your end.

Regards,

Tulasiram

0
Iñaki Vila Mar 16, 2017 at 12:51 PM
0

Hi Tulassiram,

To use a JDBC sender channel you need a field to mark when the register has been taken or not. Do you have this field or not? o can you add this field to DB table or not?

Case 1. You have this field.

Sender JDBC support to SELECT fields with CLOB types.

Case 2. You can't have this field.

You can do a proxy (async) - jdbc (sync) - idoc (async) scenario.

Regards.

Show 5 Share
10 |10000 characters needed characters left characters exceeded

Hi Inaki,

To use a JDBC sender channel you need a field to mark when the register has been taken or not. Do you have this field or not? o can you add this field to DB table or not?

Yes, we are having the field called Status. After fetching the data we are updating this field with current time stamp.

For the Case1 : can you please provide the sample code for this.

I am using java mapping to fetch the whole XML data from CLOB field (TXT_XML), below is the code.

But here the problem is, this code is working for to fetch the single record from the data base.

Suppose if we have multiple records, this code not working.

Can you please do the modification for the below code.

Kindly help me on this.


public void transform(TransformationInput in, TransformationOutput out)

throws StreamTransformationException {
try {


String sourcexml = " "; String targetxml =" "; String line =" ";


InputStream ins = in.getInputPayload().getInputStream();
OutputStream outs = out.getOutputPayload().getOutputStream();


BufferedReader br = new BufferedReader( new InputStreamReader(ins));

while ((line = br.readLine()) != null)
sourcexml +=line;
br.close();

targetxml = sourcexml;
targetxml = targetxml.replaceAll("ns0","prefix");
int a = targetxml.indexOf("<TXT_XML>");
int b = targetxml.indexOf("</TXT_XML>");

targetxml = targetxml.substring(a+18,b-3);

out.getOutputPayload().getOutputStream().write(targetxml.getBytes());

}

catch (Exception e)
{
throw new StreamTransformationException(e.getMessage());
}
}

0
Tulasiram Donkena

Please attach the sample input XML after SQL query execution , so that we can provide you the code accordingly.

0

happlication-datafarmp11microsoftwindowsdesktopido.txt

Hi Manoj,

Attached is the XML from CLOB field.

Database structure screen shot:

0

What i meant is the complete input XML file not just the data from clob field , provide the xml which is input for your OM where you have multiple clob fields.

0

Hi manoj,

Please find the below xml.

<?xml version="1.0" encoding="utf-8"?>
<ns:MT_Webedi_Asn_Test xmlns:ns="http://www.mahle.com/Webedi/Asn/INT049">
<row>
<NUM_ASN_ID>2614040</NUM_ASN_ID>
<NUM_IDOC/>
<NUM_SYSTEM_ID>4</NUM_SYSTEM_ID>
<DT_CREATE>2016-08-29 00:34:47.0</DT_CREATE>
<TXT_SHIPMENT_NUM>DH168O240</TXT_SHIPMENT_NUM>
<TXT_XML><![CDATA[<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE InASN><DELVRY03><IDOC><EDI_DC40><TABNAM>EDI_DC40</TABNAM><MANDT>025</MANDT><DIRECT>2</DIRECT><IDOCTYP>DELVRY03</IDOCTYP><MESTYP>DESADV</MESTYP><MESFCT>010</MESFCT><SNDPOR>SAPPN1</SNDPOR><SNDPRT>LI</SNDPRT><SNDPFC>LF</SNDPFC><SNDPRN>0001063548</SNDPRN><RCVPOR>SAPPN1025</RCVPOR><RCVPRT>LI</RCVPRT><RCVPRN>0001063548</RCVPRN></EDI_DC40><E1EDL20><ABLAD>J702SIL</ABLAD><BTGEW>119</BTGEW><NTGEW>103</NTGEW><GEWEI>KGM</GEWEI><ANZPK>1</ANZPK><BOLNR>DH168O240</BOLNR><LIFEX>DH168O240</LIFEX><E1EDL18><QUALF>ORI</QUALF></E1EDL18><E1ADRM1><PARTNER_Q>WE</PARTNER_Q><E1ADRE1><EXTEND_Q>301</EXTEND_Q><EXTEND_D>J702</EXTEND_D></E1ADRE1></E1ADRM1><E1ADRM1><PARTNER_Q>AG</PARTNER_Q><E1ADRE1><EXTEND_Q>302</EXTEND_Q><EXTEND_D>0001063548</EXTEND_D></E1ADRE1></E1ADRM1><E1ADRM1><PARTNER_Q>LF</PARTNER_Q><E1ADRE1><EXTEND_Q>300</EXTEND_Q><EXTEND_D>0001063548</EXTEND_D></E1ADRE1></E1ADRM1><E1ADRM1><PARTNER_Q>SP</PARTNER_Q></E1ADRM1><E1EDT13><QUALF>015</QUALF><NTANF>20160829</NTANF><NTANZ>123248</NTANZ></E1EDT13><E1EDT13><QUALF>007</QUALF><NTANF>20160829</NTANF><NTANZ>1332</NTANZ></E1EDT13><E1TXTH8><TDID>ZPRO</TDID><TDSPRAS>E</TDSPRAS><E1TXTP8><TDLINE>DHL EXPRESS</TDLINE></E1TXTP8></E1TXTH8><E1TXTH8><TDID>ZTID</TDID><TDSPRAS>E</TDSPRAS><E1TXTP8><TDLINE>N/A</TDLINE></E1TXTP8></E1TXTH8><E1TXTH8><TDID>ZSLI</TDID><TDSPRAS>E</TDSPRAS><E1TXTP8><TDLINE></TDLINE></E1TXTP8></E1TXTH8><E1EDL28><VSART>A</VSART></E1EDL28><E1EDL24><POSNR>1</POSNR><LGORT>2001</LGORT><KDMAT>16455792</KDMAT><LFIMG>6000</LFIMG><VRKME>PCE</VRKME><LIEFFZ>72000</LIEFFZ><E1EDL41><QUALI>001</QUALI><BSTNR>0550601244</BSTNR></E1EDL41><E1TXTH9><TDOBNAME>VBBP</TDOBNAME><TDID>ZMFG</TDID><TDSPRAS>E</TDSPRAS><E1TXTP9><TDLINE></TDLINE></E1TXTP9></E1TXTH9></E1EDL24></E1EDL20></IDOC></DELVRY03>]]></TXT_XML>
<TXT_BOLN>DH168O240</TXT_BOLN>
<TXT_DATA_DESTINATION>SAPPN1025</TXT_DATA_DESTINATION>
<TXT_LABEL_XML><![CDATA[<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE LabelXML><AsnLabel><IDOC dataDestination="DESTINATION" plantName="MAHLE BEHR THERMAL SILAO" unloadPoint="J702SIL" vendorCd="0001063548"/><Plant format="EDIFACT" region="Vega2" selected="J702"/><Parts><Part cd="16455792" dimensionId="608614" idocNum="501186743" manufDuns="" matchCd="3" matchId="3585944" partDesc="STUD-M6X1TORX/COLR M7X2.89ZP/CR 6" revisionLevel="" scheduleAgreement="0550601244" storageBin="" storageLocation="2001" supplierPartCd="" totalQty="6000" unitOfMeasure="PCE"/></Parts><Containers packageLevel="1" totalQty="6000"><LabelHeader docNum="DH168O240" dockCd="" shipDt="20160829" unloadPoint="J702SIL"><Supplier cd="557805030" city="DAEGU" cntry="KR" locName="ERAE AUTOMOTIVE-COMPRESSOR" state="27" zip=""/><Receiver city="SILAO" division="" name="MAHLE BEHR THERMAL SILAO" postalCd="SILAO" state="" street=""/></LabelHeader><OuterContainer containerType="1" index="0" numContainers="1" totalQty="6000"><LabelContainer id="2" lotNum="" lotNum_Code128="È1T3Ê" made="M" manDt="20160829" parentId="2" serialNum="8675944"/><PartRow index="0" partCd="16455792" partCd_Code128="ÈP16455792XÊ" partQty="6000"/></OuterContainer></Containers><Header callOffId="" carrierPRONum="DHL Express" deliveryCarrierIdentification="DHL Express" deliveryExcessTransit="" deliveryExcessTransitAuth="" deliveryExcessTransitReason="" deliveryExcessTransitReasonId="" deliveryExcessTransitResponsibility="" deliveryExcessTransitResponsibilityId="" deliveryTransportationMode="Air" deliveryTransportationModeId="A" departureCarrierIdentification="DHL Express" departureExcessTransit="" departureExcessTransitAuth="" departureExcessTransitReason="" departureExcessTransitReasonId="" departureExcessTransitResponsibility="" departureExcessTransitResponsibilityId="" departureTransportationMode="Air" departureTransportationModeId="A" equipment="Trailer" equipmentId="T" equipmentIdentification="N/A" grossWeight="119" masterBillOfLading="DH168O240" netWeight="103" packagingLevel="1" sLogNum="" shipmentIdentificationNum="DH168O240" unitOfMeasure="Kilograms" unitOfMeasureId="KGM"><DispatchDate amPm="PM" day="29" hour="01" minute="32" month="08" year="2016"/><DeliveryDate amPm="PM" day="29" hour="01" minute="32" month="08" year="2016"/></Header></AsnLabel>]]></TXT_LABEL_XML>
<DT_SHIP>2016-08-29 13:32:47.0</DT_SHIP>
<NUM_USER_ID>27925</NUM_USER_ID>
</row>
</ns:MT_Webedi_Asn_Test>

0
Evgeniy Kolmakov Apr 20, 2017 at 01:50 PM
0

Hi Tulasiram!

I'd prefer using DOM parser to read contents of every row in your source XML.

But if you want to parse it as text you might consider using indexOf method with following syntax: indexOf(String str, int fromIndex)

After you found first occurence, you start next search from that index and so on untill you get -1. Thus you'll get all values, not just the first one.

Regards, Evgeniy.

Show 4 Share
10 |10000 characters needed characters left characters exceeded

Hi Evgeniy,

Below is the code I am using to fetch the CLOM data which is containing XML data in it, but this code is working for single record , not for multiple records.

Please suggest on this.


public void transform(TransformationInput in, TransformationOutput out)

throws StreamTransformationException {
try {


String sourcexml = " "; String targetxml =" "; String line =" ";


InputStream ins = in.getInputPayload().getInputStream();
OutputStream outs = out.getOutputPayload().getOutputStream();


BufferedReader br = new BufferedReader( new InputStreamReader(ins));

while ((line = br.readLine()) != null)
sourcexml +=line;
br.close();

targetxml = sourcexml;
// targetxml = targetxml.replaceAll("ns0","prefix");
int a = targetxml.indexOf("<TXT_XML>");
int b = targetxml.indexOf("</TXT_XML>");
String s1="<";
String s2=">";
String s3=""";
String s4="<";
String s5=">";
String s6=" \"\" ";
targetxml = targetxml.substring(a+18,b-3);
targetxml = targetxml.replaceAll(s1,s4);
targetxml = targetxml.replaceAll(s2,s5);
targetxml = targetxml.replaceAll(s3,s6);
out.getOutputPayload().getOutputStream().write(targetxml.getBytes());

}

catch (Exception e)
{
throw new StreamTransformationException(e.getMessage());
}
}

0

Hi Tulasiram,

There should be a loop between the following lines:

targetxml = sourcexml;

and

out.getOutputPayload().getOutputStream().write(targetxml.getBytes());

What is the purpose of those lines?

targetxml = targetxml.replaceAll(s1,s4);
targetxml = targetxml.replaceAll(s2,s5);

When you replace variables with values, then these lines look strange:

targetxml = targetxml.replaceAll("<","<");
targetxml = targetxml.replaceAll(">",">");

Regards,

Andrzej

0

Hi Tulasiram!

This method adds to returned ArrayList contents of each occurence of element which name is given as input parameter:

public ArrayList<String> getElementValues(String srcXML, String elementName) {
ArrayList<String> elementValues = new ArrayList<String>();
String startTag = "<" + elementName + ">";
String endTag = "</" + elementName + ">";
int startTagLength = 2 + elementName.length();

int startTagPos = srcXML.indexOf(startTag);
int endTagPos = srcXML.indexOf(endTag);

while (startTagPos != -1 && endTagPos != -1) {
elementValues.add(srcXML.substring(startTagPos + startTagLength, endTagPos));

startTagPos = srcXML.indexOf(startTag, ++startTagPos);
endTagPos = srcXML.indexOf(endTag, ++endTagPos);
}

return elementValues;
}

Regards, Evgeniy.

1

Hi Evgeniy,

Tried with your code, but no luck.

Still we are facing the same issue.

If possible can you give me the complete code to fetch XML data from CLOB field type in oracle database and map the same xml structure to target IDOC as I explained above.

Thanks in advace.

0