December 21, 2014 at 3:38 pm
I have the below xml and i need it to be in sql a table :
<?xml version="1.0" encoding="UTF-8"?>
<rts:RateMessage xmlns:rts="http://www.reuters.com/Rate" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.reuters.com/Rate.xsd ">
<FXRates Timestamp="20141203164900" Type="Cash">
<Rate Name="USD.SAR" Type ="FXRate" Buy="3.75" Sell="3.79" />
<Rate Name="EUR.SAR" Type ="FXRate" Buy="4.60" Sell="4.64" />
<Rate Name="GBP.SAR" Type ="FXRate" Buy="5.87" Sell="5.91" />
<Rate Name="CHF.SAR" Type ="FXRate" Buy="3.83" Sell="3.87" />
<Rate Name="AED.SAR" Type ="FXRate" Buy="1.00" Sell="1.04" />
<Rate Name="KWD.SAR" Type ="FXRate" Buy="12.84" Sell="12.88" />
<Rate Name="QAR.SAR" Type ="FXRate" Buy="1.01" Sell="1.05" />
<Rate Name="BHD.SAR" Type ="FXRate" Buy="9.93" Sell="9.97" />
<Rate Name="EGP.SAR" Type ="FXRate" Buy="0.50" Sell="0.54" />
<Rate Name="TRY.SAR" Type ="FXRate" Buy="1.66" Sell="1.70" />
</FXRates>
<FXRates Timestamp="20141203164900" Type="Transfer">
<Rate Name="USD.SAR" Type ="FXRate" Buy="3.76" Sell="3.78" />
<Rate Name="EUR.SAR" Type ="FXRate" Buy="4.61" Sell="4.63" />
<Rate Name="CAD.SAR" Type ="FXRate" Buy="3.29" Sell="3.31" />
<Rate Name="NZD.SAR" Type ="FXRate" Buy="2.91" Sell="2.93" />
<Rate Name="HKD.SAR" Type ="FXRate" Buy="0.47" Sell="0.49" />
<Rate Name="GBP.SAR" Type ="FXRate" Buy="5.88" Sell="5.90" />
<Rate Name="CHF.SAR" Type ="FXRate" Buy="3.84" Sell="3.86" />
<Rate Name="AUD.SAR" Type ="FXRate" Buy="3.15" Sell="3.17" />
<Rate Name="JPY.SAR" Type ="FXRate" Buy="0.029" Sell="0.031" />
<Rate Name="ZAR.SAR" Type ="FXRate" Buy="0.32" Sell="0.34" />
<Rate Name="AED.SAR" Type ="FXRate" Buy="1.01" Sell="1.03" />
<Rate Name="QAR.SAR" Type ="FXRate" Buy="1.02" Sell="1.04" />
<Rate Name="OMR.SAR" Type ="FXRate" Buy="9.74" Sell="9.76" />
<Rate Name="MAD.SAR" Type ="FXRate" Buy="0.41" Sell="0.43" />
<Rate Name="INR.SAR" Type ="FXRate" Buy="0.059" Sell="0.062" />
<Rate Name="KWD.SAR" Type ="FXRate" Buy="12.85" Sell="12.87" />
<Rate Name="BHD.SAR" Type ="FXRate" Buy="9.94" Sell="9.96" />
<Rate Name="EGP.SAR" Type ="FXRate" Buy="0.51" Sell="0.53" />
<Rate Name="JOD.SAR" Type ="FXRate" Buy="5.31" Sell="5.33" />
<Rate Name="PKR.SAR" Type ="FXRate" Buy="0.029" Sell="0.040" />
<Rate Name="TRY.SAR" Type ="FXRate" Buy="1.67" Sell="1.69" />
</FXRates>
<OtherRates Timestamp="20141203164900">
<Rate Name="XAU.USD" Type ="Metal" Last="1.66" />
<Rate Name="XAG.USD" Type ="Metal" Last="1.66" />
<Rate Name="WTI" Type="Brent" Last="1.45" />
<Rate Name="SAR" Type ="IR" Last="1.0100" />
<Rate Name="USD" Type ="IR" Last="1.0200" />
<Rate Name="EUR" Type ="IR" Last="1.0255" />
<Rate Name="GBP" Type ="IR" Last="1.0400" />
<Rate Name="TASI" Type ="Index" Last="" />
<Rate Name="DFMGI" Type ="Index" Last="" />
<Rate Name="ADSMI" Type ="Index" Last="" />
<Rate Name="KWSEIDX" Type ="Index" Last="" />
<Rate Name="MSM30" Type ="Index" Last="" />
<Rate Name="BHSEASI" Type ="Index" Last="" />
<Rate Name="DOW" Type ="Index" Last="" />
<Rate Name="NASDAQ" Type ="Index" Last="" />
<Rate Name="FTSE" Type ="Index" Last="" />
<Rate Name="DAX" Type ="Index" Last="" />
<Rate Name="CAC" Type ="Index" Last="" />
<Rate Name="DSM" Type ="Index" Last="" />
</OtherRates>
</rts:RateMessage>
December 21, 2014 at 4:25 pm
What did you try so far?
Please show us your current query and where you get stuck.
December 22, 2014 at 12:20 am
The problem that i don't know from where to start, i need to have table to store the data
December 22, 2014 at 2:29 am
Start with MSDN XML chapter http://msdn.microsoft.com/en-us/library/bb522446.aspx. Specifically, you need xml namespace processing http://msdn.microsoft.com/en-us/library/ms177400.aspx
For example
declare @x XML =
'<?xml version="1.0" encoding="UTF-8"?>
<rts:RateMessage xmlns:rts="http://www.reuters.com/Rate" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.reuters.com/Rate.xsd ">
<FXRates Timestamp="20141203164900" Type="Cash">
<Rate Name="USD.SAR" Type ="FXRate" Buy="3.75" Sell="3.79" />
<Rate Name="EUR.SAR" Type ="FXRate" Buy="4.60" Sell="4.64" />
<Rate Name="GBP.SAR" Type ="FXRate" Buy="5.87" Sell="5.91" />
<Rate Name="CHF.SAR" Type ="FXRate" Buy="3.83" Sell="3.87" />
<Rate Name="AED.SAR" Type ="FXRate" Buy="1.00" Sell="1.04" />
<Rate Name="KWD.SAR" Type ="FXRate" Buy="12.84" Sell="12.88" />
<Rate Name="QAR.SAR" Type ="FXRate" Buy="1.01" Sell="1.05" />
<Rate Name="BHD.SAR" Type ="FXRate" Buy="9.93" Sell="9.97" />
<Rate Name="EGP.SAR" Type ="FXRate" Buy="0.50" Sell="0.54" />
<Rate Name="TRY.SAR" Type ="FXRate" Buy="1.66" Sell="1.70" />
</FXRates>
<FXRates Timestamp="20141203164900" Type="Transfer">
<Rate Name="USD.SAR" Type ="FXRate" Buy="3.76" Sell="3.78" />
<Rate Name="EUR.SAR" Type ="FXRate" Buy="4.61" Sell="4.63" />
<Rate Name="CAD.SAR" Type ="FXRate" Buy="3.29" Sell="3.31" />
<Rate Name="NZD.SAR" Type ="FXRate" Buy="2.91" Sell="2.93" />
<Rate Name="HKD.SAR" Type ="FXRate" Buy="0.47" Sell="0.49" />
<Rate Name="GBP.SAR" Type ="FXRate" Buy="5.88" Sell="5.90" />
<Rate Name="CHF.SAR" Type ="FXRate" Buy="3.84" Sell="3.86" />
<Rate Name="AUD.SAR" Type ="FXRate" Buy="3.15" Sell="3.17" />
<Rate Name="JPY.SAR" Type ="FXRate" Buy="0.029" Sell="0.031" />
<Rate Name="ZAR.SAR" Type ="FXRate" Buy="0.32" Sell="0.34" />
<Rate Name="AED.SAR" Type ="FXRate" Buy="1.01" Sell="1.03" />
<Rate Name="QAR.SAR" Type ="FXRate" Buy="1.02" Sell="1.04" />
<Rate Name="OMR.SAR" Type ="FXRate" Buy="9.74" Sell="9.76" />
<Rate Name="MAD.SAR" Type ="FXRate" Buy="0.41" Sell="0.43" />
<Rate Name="INR.SAR" Type ="FXRate" Buy="0.059" Sell="0.062" />
<Rate Name="KWD.SAR" Type ="FXRate" Buy="12.85" Sell="12.87" />
<Rate Name="BHD.SAR" Type ="FXRate" Buy="9.94" Sell="9.96" />
<Rate Name="EGP.SAR" Type ="FXRate" Buy="0.51" Sell="0.53" />
<Rate Name="JOD.SAR" Type ="FXRate" Buy="5.31" Sell="5.33" />
<Rate Name="PKR.SAR" Type ="FXRate" Buy="0.029" Sell="0.040" />
<Rate Name="TRY.SAR" Type ="FXRate" Buy="1.67" Sell="1.69" />
</FXRates>
<OtherRates Timestamp="20141203164900">
<Rate Name="XAU.USD" Type ="Metal" Last="1.66" />
<Rate Name="XAG.USD" Type ="Metal" Last="1.66" />
<Rate Name="WTI" Type="Brent" Last="1.45" />
<Rate Name="SAR" Type ="IR" Last="1.0100" />
<Rate Name="USD" Type ="IR" Last="1.0200" />
<Rate Name="EUR" Type ="IR" Last="1.0255" />
<Rate Name="GBP" Type ="IR" Last="1.0400" />
<Rate Name="TASI" Type ="Index" Last="" />
<Rate Name="DFMGI" Type ="Index" Last="" />
<Rate Name="ADSMI" Type ="Index" Last="" />
<Rate Name="KWSEIDX" Type ="Index" Last="" />
<Rate Name="MSM30" Type ="Index" Last="" />
<Rate Name="BHSEASI" Type ="Index" Last="" />
<Rate Name="DOW" Type ="Index" Last="" />
<Rate Name="NASDAQ" Type ="Index" Last="" />
<Rate Name="FTSE" Type ="Index" Last="" />
<Rate Name="DAX" Type ="Index" Last="" />
<Rate Name="CAC" Type ="Index" Last="" />
<Rate Name="DSM" Type ="Index" Last="" />
</OtherRates>
</rts:RateMessage> ';
with xmlnamespaces ('http://www.reuters.com/Rate' as rts)
select n1.a.value('fn:local-name(.)','varchar(20)') as rateType
,n1.a.value ('.[1]/@Timestamp','varchar(20)') as rateTimestamp
from @x.nodes('rts:RateMessage/*') n1(a);
'fn:local-name(.)' and other stuff are W3C XPath, XQuery expressions. They aren't documented well at MS site, see http://www.w3schools.com or some other tutorials.
December 22, 2014 at 3:38 am
How can i read XML from path , then table retrive as below :
rate Type - rate Timestamp - Type - rate Name - rate type - Buy - sell
December 22, 2014 at 3:59 am
Having a table of parent nodes, n1(a), you can continue getting child nodes applying the same nodes() xml function to parent nodes. E.g.
with xmlnamespaces ('http://www.reuters.com/Rate' as rts)
select n1.a.value('fn:local-name(.)','varchar(20)') as rateType
,n1.a.value ('./@Timestamp','varchar(20)') as rateTimestamp
,n2.a.value ('./@Name','varchar(20)') as rName
,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 22, 2014 at 4:19 am
amazing, two things and will not ask again " Sorry "
1- How read XML from local drive
2- Timestamp is datetime how to format it into two column one for date and other time
Thanks anyway
December 22, 2014 at 5:12 am
🙂 OK.
declare @x xml;
set @x = (SELECT * FROM OPENROWSET(BULK '<YOUR PATH ON SQL SERVER MACHINE>\rates.xml', SINGLE_CLOB) AS x);
with xmlnamespaces ('http://www.reuters.com/Rate' as rts)
select n1.a.value('fn:local-name(.)','varchar(20)') as rateType
,cast(left(n1.a.value ('./@Timestamp','varchar(20)'),8) as date )as rateDate
,cast(stuff(stuff(right(n1.a.value ('./@Timestamp','varchar(20)'),6),5,0,':'),3,0,':') as time(0)) as rateTime
,n2.a.value ('./@Name','varchar(20)') as rName
,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) ;
Prerequisites. Sql server account must have access to the file.
File must have ANSI encoding.
Good luck.
December 22, 2014 at 8:04 am
Thank you very much
December 27, 2014 at 7:19 am
This the final script , i need the paths to be selected from table for example :
declare @path = (Select Description from Lookup where code ='XML Path';
then i need to replace with any pathes in the below :
-- First Enable XP_cmdshell
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
-- Second check if the file exists
declare @iFileExists INT;
EXEC master..xp_fileexist 'D:\Work\Appspace\Currency\currency.xml',
@iFileExists OUTPUT
-- Check if the file available
if (select @iFileExists) = 1
Begin
declare @x xml;
-- Delete all data from existing table and then insert from new file
Delete From Currency_Rates
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)
)
EXEC master..xp_cmdshell 'Del D:\Work\Appspace\Currency\currency.xml'
End
Else
Begin
Update Currency_Rates
Set Buy= 0
,Sell= 0
,Other= 0
End
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply