Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query the data from a Dataset

Posted on 2008-10-02
9
Medium Priority
?
279 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 64

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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 64

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 64

Accepted Solution

by:
Fernando Soto earned 2000 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 64

Expert Comment

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

783 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