asked
OPENROWSET(BULK @path )
Need to change the path to @path need the line : (SELECT * FROM OPENROWSET(BULK 'D:\Work\Appspace\Currency\currency.xml', SINGLE_CLOB) AS x); To (SELECT * FROM OPENROWSET(BULK @path, SINGLE_CLOB) AS x); ------------------------------------- set @x = (SELECT * FROM OPENROWSET(BULK 'D:\Work\Appspace\Currency\currency.xml', SINGLE_CLOB) AS x); with xmlnamespaces ('
http://www.reuters.com/Rate' as rts) -- Insert into the table Insert Into Currency_Rates (Rate_Type ,Date,Time,From_Currency,To_Currency,Type,Buy,Sell,Other) ( select n1.a.value('fn:local-name(.)','varchar(20)') as rateType ,cast(left(n1.a.value ('./@Timestamp','varchar(20)'),8) as date )as rateDatestamp ,cast(stuff(stuff(right(n1.a.value ('./@Timestamp','varchar(20)'),6),5,0,':'),3,0,':') as time(0)) as rateTimestamp ,(select SUBSTRING(n2.a.value ('./@Name','varchar(20)'),0,CHARINDEX('.',n2.a.value ('./@Name','varchar(20)'),0)))as CurrencyFrom ,(select substring(n2.a.value ('./@Name','varchar(20)'),charindex('.',n2.a.value ('./@Name','varchar(20)'))+1 ,250)) as CurrencyTo ,n2.a.value ('./@Type','varchar(20)') as rType ,n2.a.value ('./@Buy','float') as rBuy ,n2.a.value ('./@Sell','float') as rSell ,n2.a.value ('./@Last','float') as rLast from @x.nodes('rts:RateMessage/*') n1(a) cross apply n1.a.nodes('Rate') n2(a) )
The OPENROWSET does not accept parameters so you cannot pass @pass directly to it. Instead, you can use the parameterized query to populate the xml variable named @x, which achieves the same result. One thing I would like to point out is that whenever there is a need to use the OPENROWSET to read the contents of the entire xml file, it is much better to use the **single\_blob** instead of **single\_clob**. This might result in the small performance gain. Here is the script you can use, assuming that you have the line reading declare @x xml somewhere earlier in the script: -- this is the original line commented out -- set @x = (SELECT * FROM OPENROWSET(BULK 'D:\Work\Appspace\Currency\currency.xml', SINGLE_CLOB) AS x); declare @path varchar(255) = 'D:\Work\Appspace\Currency\currency.xml'; declare @sql nvarchar(max); select @sql = 'select @xmlOut = x.BulkColumn from openrowset( bulk ''' + @path + ''', single_blob) x'; exec sp_executesql @sql, N'@xmlOut xml out', @xmlOut = @x out; -- test the variable to see that it is now populated -- select @x Hope this helps. Oleg
15 People are following this question.
