Solved

Query the data from a Dataset

Posted on 2008-10-02
9
263 Views
Last Modified: 2013-11-11
I have a dataset that i have populated from an XML file. the File is 10mb and has about 70 elements per record. I want to know how i can query (not necessarily a SQL statement) for a specific field value in one of the element values basically transIdNo.

I assume that the data will exist in one table within the dataset (however when when i run debug is says that ds table count = 8 which i dont understand) i have not defined a table setup. I tried to create a table adapter but with no luck.

I just want to be able to extract data relavent for my criteria on the value of element
<tc810><Tc810Grp><Tc810Grp1><tc810KeyGrp1><tc810Rec><transIdNo>#value


the xml data is as follows
<tc810 xmlns="http://deutsche-boerse.com/dbag/app/open/xetra">
<rptHdr>
</rptHdr>
<tc810Grp>
<tc810KeyGrp>
<membExcIdCod>EDFLO</membExcIdCod>
<membClgIdCod>EDFLO</membClgIdCod>
<membCcpClgIdCod>EDFLO</membCcpClgIdCod>
<stlIdAct>8627      </stlIdAct>
<stlIdLoc>CBF</stlIdLoc>
<instTitl>
<instMnem>O2C  </instMnem>
<instNam>C.A.T. OIL AG                 </instNam>
<wknNo>000A0JKWU</wknNo>
<isinCod>AT0000A00Y78</isinCod>
</instTitl>
<setlCurrTypCod>EUR</setlCurrTypCod>
<denCurrTypCod>   </denCurrTypCod>
<cntcUnt>1</cntcUnt>
</tc810KeyGrp>
<tc810Grp1>
<tc810KeyGrp1>
<partIdCod>ORD027</partIdCod>
</tc810KeyGrp1>
<tc810Rec>
<tranTim>09:04:56.00</tranTim>
<tranIdNo>23231</tranIdNo>
<tranIdSfxNo>0</tranIdSfxNo>
<setlmCod>S</setlmCod>
<isinCod>AT0000A00Y78</isinCod>
<ordrNo>6497250457</ordrNo>
<acctTypCodGrp>P1</acctTypCodGrp>
<ordrBuyCod>B</ordrBuyCod>
<tradMtchQty>+3.000</tradMtchQty>
<tradMtchPrc>      3.57000</tradMtchPrc>
<stlAmnt>+10.710</stlAmnt>
<stlDate>2008-10-03</stlDate>
<feeAmt>+0.60</feeAmt>
<feesCurrTypCod>EUR</feesCurrTypCod>
<membCtpyIdCod>CCPFR</membCtpyIdCod>
<ctpyStlIdAct>8501      </ctpyStlIdAct>
<setlTypCod>DVP</setlTypCod>
<dwzNo>8501</dwzNo>
<usrOrdrNum>00003261963VOLO0</usrOrdrNum>
<otcTrdFlgGrp>
<otcTrdFlg_1>  </otcTrdFlg_1>
<otcTrdFlg_2>  </otcTrdFlg_2>
<otcTrdFlg_3>  </otcTrdFlg_3>
</otcTrdFlgGrp>
<kindOfDepo>GS </kindOfDepo>
</tc810Rec>
<tc810Rec>
<tranTim>10:43:50.10</tranTim>
<tranIdNo>194358</tranIdNo>
<tranIdSfxNo>0</tranIdSfxNo>
<setlmCod>S</setlmCod>
<isinCod>AT0000A00Y78</isinCod>
<ordrNo>6499537623</ordrNo>
<acctTypCodGrp>P1</acctTypCodGrp>
<ordrBuyCod>B</ordrBuyCod>
<tradMtchQty>+43.000</tradMtchQty>
<tradMtchPrc>      3.30000</tradMtchPrc>
<stlAmnt>+141.900</stlAmnt>
<stlDate>2008-10-03</stlDate>
<feeAmt>+0.60</feeAmt>
<feesCurrTypCod>EUR</feesCurrTypCod>
<membCtpyIdCod>CCPFR</membCtpyIdCod>
<ctpyStlIdAct>8501      </ctpyStlIdAct>
<setlTypCod>DVP</setlTypCod>
<dwzNo>8501</dwzNo>
<usrOrdrNum>00003265275VOLO0</usrOrdrNum>
<otcTrdFlgGrp>
<otcTrdFlg_1>  </otcTrdFlg_1>
<otcTrdFlg_2>  </otcTrdFlg_2>
<otcTrdFlg_3>  </otcTrdFlg_3>
</otcTrdFlgGrp>
<kindOfDepo>GS </kindOfDepo>
</tc810Rec>
<sumPartTotBuyOrdr>46.000</sumPartTotBuyOrdr>
<sumPartTotSellOrdr>0.000</sumPartTotSellOrdr>
</tc810Grp1>
<sumMembTotBuyOrdr>46.000</sumMembTotBuyOrdr>
<sumMembTotSellOrdr>0.000</sumMembTotSellOrdr>
</tc810Grp>
Sub LinqXml()
 

        Dim myXml As FileStream
 

        Try
 

            myXml = New FileStream("c:\rpttc810.xml", FileMode.Open)

            MyDS.ReadXmlSchema("c:\InferredSchema.xds")

            MyDS.ReadXml(myXml, XmlReadMode.ReadSchema)

            Dim myTable As DataTable
 

            Dim stmt As String

            stmt = "select count(*) from tc810"
 

        Catch ex As Exception

            Debug.WriteLine(ex)

        Finally

            myXml.Close()
 

        End Try

End Sub

Open in new window

0
Comment
Question by:allanhutton
  • 4
  • 4
9 Comments
 
LVL 11

Expert Comment

by:ladarling
ID: 22624888
Ok... there are a number of questions that have to be answered before you are going to get much help :-)
-Is the XML file that you are loading the contents of 1 table, or multiple tables?

-What are you trying to accomplish with this code?:
 stmt = "select count(*) from tc810"

-Is the schema that you are loading the dataset with identical to the schema of the XML that you are populating the dataset with? That may sound obvious, but if your dataset is creating multiple tables something is amiss, either malformed XML or multiple schemas are present.
0
 

Author Comment

by:allanhutton
ID: 22625003
1. it is one table i believe, i've attached the file but renames it to txt else it gets blocked.;

2. I've added a cut down sample of the file which to me looks like would only create one table called tc810 however during debug the dataset name =tc810.

3. i was crudely trying to see how many records where in the dataset. it can and probably should be ignored.

4. i created the scheme rofm this code as the file did not have a xsd present for me to use.

 Sub XMLSchema()

        Dim nsArray As String() = {""}
 

        Dim MyDS As DataSet = New DataSet()

        MyDS.ReadXml("c:\rpttc810.xml")
 

        MyDS.WriteXmlSchema("c:\InferredSchema.xds")
 

    End Sub

Open in new window

RPTTC810.txt
InferredSchema.txt
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 22627523
Hi allanhutton;

The following are the table names of the 8 tables defined by the XML file. When reading XML into a data set each new child level defines a relationship between the parent node and the child and becomes a new table in the dataset.

rptHdr
tc810Gpr
tc810KeyGrp
instTitl
tc810Grp1
tc810KeyGrp1
tc810Rec
otcTrdFlgGrp

You can get the value of the field you want without creating a dataset. The snippet of code shows how. please note the Imports statements.


Fernando

Imports System.IO

Imports System.Xml.Linq

Imports <xmlns:ns="http://deutsche-boerse.com/dbag/app/open/xetra">
 

Public Class Form1
 

    Private Sub Button1_Click(ByVal sender As System.Object, _

        ByVal e As System.EventArgs) Handles Button1.Click
 

        LinqToXml()
 

    End Sub
 
 

    Private Sub LinqToXml()
 

        Dim doc As XDocument

        doc = XDocument.Load("rpttc810.xml")
 

        Dim transIdNos = From ids In doc.<ns:tc810>.<ns:tc810Grp>.<ns:tc810Grp1>.<ns:tc810Rec>.<ns:tranIdNo> _

                         Select ids.Value
 

        For Each transIdNo In transIdNos

            Console.WriteLine(transIdNo)

        Next
 

    End Sub
 

End Class

Open in new window

0
 

Author Comment

by:allanhutton
ID: 22628452
you are a very clever man :)

one of the reasons i wanted it in a dataset was to do a cross query will a sql table so i could see where they matched. can i do that with linq in different datasets?

regards
allan
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 62

Expert Comment

by:Fernando Soto
ID: 22628649
Hi Allan;

Most likely but I am really not sure what you want to do. In the above I used Linq To XML to query a database you would use Linq To SQL but this only supports Microsoft SQL Server at this time. If you can give a little more details of what you need to do I will try and help.

Fernando

0
 

Author Comment

by:allanhutton
ID: 22631828
the below code is what i uses to populate by SQL dataset. i then populate a datagrid with the data. but i would like to cross query the table in the sql dataset against the data in the linq xml dataset.

The sql dataset has a table called tbl_audit that has the same field identify as linq datatable called transIdNo. i want all the records where they dont match.

hence a sql statement like
"select A.* from tbl_audit as A where a.transidno not in (select transidno from xmltable)"

regards
Allan




Public Sub Getdata(ByVal SelectCommand As String)
 

        Try
 

            Me.DataAdapter1 = New SqlDataAdapter(SelectCommand, SQLConString)

            Dim CommandBuilder As New SqlCommandBuilder(Me.DataAdapter1)

            Dim table As New DataTable()

            table.Locale = System.Globalization.CultureInfo.InvariantCulture
 

            Me.DataAdapter1.Fill(table)

            Me.BindingSource1.DataSource = table
 

            Me.DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
 
 

        Catch ex As Exception

            MsgBox(ex.ToString)
 

        End Try

    End Sub

Open in new window

0
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 22639313
Hi Allan;

Try code like this.

Fernando

Imports System.Xml.Linq

Imports System.Data.SqlClient

Imports <xmlns:ns="http://deutsche-boerse.com/dbag/app/open/xetra">
 
 

Dim transIdNos As List(Of String)
 

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
 

		' Used to hold the XML document

    Dim doc As XDocument

    ' Load the XML document

    doc = XDocument.Load("rpttc810.xml")

		' Query the XML file for the field tranIdNo

    transIdNos = (From ids In doc.<ns:tc810>.<ns:tc810Grp>.<ns:tc810Grp1>.<ns:tc810Rec>.<ns:tranIdNo> _

                  Select ids.Value).ToList()
 

		' Get a reference to the data table to search through. I used

		' the DataGridView but if you have a variable use that.

    Dim dt As DataTable = CType(DataGridView1.DataSource, DataTable)
 

		' Search all rows to find all rows that do not match the value

    Dim allNotFound As IEnumerable(Of DataRow) = _

        From dr In dt _

        Where IsNotInList(dr.Field(Of String)("transIdNo")) _

        Select dr
 

		' Do something with all the DataRow's

    For Each row In allNotFound

        Console.WriteLine(row.Field(Of String)("transIdNo"))

    Next
 
 

End Sub
 

' A Function that checks all the values in the list to the current row

Private Function IsNotInList(ByVal item) As Boolean

    Dim ret As Boolean = True

    For Each val As String In transIdNos

        If item = val Then

            ret = False

        End If

    Next

    Return ret

End Function

Open in new window

0
 

Author Closing Comment

by:allanhutton
ID: 31502397
perfect thanks.

sorry i took me so long to get back to you. went on a school trip.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 22745309
Not a problem Allan, glad I was able to help.  ;=)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now