-
Mar 16th, 2021, 04:04 PM
#1
Thread Starter
New Member
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
-
Mar 16th, 2021, 04:16 PM
#2
Hyperactive Member
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.
-
Mar 16th, 2021, 07:07 PM
#3
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Mar 17th, 2021, 03:02 AM
#4
Thread Starter
New Member
Re: Download XML file
Originally Posted by .paul.
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)
-
Mar 17th, 2021, 03:18 AM
#5
Re: Download XML file
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?
-
Mar 17th, 2021, 09:15 AM
#6
Re: Download XML file
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
-
Mar 17th, 2021, 10:33 AM
#7
Thread Starter
New Member
-
Mar 17th, 2021, 10:40 AM
#8
Thread Starter
New Member
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?
-
Mar 17th, 2021, 11:12 AM
#9
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
-
Mar 17th, 2021, 11:32 AM
#10
Thread Starter
New Member
Re: Download XML file
Thank you tg for your prompt reply!
The
Code:
Dim cube As XmlNode
does not have a value ('Nothing')
-
Mar 17th, 2021, 11:32 AM
#11
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.
-
Mar 17th, 2021, 11:50 AM
#12
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|