[RESOLVED] Load xml string into dataset/datatable
I have returned xml data in a variable and need to loop through the returned xml and pull certain data from select nodes.
I was thinking the best way would be to load into a dataset (or datatable) and loop through that.
I also have the xmlschema. What's the best way to code this?
I thought I could do something like so but it gives me the following error:
DataTable 'newdt' does not match to any DataTable in source.
Code:
Dim dtResults As New DataTable("newdt")
dtResults.ReadXmlSchema("SearchResults.xsd")
dtResults.ReadXml(sRetVal)
SearchResults.xsd
Code:
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Resumes">
<xs:complexType>
<xs:sequence>
<xs:element name="Resume" maxOccurs="1000">
<xs:complexType>
<xs:sequence>
<xs:element name="id" type="xs:string"/>
<xs:element name="City" type="xs:string" minOccurs="0"/>
<xs:element name="State" type="xs:string" minOccurs="0"/>
<xs:element name="Country" type="xs:string" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
sample text need to loop through
Code:
<?xml version="1.0" encoding="utf-8"?>
<Resumes xmlns="http://hj.yahooapis.com/v1/schema.rng" xmlns:yahoo="http://www.yahooapis.com/v1/base.rng" xml:lang="en-US" yahoo:start="1" yahoo:total="4" yahoo:count="4">
<Resume yahoo:uri="http://hj.yahooapis.com/v1/Resume/MjAxxx" xml:lang="en-US" >
<id>MjAxxx</id>
<City>Prior Lake</City>
<State>MN</State>
<Country>USA</Country>
</Resume>
<Resume yahoo:uri="http://hj.yahooapis.com/v1/Resume/MjExxx" xml:lang="en-US" >
<id>MjExxx</id>
<City>Eagan</City>
<State>MN</State>
<Country>USA</Country>
</Resume>
<Resume yahoo:uri="http://hj.yahooapis.com/v1/Resume/MzExxx" xml:lang="en-US" >
<id>MzExxx</id>
<City>Burnsville</City>
<State>MN</State>
<Country>USA</Country>
</Resume>
<Resume yahoo:uri="http://hj.yahooapis.com/v1/Resume/ODExxx" xml:lang="en-US" >
<id>ODExxx</id>
<City>Eagan</City>
<State>MN</State>
<Country>USA</Country>
</Resume>
</Resumes>
<!-- uncompressed/chunked Fri Mar 27 09:57:27 PDT 2009 -->
Re: Load xml string into dataset/datatable
Try something like this:
Code:
Dim xmlString As String = "your xml string here"
Dim strRdr As New IO.StringReader(xmlString)
Dim xmlRdr As Xml.XmlReader = Xml.XmlReader.Create(strRdr)
Dim ds As New DataSet()
ds.ReadXml(xmlRdr)
Re: Load xml string into dataset/datatable
Yeah, it doesn't error but I get no results. Below is the code I have, the iTableCount value is 4 but when I loop through the outer For Loop it doesn't enter the inner For Loop.
Code:
Dim strRdr As New IO.StringReader(sXML)
Dim xmlRdr As Xml.XmlReader = Xml.XmlReader.Create(strRdr)
Dim ds As New DataSet()
ds.ReadXmlSchema("SearchResults.xsd")
ds.ReadXml(xmlRdr)
Dim sTemp As String
Dim iTable As Integer
Dim iTableCount As Integer = 0
iTableCount = ds.Tables.Count
For iTable = 0 To iTableCount - 1
For Each dataRow As DataRow In ds.Tables(iTable).Rows
sTemp = dataRow(0).ToString()
Next
Next
My other thought was to use the SelectNodes but I can't seem to get that working as well
Code:
Dim objTest As StringBuilder = New StringBuilder()
Dim xDoc3 As XmlDocument = New XmlDocument
xDoc3.LoadXml(sXML)
Dim objList As XmlNodeList = xDoc3.SelectNodes("/Resume")
For Each objNode As XmlNode In objList
objTest.Append(objNode.InnerText & "<br />")
Next
Re: Load xml string into dataset/datatable
The best way, if you want to read data out, is to use an XPathNavigator or an XmlDocument. I see you're using an XmlDocument so...
The path you want is
xDoc3.SelectNodes("/Resumes/Resume")
Remember to always start at the root node.
You can loop through it, then, as you're doing with the XmlNode and look for
objNode.SelectSingleNode("/id")
Re: Load xml string into dataset/datatable
mendhak,
I have tried that but it seems like it can't find anything then. The only time I can get a value in the xmlnodelist object is doing the following:
Code:
Dim objTest As StringBuilder = New StringBuilder()
Dim xDoc3 As XmlDocument = New XmlDocument
xDoc3.LoadXml(sRetVal)
'Dim objList As XmlNodeList = xDoc3.SelectNodes("/Resumes/Resume")
Dim objList As XmlNodeList = xDoc3.SelectNodes(".")
For Each objNode As XmlNode In objList
objTest.Append(objNode.InnerText & "<br />")
Next
What it returns is all innertext from all 4 sections and subnodes. It's like the attributes are messing it up doing it with .SelectNodes
Re: Load xml string into dataset/datatable
Oh, I didn't notice the namespace there!
You'll also need to use an XmlNamespaceManager when selecting. An example is on this page: http://developer.yahoo.com/dotnet/howto-xml_vb.html
Code:
1. Dim doc As XmlDocument
2. Dim ns As XmlNamespaceManager
3. Dim nodes As XmlNodeList
4.
5. ' Create a new XmlDocument
6. doc = New XmlDocument()
7.
8. ' Load data
9. doc.Load("http://xml.weather.yahoo.com/forecastrss?p=94704")
10.
11. ' Set up namespace manager for XPath
12. ns = New XmlNamespaceManager(doc.NameTable)
13. ns.AddNamespace("yweather", "http://xml.weather.yahoo.com/ns/rss/1.0")
14.
15. ' Get forecast with XPath
16. nodes = doc.SelectNodes("/rss/channel/item/yweather:forecast", ns)
17.
18. ' You can also get elements based on their tag name and namespace,
19. ' though this isn't recommended
20. 'nodes = doc.GetElementsByTagName("forecast", _
21. ' "http://xml.weather.yahoo.com/ns/rss/1.0")
22.
23. For Each node As XmlNode In nodes
24. Console.WriteLine("{0}: {1}, {2}F - {3}F", _
25. node.Attributes("day").InnerText, _
26. node.Attributes("text").InnerText, _
27. node.Attributes("low").InnerText, _
28. node.Attributes("high").InnerText)
29. Next
Re: Load xml string into dataset/datatable
Still doesn't work. Has no nodes to loop through.
Code:
Dim doc As XmlDocument
Dim ns As XmlNamespaceManager
Dim nodes As XmlNodeList
' Create a new XmlDocument
doc = New XmlDocument()
' Load data
doc.LoadXml(sRetVal)
' Set up namespace manager for XPath
ns = New XmlNamespaceManager(doc.NameTable)
ns.AddNamespace("", "http://hj.yahooapis.com/v1/schema.rng")
ns.AddNamespace("yahoo", "http://www.yahooapis.com/v1/base.rng")
' Get forecast with XPath
nodes = doc.SelectNodes("/Resumes/Resume", ns)
For Each node As XmlNode In nodes
Console.WriteLine("{0}", node.Item("City").ToString)
Next
Re: Load xml string into dataset/datatable
Well, I am able to retrieve data. Not how I would have liked but works.
Code:
Dim doc As XmlDocument
Dim ns As XmlNamespaceManager
Dim nodes As XmlNodeList
' Create a new XmlDocument
doc = New XmlDocument()
' Load data
doc.LoadXml(sRetVal)
' Set up namespace manager for XPath
ns = New XmlNamespaceManager(doc.NameTable)
ns.AddNamespace("res", "http://hj.yahooapis.com/v1/schema.rng")
ns.AddNamespace("yahoo", "http://www.yahooapis.com/v1/base.rng")
' Get forecast with XPath
nodes = doc.SelectNodes("//res:Resume", ns)
Dim hshResumeIDs As New Hashtable()
For Each node As XmlNode In nodes
'Dim sTemp As String = node.ChildNodes(0).InnerText 'ID
'sTemp = node.ChildNodes(1).InnerText 'CandidateID
'sTemp = node.ChildNodes(2).InnerText 'CandidateResumeID
hshResumeIDs.Add(node.ChildNodes(0).InnerText, node.ChildNodes(2).InnerText)
Next
