Topic
No replies
SystemAdmin
SystemAdmin
6772 Posts
ACCEPTED ANSWER

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>'