June 12, 2015 at 5:49 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy