using msxml2.domdocument error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

using msxml2.domdocument error

Author

Topic

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-11-12 : 13:35:32

hi,
I am trying to use msxml2.domdocument in a stored procedure
on checking the ready state it always shows me it is three,somehow it does not load the whole document.
here is the code:

alter procedure calldlltestxml
as
begin
declare @returnval int
declare @hr int
declare @object int
declare @return varchar(255)
declare @productid int
declare @@delaylength datetime
declare @padurl varchar(100)
declare @src int
declare @desc varchar(1000)
declare @product varchar(10)
DECLARE @@RETURNINFO varchar(255)

begin

exec @hr=sp_oacreate 'MSXML2.DOMDocument' ,@object out
--
exec sp_oageterrorinfo @object,@src out,@desc out

SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
IF @hr <> 0
begin
select 'can not create object'
end

declare @para varchar(3000)
exec @hr= sp_oamethod @object,'Load',null,'http://www.somesite.com/st1.xml'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

IF @hr <> 0
BEGIN

EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc

end

declare @harshal varchar(20)
declare @property int

exec @hr =sp_oagetproperty @object,'ReadyState',@property out
select @property as readystate

while @property<>4
begin
waitfor delay '0:0:15'
exec @hr =sp_oagetproperty @object,'ReadyState',@property out
select @property as readystate

break
end

declare @xml varchar(3000)

exec @hr=sp_oamethod @object,'xml',@xml out
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

IF @hr <> 0
BEGIN
select 'sachin'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
end

select @xml as xml1
exec @hr =sp_oagetproperty @object,'ReadyState',@property out
select @property as readystate
-- declare @doc varchar(3000)
-- EXEC sp_xml_preparedocument @xml OUTPUT, @doc
-- select @doc

end

EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- SELECT @hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
IF @hr <> 0
BEGIN
select 'harshal'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
end

exec @hr=sp_oadestroy @object
select @returnval
end

thankyou,
harshal.

The Judgement of the Judge is as good as the Judge.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-12 : 17:06:22

MSXML2 does not support HTTP Requests
http://support.microsoft.com:80/support/kb/articles/Q237/9/06.ASP&NoWebContent=1

MSXML3 does support this using ServerXMLHTTP Directly
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsdk30/htm/xmobjxmldomserverxmlhttp_using_directly.asp

I have included a sample that you can work up to meet your needs.


declare @hr int 
declare @object int 
declare @src int 
declare @desc varchar(1000) 

EXEC @hr=sp_oacreate  'Msxml2.ServerXMLHTTP.3.0', @object out 
IF @hr <> 0 
BEGIN 
	EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
	SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc 
END

EXEC @hr= sp_oamethod @object,'open',null,'GET','http://server/webfiles/test/test.xml'

IF @hr <> 0 
BEGIN 
	EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
	SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc 
END

EXEC @hr= sp_oamethod @object,'send',null 

IF @hr <> 0 
BEGIN 
	EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
	SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc 
END

DECLARE @xml varchar(8000)

EXEC @hr= sp_oagetproperty @object,'responseXML.xml',@xml OUT

IF @hr <> 0 
BEGIN 
	EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
	SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc 
END

select @xml 'XML'

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-11-13 : 00:05:34

Thanks a lot.

The Judgement of the Judge is as good as the Judge.

rhaynie
Starting Member

1 Post

Posted - 2011-11-03 : 20:30:48

This would only work so long your XML is smaller than 8000. How would your handle a larger response?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources