Topic
  • 2 replies
  • Latest Post - ‏2015-04-01T06:32:27Z by DSerbin
SystemAdmin
SystemAdmin
6772 Posts

Pinned topic Calling a stored procedure with one parameter of data type XML

‏2013-02-04T11:48:47Z |
Hi There,
I am facing an issue depicted below any guidance will be much appreciated.

My use case is to call a store procedure with one parameter of data type XML and another parameter is varchar, which is created on Microsoft SQL server 2008.
Below is the code snippet i have written in XSL.

<xsl:variable name="result">
<xsl:message>
This XSL will call get input parameters from the request and call FJSPDTLB stored proc
</xsl:message>
<xsl:variable name="spfullname" select="concat('CALL',' ',$SchemaName,'.sp_AuditService(?,?)')"/>
<dp:sql-execute source="$DataSourceName" statement="$spfullname">
<arguments>
<xsl:message>
Inserts Audit Payload in database tables
</xsl:message>
<argument type="SQL_WLONGVARCHAR" mode="INPUT" nullable="false" precision="0" scale="0" isNull="false">
<xsl:copy-of select="$xmlpayload"/>
</argument>
<argument type="SQL_LONGVARCHAR" mode="INPUT" nullable="false" precision="0" scale="0" isNull="false"><xsl:value-of select="$devicename"/></argument>
</arguments>
</dp:sql-execute>
</xsl:variable>

I cam getting following error in $result while executing the XSL

<sql result="error">
<message>String data, right truncated. Error in parameter 1.</message>
</sql>

Below is the xml payload which needs to be passed as parameter 1 in the store procedure. ($xmlpayload)

<LogData>
<SvcTxn>
<CorID>0ed7216b65ee47e</CorID>
<SvcNm>mpgwProviderSessionsService</SvcNm>
<FnNm>InitiateSessions</FnNm>
<TxnStat>SUCCESS</TxnStat>
<CID>TRAVEL SUITE</CID>
<CIP>10.79.210.216</CIP>
<STime>2013-01-21T03:04:30Z</STime>
<ETime>2013-01-21T03:04:42Z</ETime>
<RqExecTime>122</RqExecTime>
<RsExecTime>22</RsExecTime>
<TotExecTime>144</TotExecTime>
<BkIntStat>S</BkIntStat>
</SvcTxn>
<SvcPayLoad>
<CorID>0ed7216b65ee47e</CorID>
<SvcNm>mpgwProviderSessionsService</SvcNm>
<FnNm>InitiateSessions</FnNm>
<SvcRq>
<ProviderSessionsRQ>
<Capability>Hotel</Capability>
<PccId>TCT</PccId>
<MarketCode>USA</MarketCode>
<Domain>TravelSuite</Domain>
<DKNumber>AXUSHW11CU</DKNumber>
</ProviderSessionsRQ>
</SvcRq>
<SvcRs>
<ProviderSessionsRS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Provider>
<Status>Success</Status>
<StatusCode>SUCCESS</StatusCode>
<StatusMsg>Success</StatusMsg>
<SessionId>xx-xxxxx</SessionId>
</Provider>
</ProviderSessionsRS>
</SvcRs>
</SvcPayLoad>
<BkEndIntg>
<BkCorID>0ed7216b65ee47e</BkCorID>
<BkTxnNm>InitiateSessions</BkTxnNm>
<BkSvcProviderNm>TCT</BkSvcProviderNm>
<BkUrl>http://dwebservices.trcw.us.aexp.com:80/OHHService</BkUrl>
<BkRq>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:exsl="http://exslt.org/common">
<soap:Header>
<Security xmlns="http://www.opentravel.org/OTA/2003/05">
<UsernameToken>
<Username>AXUSHWGU</Username>
<Password>hwgus11cu</Password>
</UsernameToken>
</Security>
</soap:Header>
<soap:Body>
<OpenHotelHubSessionCreateRQ Version="5.000" xmlns="http://www.opentravel.org/OTA/2003/05">
<POS>
<Source DKNumber="AXUSHW11CU"/>
</POS>
</OpenHotelHubSessionCreateRQ>
</soap:Body>
</soap:Envelope>
</BkRq>
<BkRs>
<soap:Envelope xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Header>
<SessionId xmlns="http://www.opentravel.org/OTA/2003/05">A5363889-1895</SessionId>
<TimeStamp Start="2013-01-21T03:04:40.719Z" End="2013-01-21T03:04:40.969Z" xmlns="http://www.opentravel.org/OTA/2003/05"/>
</soap:Header>
<soap:Body>
<OpenHotelHubSessionCreateRS Version="5.000" xmlns="http://www.opentravel.org/OTA/2003/05">
<Status>Success</Status>
<SessionId>A5363889-1895</SessionId>
</OpenHotelHubSessionCreateRS>
</soap:Body>
</soap:Envelope>
</BkRs>
<BkTxnStat>S</BkTxnStat>
<BkSTime>2013-01-21T03:04:30Z</BkSTime>
<BkETime>2013-01-21T03:04:42Z</BkETime>
<BkTotExecTime>12612</BkTotExecTime>
</BkEndIntg>
<BkEndIntg>
<BkCorID>0ed7216b65ee47e</BkCorID>
<BkTxnNm>InitiateSessions</BkTxnNm>
<BkSvcProviderNm>JXTS</BkSvcProviderNm>
<BkUrl>MTPROFILE_JXTS</BkUrl>
<BkRq>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:exsl="http://exslt.org/common">
<soap:Header>
<Security xmlns="http://www.opentravel.org/OTA/2003/05">
<UsernameToken>
<Username>xxxxxxx</Username>
<Password>xxxxxx</Password>
</UsernameToken>
</Security>
</soap:Header>
<soap:Body>
<OpenHotelHubSessionCreateRQ Version="5.000" xmlns="http://www.opentravel.org/OTA/2003/05">
<POS>
<Source DKNumber="xxxxxx"/>
</POS>
</OpenHotelHubSessionCreateRQ>
</soap:Body>
</soap:Envelope>
</BkRq>
<BkRs>
<soap:Envelope xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Header>
<SessionId xmlns="http://www.opentravel.org/OTA/2003/05">A5363889-1895</SessionId>
<TimeStamp Start="2013-01-21T03:04:40.719Z" End="2013-01-21T03:04:40.969Z" xmlns="http://www.opentravel.org/OTA/2003/05"/>
</soap:Header>
<soap:Body>
<OpenHotelHubSessionCreateRS Version="5.000" xmlns="http://www.opentravel.org/OTA/2003/05">
<Status>Success</Status>
<SessionId>A5363889-1895</SessionId>
</OpenHotelHubSessionCreateRS>
</soap:Body>
</soap:Envelope>
</BkRs>
<BkTxnStat>F</BkTxnStat>
<BkSTime>2013-01-21T03:04:30Z</BkSTime>
<BkETime>2013-01-21T03:04:42Z</BkETime>
<BkTotExecTime>12612</BkTotExecTime>
</BkEndIntg>
<BkEndIntg>
<BkCorID>0ed7216b65ee47e</BkCorID>
<BkTxnNm>InitiateSessions</BkTxnNm>
<BkSvcProviderNm>TCT_SESSION</BkSvcProviderNm>
<BkUrl>TCT_SESSION</BkUrl>
<BkRq>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:exsl="http://exslt.org/common">
<soap:Header>
<Security xmlns="http://www.opentravel.org/OTA/2003/05">
<UsernameToken>
<Username>AXUSHWGU</Username>
<Password>hwgus11cu</Password>
</UsernameToken>
</Security>
</soap:Header>
<soap:Body>
<OpenHotelHubSessionCreateRQ Version="5.000" xmlns="http://www.opentravel.org/OTA/2003/05">
<POS>
<Source DKNumber="AXUSHW11CU"/>
</POS>
</OpenHotelHubSessionCreateRQ>
</soap:Body>
</soap:Envelope>
</BkRq>
<BkRs>
<soap:Envelope xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Header>
<SessionId xmlns="http://www.opentravel.org/OTA/2003/05">A5363889-1895</SessionId>
<TimeStamp Start="2013-01-21T03:04:40.719Z" End="2013-01-21T03:04:40.969Z" xmlns="http://www.opentravel.org/OTA/2003/05"/>
</soap:Header>
<soap:Body>
<OpenHotelHubSessionCreateRS Version="5.000" xmlns="http://www.opentravel.org/OTA/2003/05">
<Status>Success</Status>
<SessionId>A5363889-1895</SessionId>
</OpenHotelHubSessionCreateRS>
</soap:Body>
</soap:Envelope>
</BkRs>
<BkTxnStat>F</BkTxnStat>
<BkSTime>2013-01-21T03:04:30Z</BkSTime>
<BkETime>2013-01-21T03:04:42Z</BkETime>
<BkTotExecTime>12612</BkTotExecTime>
</BkEndIntg>
<Error>
<ErrCorID>0ed7216b65ee47e</ErrCorID>
<ErrSvcNm>mpgwProviderSessionsService</ErrSvcNm>
<ErrFuncNm>InitiateSessions</ErrFuncNm>
<ErrLevel>1</ErrLevel>
<SvcErrCode>15615</SvcErrCode>
<SvcErrMsg>dxfbzdfb</SvcErrMsg>
<BkErrCode>15155</BkErrCode>
<BkErrMsg>afbf</BkErrMsg>
<SysErrCode>12342234</SysErrCode>
<SysErrMsg>vdvdvsdvae</SysErrMsg>
</Error>
</LogData>'
  • JoVijay
    JoVijay
    1 Post

    Re: Calling a stored procedure with one parameter of data type XML

    ‏2015-03-10T20:08:13Z  

    Hi 

     

    I am also having a similar issue .  i need to insert xml file  nto ORACLE database from datapower . . In my code the second parameter is CLOB datatype 

    Any suggestions would be highly appreciated . 

    If i want to insert xml file into Oracle database invoking a stored procedure . So what is the datatype to use and how to do it ?? 

  • DSerbin
    DSerbin
    1 Post

    Re: Calling a stored procedure with one parameter of data type XML

    ‏2015-04-01T06:32:27Z  
    • JoVijay
    • ‏2015-03-10T20:08:13Z

    Hi 

     

    I am also having a similar issue .  i need to insert xml file  nto ORACLE database from datapower . . In my code the second parameter is CLOB datatype 

    Any suggestions would be highly appreciated . 

    If i want to insert xml file into Oracle database invoking a stored procedure . So what is the datatype to use and how to do it ?? 

    Hello, JoVijay

    In case of ORACLE try checking the "Enable Object Support" checkbox at the SQL Data Source settings page.