Results 1 to 12 of 12

Thread: Download XML file

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2016
    Posts
    8

    Download XML file

    Hello,

    Problem: I would like to download a XML file with exchange rates so I can further upload it into a SQL Server table.

    For the XML download, this is the code I have so far:
    Code:
    Dim webRequest As WebRequest = WebRequest.Create("https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml")
    Dim webResponse As WebResponse = webRequest.GetResponse
    Dim webStream As Stream = webResponse.GetResponseStream
    
    Dim reader As New StreamReader(webStream)
    reader.ReadToEnd()
    
    Dim xmlDoc As New XmlDocument
    xmlDoc.Load(webStream)
    I do see the data in webStream, but how can I save it to a XML file? I thought xmlDoc.Save would do the trick, but it doesn't.

    Thank you in advance
    Last edited by dday9; Mar 17th, 2021 at 12:20 PM. Reason: Formatted code and added code tags

  2. #2
    Hyperactive Member
    Join Date
    Oct 2018
    Posts
    276

    Re: Download XML file

    btw, for easier code readability, select your code and click on the button # in text editor. Your code will be then far more readable for us.
    Please dont forget to add good reputation if my advices were useful for you.
    How? Under this post there is "RATE THIS POST" button. Click on it.

  3. #3
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Download XML file

    There is an overload xmlDoc.Save(“filename.xml”)

    https://docs.microsoft.com/en-us/dot...e?view=net-5.0

  4. #4

    Thread Starter
    New Member
    Join Date
    Feb 2016
    Posts
    8

    Re: Download XML file

    Quote Originally Posted by .paul. View Post
    There is an overload xmlDoc.Save(“filename.xml”)

    https://docs.microsoft.com/en-us/dot...e?view=net-5.0
    Thank you for your reply. I doesn't work. What worked is the following:
    Code:
    Dim wc As New WebClient
            Dim xmlText As String = wc.DownloadString("https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml")
            File.WriteAllText("filename.XML", xmlText)

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Download XML file

    Quote Originally Posted by StevenVanAken View Post
    Thank you for your reply. I doesn't work. What worked is the following:
    Code:
    Dim wc As New WebClient
            Dim xmlText As String = wc.DownloadString("https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml")
            File.WriteAllText("filename.XML", xmlText)
    Why call DownloadString and then save the String to a file rather than just call DownloadFile?

  6. #6
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,754

    Re: Download XML file

    Quote Originally Posted by StevenVanAken View Post
    Thank you for your reply. I doesn't work. What worked is the following:
    Code:
    Dim wc As New WebClient
            Dim xmlText As String = wc.DownloadString("https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml")
            File.WriteAllText("filename.XML", xmlText)
    An alternative approach. That is a big file...

    Code:
            Try
                Dim wc As New Net.WebClient
                Dim xe As XElement
                xe = XElement.Parse(wc.DownloadString("https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml"))
                xe.Save("foo.xml")
            Catch ex As Exception
                Stop
            End Try
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  7. #7

    Thread Starter
    New Member
    Join Date
    Feb 2016
    Posts
    8

    Re: Download XML file

    Thank you dbasnett!

  8. #8

    Thread Starter
    New Member
    Join Date
    Feb 2016
    Posts
    8

    Re: Download XML file

    Now I downloaded the XML file, I want to load it into my SQL Server. But first I want to read the content. I tried this:

    Code:
    Imports System.IO
    Imports System.Xml
    
    Public Class XML_Download
        Const FILENAME As String = "F:\Euro_FX.XML"
        Public Sub Get_XML_File()
            Dim wc As New Net.WebClient
            Dim xe As XElement
            xe = XElement.Parse(wc.DownloadString("https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml"))
            xe.Save(FILENAME)
    
            Dim xmlDoc As XmlDocument = New XmlDocument()
            Dim xmlnode As XmlNodeList
            Dim i As Integer
            Dim str As String
            Dim fs As New FileStream(FILENAME, FileMode.Open, FileAccess.Read)
            xmlDoc.Load(fs)
    
            ' ==> HERE IS THE PROBLEM: xmlnode = xmlDoc.GetElementsByTagName("Cube")
    
            For i = 0 To xmlnode.Count - 1
                xmlnode(i).ChildNodes.Item(0).InnerText.Trim()
                str = xmlnode(i).ChildNodes.Item(0).InnerText.Trim() & "  " & xmlnode(i).ChildNodes.Item(1).InnerText.Trim() & "  " & xmlnode(i).ChildNodes.Item(2).InnerText.Trim()
                MsgBox(str)
            Next
            fs.Close()
    
        End Sub
    End Class
    Public Class Euro_Exchange
            Public Cube_Time As Date
            Public Cube_Currency As String
        Public Cube_Rate As Decimal
    End Class
    The "xmlnode = xmlDoc.GetElementsByTagName("Cube")" does not return any value although it is clearly visible in the file:

    <?xml version="1.0" encoding="utf-8"?>
    <gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
    <gesmes:subject>Reference rates</gesmes:subject>
    <gesmes:Sender>
    <gesmes:name>European Central Bank</gesmes:name>
    </gesmes:Sender>
    <Cube>
    <Cube time="2021-03-17">
    <Cube currency="USD" rate="1.1907" />
    <Cube currency="JPY" rate="130.02" />
    <Cube currency="BGN" rate="1.9558" />
    <Cube currency="CZK" rate="26.159" />
    <Cube currency="DKK" rate="7.436" />
    <Cube currency="GBP" rate="0.85668" />
    The intention is to have for every date (<Cube time>) a line per <Cube currency> and <rate>. For example: "2021-03-17" / "USD" / "1.1907", second line "2021-03-17" / "JPY" / "130.02", etc...

    The <Cube> is not recognized somehow. What am I doing wrong?

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Download XML file

    It's the namespace that's getting in the way. Might want to use SelectNodes instead and use an xPath to get the Cubes...
    I think it'll look something like this:
    Code:
            xmlDoc.Load(fs)
        Dim cube as XmlNode
        Dim nodeList as XmlNodeList 
        Dim root as XmlNode = xmlDoc.DocumentElement
    
        nodeList=root.SelectNodes("descendant::Cube")
     
        'Change the price on the books.
        for each cube in nodeList      
          str = cube.Attributes("currency").value & " " & cube.Attribute("rate").value
          MessageBox.Show(str)
        next
    Think that's right... or close enough that it should get you looking in the right direciton.

    =tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    New Member
    Join Date
    Feb 2016
    Posts
    8

    Re: Download XML file

    Thank you tg for your prompt reply!

    The
    Code:
    Dim cube As XmlNode
    does not have a value ('Nothing')

  11. #11
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,754

    Re: Download XML file

    You will need to add two imports to your project.

    Code:
    Imports <xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
    Imports <xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01">
    Then after the xe.Save statement replace what you have with this,

    Code:
                For Each cube As XElement In xe.<Cube>.Elements
                    Dim s As String
                    For Each sel As XElement In cube.Elements
                        s = String.Format("Time:{0}   Currency={1}   rate={2}",
                                          cube.@time,
                                          sel.@currency,
                                          sel.@rate)
                        Debug.WriteLine(s)
                    Next
                Next
    and see if that helps. I find using XElement and LINQ easier.

    FWIW - the xml is some of the worst I have seen.

    sample

    Code:
    <gesmes:Envelope xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref" xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01">
      <gesmes:subject>Reference rates</gesmes:subject>
      <gesmes:Sender>
        <gesmes:name>European Central Bank</gesmes:name>
      </gesmes:Sender>
      <Cube>
        <Cube time="2021-03-17">
          <Cube currency="USD" rate="1.1907" />
          <Cube currency="JPY" rate="130.02" />
          <Cube currency="BGN" rate="1.9558" />
          <Cube currency="CZK" rate="26.159" />
          <Cube currency="DKK" rate="7.436" />
          <Cube currency="GBP" rate="0.85668" />
          <Cube currency="HUF" rate="367.74" />
          <Cube currency="PLN" rate="4.6136" />
          <Cube currency="RON" rate="4.8874" />
          <Cube currency="SEK" rate="10.1355" />
          <Cube currency="CHF" rate="1.1047" />
          <Cube currency="ISK" rate="151.4" />
          <Cube currency="NOK" rate="10.1213" />
          <Cube currency="HRK" rate="7.5765" />
          <Cube currency="RUB" rate="87.8245" />
          <Cube currency="TRY" rate="8.9498" />
          <Cube currency="AUD" rate="1.5427" />
          <Cube currency="BRL" rate="6.7299" />
          <Cube currency="CAD" rate="1.4852" />
          <Cube currency="CNY" rate="7.7435" />
          <Cube currency="HKD" rate="9.2476" />
          <Cube currency="IDR" rate="17216.3" />
          <Cube currency="ILS" rate="3.9155" />
          <Cube currency="INR" rate="86.429" />
          <Cube currency="KRW" rate="1346.65" />
          <Cube currency="MXN" rate="24.6882" />
          <Cube currency="MYR" rate="4.9075" />
          <Cube currency="NZD" rate="1.6618" />
          <Cube currency="PHP" rate="57.982" />
          <Cube currency="SGD" rate="1.6036" />
          <Cube currency="THB" rate="36.656" />
          <Cube currency="ZAR" rate="17.718" />
        </Cube>
        <Cube time="2021-03-16">
          <Cube currency="USD" rate="1.1926" />
          <Cube currency="JPY" rate="129.88" />
          <Cube currency="BGN" rate="1.9558" />
          <Cube currency="CZK" rate="26.197" />
          <Cube currency="DKK" rate="7.436" />
          <Cube currency="GBP" rate="0.85945" />
          <Cube currency="HUF" rate="367.3" />
          <Cube currency="PLN" rate="4.5933" />
          <Cube currency="RON" rate="4.8868" />
          <Cube currency="SEK" rate="10.1388" />
          <Cube currency="CHF" rate="1.1033" />
          <Cube currency="ISK" rate="151.6" />
          <Cube currency="NOK" rate="10.1028" />
          <Cube currency="HRK" rate="7.5765" />
          <Cube currency="RUB" rate="86.6948" />
          <Cube currency="TRY" rate="8.935" />
          <Cube currency="AUD" rate="1.539" />
          <Cube currency="BRL" rate="6.6722" />
          <Cube currency="CAD" rate="1.4867" />
          <Cube currency="CNY" rate="7.7519" />
          <Cube currency="HKD" rate="9.2626" />
          <Cube currency="IDR" rate="17206.59" />
          <Cube currency="ILS" rate="3.9297" />
          <Cube currency="INR" rate="86.479" />
          <Cube currency="KRW" rate="1347.36" />
          <Cube currency="MXN" rate="24.6021" />
          <Cube currency="MYR" rate="4.9064" />
          <Cube currency="NZD" rate="1.6578" />
          <Cube currency="PHP" rate="57.994" />
          <Cube currency="SGD" rate="1.6049" />
          <Cube currency="THB" rate="36.655" />
          <Cube currency="ZAR" rate="17.7067" />
        </Cube>
      </Cube>
    </gesmes:Envelope>
    Last edited by dbasnett; Mar 17th, 2021 at 11:39 AM.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  12. #12

    Thread Starter
    New Member
    Join Date
    Feb 2016
    Posts
    8

    Re: Download XML file

    Great! Thank you!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width