Re: Help Retrieving XML with HTTP within stored procedure ..

From: Erland Sommarskog (esquel_at_sommarskog.se)
Date: 08/08/04

  • Next message: Itzik: "FOR XML RAW" 
    Date: Sun, 8 Aug 2004 08:26:17 +0000 (UTC)

    [posted and mailed, please reply in news]

    Richard Weerts (rweerts@ndis.us) writes:
    > 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

    If that XML document is not longer than 4000 charcters, you could write
    an extended stored prodedure to fetch the document. Alternatively, you
    could use a COM object and use sp_OACreate and friends to retrieve the
    information.

    The reason the limit would be 4000 characters, is because that is big
    you can make an nvarchar value. (Need nvarchar for OPENXML). For bigger
    values you need ntext, but since you cannot assign to them in a stored
    procedure, you lose.

    If the documents can be any size, the possibility I can think would be
    to use OPENROWSET, and then implement a rowset provider that produces
    the same output as OPENXML. It goes without saying that this is a major
    undertaking.
     
    You may also want to inquire in microsoft.public.sqlserver.xml for
    more suggestions. 

    -- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
  • Next message: Itzik: "FOR XML RAW"

    Relevant Pages

    • Re: Parameters
      ... ALTER FUNCTION dbo.My_Function2(@order nvarchar (10), ... In the listbox simple selection mode i used the following code: ... > generated sql and drop the stored procedure, or you can make the stored ... > It looks like your performing a query against a stored procedure. ...
      (microsoft.public.dotnet.framework.aspnet)
    • Re: problems inserting into sql using SqlHelper.ExecuteScalar
      ... Here is the code and the stored procedure I'm ... > @Cont1FirstName nvarchar, ... > @Presort bit, ... > It returns the correct id number but the varchar fields are only recieving ...
      (microsoft.public.dotnet.framework.aspnet)
    • Re: STRANGE PROBLEM
      ... It is damn bad practise lah encik. ... im looping SqlCommand in a while loop. ... @MarkaLink NVARCHAR, ... You add parameters to the stored procedure in a loop.So u add the parameters while true.You should not add parameters in a loop ...
      (microsoft.public.dotnet.languages.csharp)
    • Datatype-convertion in TSQL
      ... Can I convert the data within the Stored Procedure for selecting and sorting ... The client wants to stay with the comma-format because it is common-used ... The format can be TSQL-format in the resultset of a Stored Procedure without ...
      (comp.databases.ms-sqlserver)
    • Re: Biztalk 2004 Single SQL Adapter for multiple inserts.
      ... stored procedure as parameterand used OpenXML to insert ... > Is there any way by which i can use a single SQLAdapter ... > I have a input file which has records of different types. ...
      (microsoft.public.biztalk.general)