December 28, 2014 at 4:03 pm
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) )
December 29, 2014 at 11:32 am
I'm pretty sure no one here has any idea what you're question is. Please be specific as to what you want, with as many details as possible to describe the what and the why of the problem, as well as the how of what you've already tried.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 29, 2014 at 4:09 pm
Ok , i have XML file and i import it to table from path, no wi need just to replace "c:\------" to just @path
the below is my script so what i need is to change 'D:\Work\Appspace\Currency\currency.xml' to @path
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)
)
December 29, 2014 at 4:17 pm
I'm pretty sure this has to be done with dynamic SQL, as I believe the OPENROWSET requires a text string input, and can't accept a variable, so that's the only possible solution. You'll have to DECLARE a varchar variable that holds the query and rather than having D:\ and the rest of that path, you should probably code something like :XX:, and then construct dynamic SQL to use the REPLACE function on the :XX: with @path.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 29, 2014 at 4:26 pm
if you please can you just help me to do it
December 29, 2014 at 8:17 pm
That's how you learn. Try, fail, modify... hit F1, read Books Online, try again.
DECLARE @NewPath VARCHAR(100) = 'C:\Dir\Subdir\File.xml';
DECLARE @sqlstring VARCHAR(200) = 'SELECT * FROM OPENROWSET(BULK ' + '''strFileName'', SINGLE_CLOB)';
SET @sqlstring = REPLACE(@sqlString,'strFileName',@NewPath)
PRINT @sqlstring;
December 29, 2014 at 8:37 pm
As posted by pietlinden, yes, that's how you learn. Check out Books Online (as was suggested), and don't expect someone else to do ALL the work for you.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 30, 2014 at 3:57 am
Steve, please don't give me your advice, if you need to help you will do, if you don't , just don't reply, because sometimes anyone can search ,learn and read a lot of books but it is stuck with him.
Thanks pietlinden for your help and your advice
December 30, 2014 at 11:11 am
I was being serious when I said that's how you learn. Try, test, fail, read, try... if you aren't willing to do that, then pay someone to help you. Nobody gets paid here, so be polite, and you stand a much better chance of getting an answer that actually works.
December 30, 2014 at 1:15 pm
""" so be polite """ You said . amazing reply in technical forums, thanks by the way:-)
December 30, 2014 at 2:18 pm
I had decided to just stop posting on this topic until I saw your most recent post. I may not have the right interpretation of it, as it is rather difficult to understand what you were trying to say, but it seems disrespectful that you would call out the "be polite" comment as an "amazing reply in technical forums", as either a good kind or a bad kind of amazing would still be the wrong thing to say (if you meant good, then you should have then apologized to me, and if you meant bad, then why say "thanks by the way", unless it was just more sarcasm?).
As at least one other person thinks you weren't being polite when you asked to not have my advice, I'm pretty sure I wasn't the one causing the problem by asking you not to expect others to do all the work for you. If what I said upset you, then maybe you need to better understand what folks that contribute here are doing. We volunteer our time and effort to answer questions, and we don't get paid for it. We do so because there's a pretty good chance that when we have a question, an answer will be there for us. Getting upset because you're not provided a complete and total answer that requires zero effort on your part just isn't reasonable behavior. You can and should be expected to participate in helping yourself solve your problem. If we did all the work for you, no learning would take place, and you would soon become dependent on the answers.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 31, 2014 at 1:51 pm
ikrami2000 (12/30/2014)
Steve, please don't give me your advice, if you need to help you will do, if you don't , just don't reply, because sometimes anyone can search ,learn and read a lot of books but it is stuck with him.Thanks pietlinden for your help and your advice
If you are not looking for advise you came to the wrong place. This is a forum that is designed to help those who have questions and we do our best to offer help as a courtesy. I see nothing posted on this thread that warrants you being upset.
----------------------------------------------------
January 1, 2015 at 2:40 am
Thanks to all for your advices
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply