import xml file from web page in SQL

  • hi,

    I want to import xml file directly from web page into microsoft sql table.

    At the moment the import is done after the XML file is downloaded local.

    I want to skip this step to manualy download the file.

    It can be done in SQL?

    when i change the path i get this error: Cannot bulk load because the file "http://www.bnr.ro/nbrfxrates.xml" could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.)

    thanx.

    below is the code

    DECLARE @idoc INT

    DECLARE @doc XML

    SET @Doc = (SELECT * FROM OPENROWSET(BULK 'F:\Folderbrfxrates.xml', SINGLE_CLOB) AS xmlData) -- 1 LOCAL works

    --SET @Doc = (SELECT * FROM OPENROWSET(BULK 'http://www.bnr.ro/nbrfxrates.xml', SINGLE_CLOB) AS xmlData) -- from web i get error

    SELECT @Doc

    EXEC sp_xml_preparedocument

    @idoc OUTPUT

    ,@doc

    ,'<DataSet xmlns:a="http://www.bnr.ro/xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.bnr.ro/xsd nbrfxrates.xsd"/>'

    INSERT INTO tbl_CursValutar

    (

    [Moneda]

    ,[Multiplicator]

    ,[Curs]

    ,[DataCurs]

    )

    (

    SELECT * FROM OPENXML(@idoc, '/a:DataSet/a:Body/a:Cube/a:Rate', 1) WITH

    (

    currency VARCHAR(3)

    ,multiplier INT

    ,Rate NUMERIC(18, 10) 'text()'

    ,DATE DATETIME '/a:DataSet/a:Body/a:Cube/@date'

    )

    )

    UPDATE tbl_CursValutar

    SET multiplicator = isnull(multiplicator, 1)

    EXEC sp_xml_removedocument @idoc

Viewing post 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply