Skip to Content
avatar image
Former Member

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

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Mar 08, 2017 at 01:53 PM

    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,

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • Mar 16, 2017 at 12:51 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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>

  • Apr 20, 2017 at 01:50 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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.