Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query the data from a Dataset

Posted on 2008-10-02
9
Medium Priority
?
276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

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.

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

688 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