[RESOLVED] Best way to consume xml results

[lleemon]

Is there an easy way to consume xml to access any of the nodes directly without reading line by line?

Here is a sample set of the xml working with. It contains two records of data. I am trying to find a way to load it so if I wanted to get say PostalCode and say Site SID value quickly.

<?xml version="1.0" encoding="utf-8"?><MainSite><MainSites Found="201" Returned="20" Status="0"><Site SID="123456"><Relevance>0.985</Relevance><Title>JDoe</Title><DateModified Date="2010-01-29T01:05:00">1/29/2010</DateModified><DateCreated Date="2010-01-29T01:05:00">1/29/2010</DateCreated><PersonalData><Confidential>0</Confidential><Name><First>John</First><Middle></Middle><Last>Doe</Last></Name><Address><Country LID="316" /><State LID="477" /><City>City1</City><PostalCode>85306</PostalCode><Location>US-ST-City1</Location></Address><MilitaryExperience><ServiceFlag>0</ServiceFlag><MilitaryInvolvement>0</MilitaryInvolvement></MilitaryExperience></PersonalData><Target><JobTitle></JobTitle></Site><Site SID="a9hwv"><Relevance>0.985</Relevance><Title>Jill Doe</Title><DateModified Date="2010-01-29T01:05:00">1/29/2010</DateModified><DateCreated Date="2010-01-29T01:05:00">1/29/2010</DateCreated><PersonalData><Confidential>0</Confidential><Name><First>Jane</First><Middle></Middle><Last>Doe</Last></Name><Address><Country LID="316" /><State LID="477" /><City>City1</City><PostalCode>85306</PostalCode><Location>US-ST-City1</Location></Address><MilitaryExperience><ServiceFlag>0</ServiceFlag><MilitaryInvolvement>0</MilitaryInvolvement></MilitaryExperience></PersonalData><Target><JobTitle></JobTitle></Site></MainSites><MainSite>

[techgnome]

Using an XMLDocument object?

-tg

[lleemon]

I guess I am looking to see what people think is the best way with the format I have listed above.

[techgnome]

Okaaaaay.... let me be a little more specific...
I would create an XMLDocument object, I would load the data into it (there's a couple ways to do that), then I would use something like SelectNodes to get all of the nodes I'm interested in.

-tg

[lleemon]

In this For Each, how do I grab the <First>, <Middle>, and ,<Last> InnerText values since <PersonalData> is a ChildNode and I need a ChildNode of a ChildNode?

#Region " fnReadXML "
    Function fnReadXML(ByVal sXML As String) As String
        Dim m_xmld As XmlDocument
        Dim m_nodelist As XmlNodeList
        Dim m_node As XmlNode

        'Create the XML Document
        m_xmld = New XmlDocument()

        'Load the xml file
        m_xmld.LoadXml(sXML)

        'Get the list of name nodes
        m_nodelist = m_xmld.SelectNodes("/MainSite/MainSites/Site")

        'Loop through the nodes
        For Each m_node In m_nodelist

            'Get the Relevance Element Value
            Dim sRelevance As String = m_node.ChildNodes.Item(0).InnerText

            'Get the Title Element Value
            Dim sTitle As String = m_node.ChildNodes.Item(1).InnerText

            'Get the DateModified Element Value
            Dim sDateModified As String = m_node.ChildNodes.Item(2).InnerText

            'Get the DateCreated Element Value
            Dim sDateCreated As String = m_node.ChildNodes.Item(3).InnerText

            'Write Result to the Console
            Console.Write(" Relevance: " & sRelevance & " Title: " _
            & sTitle & " Date Modified: " & sDateModified & " DateCreated: " & sDateCreated)
            Console.Write(vbCrLf)
        Next

        Return ""
    End Function
#End Region

[gep13]

LINQ to XML:

http://weblogs.asp.net/scottgu/archi...r-with-it.aspx

Gary

[lleemon]

Gary - thanks, gave me a start but I am a little confused. In my 'Select New FeedDefinition the 'Name' and 'Url' are both saying not declared.

Utterly confused. Is there a good vb.net example somewhere?

#Region "fnLinqToXML"
    Function fnLinqToXML(ByVal sXML As String)
        'Dim tr As TextReader = New StringReader(sXML)
        'Dim feedXML As XDocument = XDocument.Load(tr)

        Dim feedXML As XDocument = XDocument.Parse(sXML)

        Dim query = From feed In feedXML.Descendants.Elements("Site") _
                    Select New FeedDefinition(Name = feed.Element("Relevance").Value, Url = feed.Element("ResumeTitle").Value)



        Return "?"
    End Function
#End Region

Public Class FeedDefinition
    Private _Name As String = ""
    Private _Url As String = ""
    Public Property Name() As String
        Get
            Return _Name
        End Get
        Set(ByVal value As String)
            _Name = value
        End Set
    End Property

    Public Property Url() As String
        Get
            Return _Url
        End Get
        Set(ByVal value As String)
            _Url = value
        End Set
    End Property

End Class

[Lightning]

You could also create a class, that can derserialize this XML. If you have an XSD this can be done with XSD.exe.

[stanav]

How about loading it into a DataSet using by calling the DataSet.ReadXml method?

[lleemon]

Yeah, I think the dataset is the way to go. Just taking me too long to figure out the LINQ and xmldocument. Here is a sample function of using the dataset.

#Region " fnViewResults "
    Function fnViewResults(ByVal sXML As String) As DataTable
        Dim myDataTable As DataTable = New DataTable()
        Dim myDataColumn As DataColumn

        myDataColumn = New DataColumn()
        myDataColumn.DataType = Type.GetType("System.String")
        myDataColumn.ColumnName = "CandidateSID"
        myDataTable.Columns.Add(myDataColumn)

        myDataColumn = New DataColumn()
        myDataColumn.DataType = Type.GetType("System.String")
        myDataColumn.ColumnName = "CandidateResumeID"
        myDataTable.Columns.Add(myDataColumn)

        myDataColumn = New DataColumn()
        myDataColumn.DataType = Type.GetType("System.String")
        myDataColumn.ColumnName = "FirstName"
        myDataColumn.DefaultValue = ""
        myDataTable.Columns.Add(myDataColumn)

        myDataColumn = New DataColumn()
        myDataColumn.DataType = Type.GetType("System.String")
        myDataColumn.ColumnName = "LastName"
        myDataColumn.DefaultValue = ""
        myDataTable.Columns.Add(myDataColumn)

        myDataColumn = New DataColumn()
        myDataColumn.DataType = Type.GetType("System.String")
        myDataColumn.ColumnName = "Email"
        myDataColumn.DefaultValue = ""
        myDataTable.Columns.Add(myDataColumn)

        myDataColumn = New DataColumn()
        myDataColumn.DataType = Type.GetType("System.String")
        myDataColumn.ColumnName = "Street"
        myDataColumn.DefaultValue = ""
        myDataTable.Columns.Add(myDataColumn)

        myDataColumn = New DataColumn()
        myDataColumn.DataType = Type.GetType("System.String")
        myDataColumn.ColumnName = "City"
        myDataColumn.DefaultValue = ""
        myDataTable.Columns.Add(myDataColumn)

        myDataColumn = New DataColumn()
        myDataColumn.DataType = Type.GetType("System.String")
        myDataColumn.ColumnName = "State"
        myDataColumn.DefaultValue = ""
        myDataTable.Columns.Add(myDataColumn)

        myDataColumn = New DataColumn()
        myDataColumn.DataType = Type.GetType("System.String")
        myDataColumn.ColumnName = "Country"
        myDataColumn.DefaultValue = ""
        myDataTable.Columns.Add(myDataColumn)

        myDataColumn = New DataColumn()
        myDataColumn.DataType = Type.GetType("System.String")
        myDataColumn.ColumnName = "PostalCode"
        myDataColumn.DefaultValue = ""
        myDataTable.Columns.Add(myDataColumn)

        myDataColumn = New DataColumn()
        myDataColumn.DataType = Type.GetType("System.String")
        myDataColumn.ColumnName = "Phone"
        myDataColumn.DefaultValue = ""
        myDataTable.Columns.Add(myDataColumn) 'could have two

        myDataColumn = New DataColumn()
        myDataColumn.DataType = Type.GetType("System.String")
        myDataColumn.ColumnName = "DesiredSalary"
        myDataColumn.DefaultValue = ""
        myDataTable.Columns.Add(myDataColumn)

        myDataColumn = New DataColumn()
        myDataColumn.DataType = Type.GetType("System.String")
        myDataColumn.ColumnName = "Experience"
        myDataColumn.DefaultValue = ""
        myDataTable.Columns.Add(myDataColumn)


        myDataColumn = New DataColumn()
        myDataColumn.DataType = Type.GetType("System.String")
        myDataColumn.ColumnName = "ResumeText"
        myDataColumn.DefaultValue = ""
        myDataTable.Columns.Add(myDataColumn)

        myDataColumn = New DataColumn()
        myDataColumn.DataType = Type.GetType("System.String")
        myDataColumn.ColumnName = "Industry"
        myDataColumn.DefaultValue = ""
        myDataTable.Columns.Add(myDataColumn)

        myDataColumn = New DataColumn()
        myDataColumn.DataType = Type.GetType("System.String")
        myDataColumn.ColumnName = "Location"
        myDataColumn.DefaultValue = ""
        myDataTable.Columns.Add(myDataColumn)

        Try

            'create a stream object from string object
            Dim xmlStream As IO.MemoryStream = ConvertToStream(sXML)

            'load dataset
            Dim dataSet As New Data.DataSet
            dataSet.ReadXml(xmlStream, XmlReadMode.Auto)
            'PrintDataSet(dataSet)
            Dim dvSite As New DataView(dataSet.Tables("Site"))

            Dim dvPersonalData As New DataView(dataSet.Tables("PersonalData"))
            Dim dvName As New DataView(dataSet.Tables("Name"))
            Dim dvAddress As New DataView(dataSet.Tables("Address"))
            Dim dvCountry As New DataView(dataSet.Tables("Country"))
            Dim dvState As New DataView(dataSet.Tables("State"))
            Dim dvDateModified As New DataView(dataSet.Tables("DateModified"))

            Dim iSiteCount As Integer = 0
            Dim iLoop As Integer = 0
            If dvSite.Count > 0 Then
                iSiteCount = dvSite.Count - 1
                For iLoop = 0 To iSiteCount
                    Dim row As DataRow
                    row = myDataTable.NewRow()
                    row("CandidateResumeID") = dvSite(iLoop)("SID")
                    row("FirstName") = dvName(iLoop)("First")
                    row("LastName") = dvName(iLoop)("Last")
                    row("City") = dvAddress(iLoop)("City")

                    If IsDBNull(dvState(iLoop)(0)) Then
                        row("State") = ""
                    Else
                        row("State") = fnGetMonsterLocationCode(dvState(iLoop)(0))
                    End If

                    If IsDBNull(dvCountry(iLoop)(0)) Then
                        row("Country") = ""
                    Else
                        row("Country") = fnGetMonsterLocationCode(dvCountry(iLoop)(0))
                    End If

                    row("PostalCode") = dvAddress(iLoop)("PostalCode")
                    row("Location") = dvAddress(iLoop)("Location")
                    myDataTable.Rows.Add(row)
                Next
            End If

      

            'Loop through results of myDataTable
            If myDataTable.Rows.Count > 0 Then
                For Each dr2 As DataRow In myDataTable.Rows
                    Dim sResults As String = ""
                    sResults = "CandidateResumeID: " & dr2("CandidateResumeID").ToString _
                        & " - First Name: " & dr2("FirstName").ToString _
                        & " - Last Name: " & dr2("LastName").ToString _
                        & " - City: " & dr2("City").ToString _
                        & " - PostalCode: " & dr2("PostalCode").ToString _
                        & " - Country: " & dr2("Country").ToString _
                        & " - State: " & dr2("State").ToString _
                        & " - Location: " & dr2("Location").ToString

                    MessageBox.Show(sResults)
                Next
            End If


        Catch ex As Exception
            LogIt("[-ERROR fnViewResults: " & ex.ToString & "]")
            LogIt("[ XML sent: " & sXML & "]")
        End Try

        Return myDataTable
    End Function
#End Region

[gep13]

Hey,

First up, the XML that you posted in your first post is malformed.

Here is a corrected version:

<?xml version="1.0" encoding="utf-8"?>
<MainSite>
  <MainSites Found="201" Returned="20" Status="0">
    <Site SID="123456">
      <Relevance>0.985</Relevance>
      <Title>JDoe</Title>
      <DateModified Date="2010-01-29T01:05:00">1/29/2010</DateModified>
      <DateCreated Date="2010-01-29T01:05:00">1/29/2010</DateCreated>
      <PersonalData>
        <Confidential>0</Confidential>
        <Name>
          <First>John</First>
          <Middle></Middle>
          <Last>Doe</Last>
        </Name>
        <Address>
          <Country LID="316" />
          <State LID="477" />
          <City>City1</City>
          <PostalCode>85306</PostalCode>
          <Location>US-ST-City1</Location>
        </Address>
        <MilitaryExperience>
          <ServiceFlag>0</ServiceFlag>
          <MilitaryInvolvement>0</MilitaryInvolvement>
        </MilitaryExperience>
      </PersonalData>
      <Target>
        <JobTitle></JobTitle>
      </Target>
    </Site>
    <Site SID="a9hwv">
      <Relevance>0.985</Relevance>
      <Title>Jill Doe</Title>
      <DateModified Date="2010-01-29T01:05:00">1/29/2010</DateModified>
      <DateCreated Date="2010-01-29T01:05:00">1/29/2010</DateCreated>
      <PersonalData>
        <Confidential>0</Confidential>
        <Name>
          <First>Jane</First>
          <Middle></Middle>
          <Last>Doe</Last>
        </Name>
        <Address>
          <Country LID="316" />
          <State LID="477" />
          <City>City1</City>
          <PostalCode>85306</PostalCode>
          <Location>US-ST-City1</Location>
        </Address>
        <MilitaryExperience>
          <ServiceFlag>0</ServiceFlag>
          <MilitaryInvolvement>0</MilitaryInvolvement>
        </MilitaryExperience>
      </PersonalData>
      <Target>
        <JobTitle></JobTitle>
      </Target>
    </Site>
  </MainSites>
</MainSite>

So, to go down the route of using XmlDocument and XPath, you could do something like this:

        Dim xmlDoc As New XmlDocument()
        xmlDoc.Load("C:\temp\WebApplication1\WebApplication1\XmlFile1.xml")

        Dim sites As XmlNodeList = xmlDoc.SelectNodes("/MainSite/MainSites/Site")

        For Each site As XmlNode In sites
            Dim siteID As String = site.Attributes("SID").InnerText
            Dim postalCode As String = site.SelectSingleNode("PersonalData/Address/PostalCode").InnerText
        Next

And with LINQ to XML, you would do something like this:

        Dim siteXml As XDocument = XDocument.Load("C:\temp\WebApplication1\WebApplication1\XmlFile1.xml")

        Dim sites = From site In siteXml.Descendants("Site")
            Select SiteId = site.Attribute("SID").Value,
            PostalCode = site.Descendants("PostalCode").Value

        For Each mySite In sites
            Console.WriteLine(mySite.SiteId)
            Console.WriteLine(mySite.PostalCode)
        Next

Hope that helps!!

Gary

[lleemon]

Gary - Yes, these are great examples. I couldn't figure out how to get the subnodes but you have a good example here. Much appreciated. When I have more time I will have to check the LINQ stuff out.

[gep13]

Yip, it is definitely worth spending some time having a look at.

Having said that, I need to spend more time on it myself

Gary