October 6, 2003 at 2:54 pm
I am creating a stored procedure that will get rate information from UPS. They require you to send two XML files as one to UPS via HTTP. I was trying to use the sp_OAMethod @ObjXMLHTTP 'send' method. But it returns an error:
Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 164
XML parsing error: Invalid xml declaration.
Is this because the two files together do not make a valid XML document? If so is there another way to send via HTTP using TSQL?
Thanks,
David
October 7, 2003 at 5:26 am
Can you post more code?
In particular where you are setting the object '@ObjXMLHTTP' up.
October 7, 2003 at 10:31 am
Thanks for looking at this. Here is more code:
CREATE proc bsp_UPS_shipping
@DocNum varchar(20),
@Weight numeric(6,1),
@Service varchar(10),
@ShipToZip varchar(20),
@ShipToCountry varchar(20),
@IsTest varchar(100),
@Charge money output,
@Message varchar(100) output
as
declare @URL VARCHAR(100)
declare @XpciVersion varchar(100)
declare @CustomerContext varchar(50)
declare @RequestAction varchar(15)
declare @AccessKey varchar(50)
declare @UID varchar(50)
declare @PWD VARCHAR(50)
declare @PickTypeCode varchar(2)
declare @CustomerClassificationCode varchar(2) declare @ShipperPostalCode varchar(9) declare @ShipperCountryCode varchar(2)
declare @PackageTypeCode varchar(2)
declare @XMLRequest varchar(8000)
declare @XMLResponse varchar(8000)
if ltrim(rtrim(@IsTest))='test'
select @URL='https://wwwcie.ups.com/ups.app/xml/rate'
else
select @URL='https://www.ups.com/ups.app/xml/rate'
select @XpciVersion='1.0001'
select @CustomerContext='CompanyNameHere'
select @RequestAction='Rate'
select @AccessKey='AccessKeyHere'
select @UID='UserIDHere'
select @PWD='PasswordHere'
select @PickTypeCode='01'
select @CustomerClassificationCode='01'
select @ShipperPostalCode='73118'
select @ShipperCountryCode='US'
SELECT @ShipToZip=ltrim(rtrim(replace(@ShipToZip,'-','')))
select @PackageTypeCode='02'
select @XMLRequest= <XML is concatenated here>
DECLARE @ObjXMLHTTP int
declare @ObjXMLDOC int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
declare @xmldoc int
EXEC @hr = sp_OACreate 'MSXML2.XMLHTTP', @ObjXMLHTTP OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ObjXMLHTTP, @src OUT, @desc OUT
END
EXEC @hr = sp_OAMethod @ObjXMLHTTP, 'Open',null,'POST', @URL, false
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ObjXMLHTTP, @src OUT, @desc OUT
END
EXEC @hr = sp_OACreate 'MSXML2.DOMDocument', @ObjXMLDOC OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ObjXMLDOC, @src OUT, @desc OUT
END
EXEC @hr = sp_OAMethod @ObjXMLDOC, 'loadXML',null,@ObjXMLDOC
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ObjXMLDOC, @src OUT, @desc OUT
END
exec sp_xml_preparedocument @xmldoc output, @XMLRequest
EXEC @hr = sp_OAMethod @ObjXMLHTTP, 'send' ,null,@xmldoc
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ObjXMLHTTP, @src OUT, @desc OUT
END
EXEC @hr = sp_OAGetProperty @ObjXMLHTTP, 'responseText', @XMLResponse OUT
EXEC @hr = sp_OADestroy @ObjXMLHTTP
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ObjXMLHTTP, @src OUT, @desc OUT
END
else
print '@ObjXMLHTTP Destroyed successfully'
EXEC @hr = sp_OADestroy @ObjXMLDOC
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ObjXMLDOC, @src OUT, @desc OUT
END
Print '@XMLResponse is:'+@XMLResponse
/* To Use:
DECLARE @rc int
DECLARE @DocNum varchar(20)
DECLARE @Weight numeric(6,1)
DECLARE @Service varchar(10)
DECLARE @ShipToZip varchar(20)
DECLARE @ShipToCountry varchar(20)
DECLARE @IsTest varchar(100)
DECLARE @Charge decimal(19,4)
DECLARE @Message varchar(100)
-- Set parameter values
select @Weight=10.0
select @Service=02
select @ShipToZip='44219'
select @ShipToCountry='US'
SELECT @IsTest='test'
select @Charge=0
EXEC @rc = [IT_DEV].[dbo].[bsp_eConnect_UPS_shipping] @DocNum, @Weight, @Service, @ShipToZip, @ShipToCountry, @IsTest, @Charge OUTPUT , @Message OUTPUT
print '@Charge is:'+str(@Charge, 10,2)
*/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply