XML : The parameter sent to SQL Server from XML file lost leading zeros

thank you for reading this question. I appreciate any help...for we've been stuck with this issue for a while and need your help.

We have a stored procedure provided by the WMS company which creates a tracking record. 1 of the parameter MUST have the leading zeros to search the transaction record to send the data to the accounting system.
The SP is executed when the shipping label is scanned by a small label making system. This system uses a XML file to do the process when the label is scanned and at the end of the file, the SP is called with the necessary parameters. When we checked the parameters at scanning, this problematic parameter has the leading zeros but no single quotes at the front or at the end. Then when the SQL Server executes the SP, the leading zeros are gone. At the end, the transaction data is not found for these missing zeros, our accounting system doesn't receive any data.

We asked and worked a lot with the vendor of the label making system and they gave up. Our WMS never tries to help us.

We did everything we could think of to add leading zeros and single quotes, which all ended up with failure.

The code in XML file is as below: hope someone can give us some light...

Set @ContainerLabelId = '' + CONVERT(CHAR, RIGHT('000000000'+ CONVERT(VARCHAR,'$OrderNumber'),9)) + ''

exec [dbo].[spCreateTrackingRecord] $TrackingNumber,@OrderID,1,@ShipDate,MM, NULL,NULL,$Weight,$Postage,NULL,jesquivel,NULL,NULL,NULL,@ContainerLabelId,I,NULL,NULL,NULL

Thank you for your help!

No comments:

Post a Comment