import xml file from web page in SQL

• silviu_fn

  SSC Enthusiast

  Points: 107

  More actions

  June 12, 2015 at 5:49 am

  #299049

  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