Solved

Query the data from a Dataset

Posted on 2008-10-02
9
265 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 63

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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
 
LVL 63

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 63

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 63

Expert Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 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