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

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.

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

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

Quote:

---

Originally Posted by .paul.

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)

---

Quote:

---

Originally Posted by StevenVanAken

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?

Quote:

---

Originally Posted by StevenVanAken

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

---

Thank you dbasnett!

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:

Quote:

---

<?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?

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

Thank you tg for your prompt reply!

The

Code:

---

Dim cube As XmlNode

---

does not have a value ('Nothing')

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>

---

Great! Thank you!