[RESOLVED] Load xml string into dataset/datatable

[lleemon]

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.

Dim dtResults As New DataTable("newdt")
dtResults.ReadXmlSchema("SearchResults.xsd")
dtResults.ReadXml(sRetVal)

SearchResults.xsd

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

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

[stanav]

Try something like this:

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)

[lleemon]

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.

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

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

[mendhak]

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")

[lleemon]

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:

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

[mendhak]

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

 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

[lleemon]

Still doesn't work. Has no nodes to loop through.

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

[lleemon]

Well, I am able to retrieve data. Not how I would have liked but works.

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