DataPower

DataPower

Join this online group to communicate across IBM product users and experts by sharing advice and best practices with peers and staying up to date regarding product enhancements.

 View Only
Back to discussions
Expand all | Collapse all

Issue while inserting XML Payload into Oracle DB from datapower XSLT using SQL data source object

  • 1.  Issue while inserting XML Payload into Oracle DB from datapower XSLT using SQL data source object

    Posted Thu July 13, 2023 05:53 AM

    Hi Team...

    I am using SQL data source in Datapower gateway to insert data into Oracle database. One of the columns in my table is of CLOB datatype and I am using the below code in XSLT to insert the XML payload, but the data is not getting inserted as XML. Please help how to achieve this.

    XSLT code

    <argument mode="INPUT" type="SQL_LONGVARCHAR"><xsl:value-of select="/*[local-name()='API_LOG_MESSAGE']/*[local-name()='LOG_DATA']/*[local-name()='PAYLOAD']"/></argument>

    XML Data is getting inserted into Oracle database as below

    0000SUCCESSSU57N6b7aa0f87209a47eb3fad5874a95db192023071313260020230713132600CO8810000000056SUC2122-06-04T12:33:32



    ------------------------------
    Ashok Beshra
    ------------------------------


  • 2.  RE: Issue while inserting XML Payload into Oracle DB from datapower XSLT using SQL data source object

    Posted Thu July 13, 2023 07:18 AM

    Replace "<xsl:value-of ..." with "<xsl:copy-of ..."



    ------------------------------
    Hermann Stamm-Wilbrandt
    Compiler Level 3 support & Fixpack team lead
    IBM DataPower Gateways (⬚ᵈᵃᵗᵃ / ⣏⠆⡮⡆⢹⠁⡮⡆⡯⠂⢎⠆⡧⡇⣟⡃⡿⡃)
    https://stamm-wilbrandt.de/en/blog/
    ------------------------------

    Original Message


  • 3.  RE: Issue while inserting XML Payload into Oracle DB from datapower XSLT using SQL data source object

    Posted Thu July 13, 2023 08:17 AM
    Hi Hermann...
     
    Thanks for your solution. It worked now, and able to get the payload in XML format. However, the payload is coming with the root tag which I do not wanted to get logged.
     
    Additionally, some of our payload will have JSON data as well and we wanted to insert it into the database.
    Modified XSLT code,
    <argument mode="INPUT" type="SQL_LONGVARCHAR"><xsl:copy-of select="/*[local-name()='API_LOG_MESSAGE']/*[local-name()='LOG_DATA']/*[local-name()='PAYLOAD']"/></argument>
     
    i,e the payload data is below
     
    <PAYLOAD>
    • <soapenv:Envelope
      xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
      xmlns:xsd="http://www.w3.org/2001/XMLSchema"
      xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      >
      • <soapenv:Body>
        • <CustomGetAccountListPermissionResponse xsi:schemaLocation="http://www.infosys.com/response/CustomGetAccountListPermission CustomGetAccountListPermissionResponse.xsd"
          xmlns="http://www.infosys.com/response/CustomGetAccountListPermission"
          xmlns:hd="http://www.infosys.com/response/header"
          xmlns:ft="http://www.infosys.com/response/footer"
          >
          • <hd:header>
            • <hd:STATUS>
              • <hd:MESSAGE>
                • <hd:MESSAGE_CODE>0000</hd:MESSAGE_CODE>
                • <hd:MESSAGE_DESC>SUCCESS</hd:MESSAGE_DESC>
                • <hd:MESSAGE_TYPE>SU</hd:MESSAGE_TYPE>
                </hd:MESSAGE>
     
    It is written to database as below. I want the Root <PAYLOAD> and its namespaces to be removed.
    <PAYLOAD xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:date="http://exslt.org/dates-and-times" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:str="http://exslt.org/strings"><soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soapenv:Body><CustomGetAccountListPermissionResponse xsi:schemaLocation="http://www.infosys.com/response/CustomGetAccountListPermission CustomGetAccountListPermissionResponse.xsd" xmlns:ft="http://www.infosys.com/response/footer" xmlns:hd="http://www.infosys.com/response/header" xmlns="http://www.infosys.com/response/CustomGetAccountListPermission"><hd:header><hd:STATUS><hd:MESSAGE><hd:MESSAGE_CODE>0000</hd:MESSAGE_CODE><hd:MESSAGE_DESC>SUCCESS</hd:MESSAGE_DESC><hd:MESSAGE_TYPE>SU</hd:MESSAGE_TYPE></hd:MESSAGE></hd:STATUS><hd:PAGINATION><hd:NUM_REC_RETURNED>57</hd:NUM_REC_RETURNED><hd:HAS_MORE_RECORDS>N</hd:HAS_MORE_RECORDS></hd:PAGINATION>...


    ------------------------------
    Ashok Beshra
    ------------------------------

    Original Message


  • 4.  RE: Issue while inserting XML Payload into Oracle DB from datapower XSLT using SQL data source object

    Posted Thu July 13, 2023 09:38 AM
    Edited by Hermann Stamm-Wilbrandt Thu July 13, 2023 09:40 AM

    > I want the Root <PAYLOAD> and its namespaces to be removed.
    >
    You can only get those namespaces removed, that are not used in what you extract.
    Even then, there is no guarantee by XSLT spec that you will be able to, but for many cases there are options.

    Let's first get logged what you are interested in.

    Instead 

    <xsl:copy-of select="/*[local-name()='API_LOG_MESSAGE']/*[local-name()='LOG_DATA']/*[local-name()='PAYLOAD']"/>

    use

    <xsl:copy-of select="/*[local-name()='API_LOG_MESSAGE']/*[local-name()='LOG_DATA']/*[local-name()='PAYLOAD']/*"/>

    and see whether you log what you want.

    If so, see which namespace definitions are unused and you want get removed.
    Additional namespace definitions do not change the XML infoset.
    So they are no problem for all XML processors that comply to XML spec.



    ------------------------------
    Hermann Stamm-Wilbrandt
    Compiler Level 3 support & Fixpack team lead
    IBM DataPower Gateways (⬚ᵈᵃᵗᵃ / ⣏⠆⡮⡆⢹⠁⡮⡆⡯⠂⢎⠆⡧⡇⣟⡃⡿⡃)
    https://stamm-wilbrandt.de/en/blog/
    ------------------------------

    Original Message