search dataset from xml file

Question

I need to filter the results I obtain when I load my xml file. For example I need to search the xml data for items with keyword "Chemistry" for example. The below xml example is a summary of my xml file. The data is loaded in a gridview. Could you help? Thanks!

Xml File (summary):

<CONTRACTS> <CONTRACT> <CONTRACTID>779</CONTRACTID> <NAME>ContractName</NAME> <KEYWORDS>Chemistry, Engineering, Chemical</KEYWORDS> <CONTRACTSTARTDATE>1/8/2005</CONTRACTSTARTDATE> <CONTRACTENDDATE>31/7/2008</CONTRACTENDDATE> <COMMODITIES><COMMODITY><COMMODITYCODE>CHEM</COMMODITYCODE> <COMMODITYNAME>Chemicals</COMMODITYNAME></COMMODITY></COMMODITIES> </CONTRACT></CONTRACTS>

My code behind code is:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim ds As DataSet = New DataSet()

    ds.ReadXml(AppDomain.CurrentDomain.BaseDirectory + "/testxml.xml")

    Dim dtContract As DataTable = ds.Tables(0)
    Dim dtJoinCommodities As DataTable = ds.Tables(1)
    Dim dtCommodity As DataTable = ds.Tables(2)
    dtContract.Columns.Add("COMMODITYCODE")
    dtContract.Columns.Add("COMMODITYNAME")

    Dim count As Integer = 0
    Dim commodityCode As String = Nothing
    Dim commodityName As String = Nothing

    Dim dRowJoinCommodity As DataRow
    Dim trimChar As Char() = {","c, " "c}

    Dim textboxstring As String = "KEYWORDS like 'pencil'"

        For Each dRow As DataRow In dtContract.Select(textboxstring)

        commodityCode = ""
        commodityName = ""

        count = dtContract.Rows.IndexOf(dRow)
        dRowJoinCommodity = dtJoinCommodities.Rows(count)

        For Each dRowCommodities As DataRow In dtCommodity.Rows
            If dRowCommodities("COMMODITIES_Id").ToString() = dRowJoinCommodity("COMMODITIES_ID").ToString() Then

                commodityCode = commodityCode + dRowCommodities("COMMODITYCODE").ToString() + ", "

                commodityName = commodityName + dRowCommodities("COMMODITYNAME").ToString() + ", "

            End If

        Next

       commodityCode = commodityCode.TrimEnd(trimChar)
        commodityName = commodityName.TrimEnd(trimChar)
        dRow("COMMODITYCODE") = commodityCode
        dRow("COMMODITYNAME") = commodityName

    Next


    GridView1.DataSource = dtContract
    GridView1.DataBind()
End Sub

Answer 1

I must suggest that you check out XQuery and how to use it in VB since it's designed for precisely this sort of scenarios. It's a bit intimidating at first like most languages, but it's quite powerful and will make what you're doing trivial.

XQuery is a language for describing searches in XML. XPath is a search string describing the "path" of stuff in XML. You can easily look up things by element name, element value, attribute name/value etc.

Unfortunately I don't know Visual Basic and haven't used it since I was about 16 so I can't help you more than that, sorry. But you will never regret learning how to use XQuery. It really brings out the true power of XML.

For what it's worth (admittedly not a lot until you figure out how to use XQuery in VB), here are some examples of what you can do to configure XPath searches:

http://wxww.w3schools.com/xpath/xpath_functions.asp

Again, don't be intimidated by it, just find some basic XQuery tutorials for VB and you'll figure it out in no time. :)