Post reply

XML to Table

  • December 21, 2014 at 3:38 pm

    #299587

    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

    #1765896

    What did you try so far?

    Please show us your current query and where you get stuck.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • December 22, 2014 at 12:20 am

    #1765917

    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

    #1765926

    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

    #1765933

    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

    #1765938

    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

    #1765940

    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

    #1765954

    🙂 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

    #1765984

    Thank you very much

  • December 27, 2014 at 7:19 am

    #1766554

    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

Cookies on SQLServerCentral

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