HELP Retrieving remote XML and returning ROWSET

Within a stored procedure, I need to retrieve XML over HTTP from an external
URL, then load some stuff into a recordset and return as such.
Like this, sort-of:
-- *******************************
create procudure queryremote (@.param)
declare @.xmldoc varchar(2000)
set @.xmldoc = (GET 'HTTP://someplaceservesXML.com/script.ext?param=' +
@.param)
sp_preparedocument (@.xmldoc etc.)
SELECT * FROM OPENXML(thexmlthing, "/xpath")
Return
-- *********************************
Just to be clear, this is exactly the opposite of what everyone is trying to
do; many articles on getting XML OUT of SQL server and putting XML data INTO
SQL server, over HTTP.
This requirement is to provide backward -compatibility to
SQL-recordset-aware-only applications. Making an HTTP XML query service
LOOK like an SQL recordset.
Email and post please, thanks a lot!
Richard Weerts
rweerts@.ndis.us
In SQL Server 2000 you have two options:
Do the get on the mid-tier and pass the XML as an argument to the stored
proc.
Write your own extended stored proc to GET the data.
In SQL Server 2005, you can write the function using CLR instead of writing
an extended stored proc.
HTH
Michael
"411XML Richard Weerts" <rweerts@.ndis.us> wrote in message
news:OLr5YmXfEHA.2848@.TK2MSFTNGP10.phx.gbl...
>
> Within a stored procedure, I need to retrieve XML over HTTP from an
> external
> URL, then load some stuff into a recordset and return as such.
> Like this, sort-of:
> -- *******************************
> create procudure queryremote (@.param)
> declare @.xmldoc varchar(2000)
> set @.xmldoc = (GET 'HTTP://someplaceservesXML.com/script.ext?param=' +
> @.param)
> sp_preparedocument (@.xmldoc etc.)
> SELECT * FROM OPENXML(thexmlthing, "/xpath")
> Return
> -- *********************************
> Just to be clear, this is exactly the opposite of what everyone is trying
> to
> do; many articles on getting XML OUT of SQL server and putting XML data
> INTO
> SQL server, over HTTP.
> This requirement is to provide backward -compatibility to
> SQL-recordset-aware-only applications. Making an HTTP XML query service
> LOOK like an SQL recordset.
> Email and post please, thanks a lot!
> Richard Weerts
> rweerts@.ndis.us
>
>