[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 405
  • Last Modified:

Visual Basic and Linq to XML

Morning,

I was wondering if someone could help.  I'm trying to create an xml file.  I've populated a data table mytable, in dataset mydataset.  It has 3 columns a, b, and c.

An Example of this data would be...

A            B                 C
1             James         10
1             Simon          8
1             Steve           9
2             James          12
2             Simon           3
3             James          6
3             Steve          18


I've made a function to create my xml,  (see code below)

The output of this is fine for my first attempt at an XML file but how would I group column a "department"?  I understand Linq is much like SQL and should be able to add a Group by statement but where/how.

From Items In myDataSet.myTable Group by items.a   ?

This expects an into?  Where?  Can someone give me an example or point me in the right direction, I can't seem to find any on the web.

Many thanks



Public Function TextXml()
        Dim doc = New XElement("myHeader", _
                                New XElement("mysubheader", _
                                New XElement("date", Textbox1.Text), _
                                         From Items In myDataSet.myTable _
                                         Select New XElement("mydetails", _
                                            New XElement("Department", items.a), _
                                            New XElement("Name", items.b), _
                                            New XElement("Amount", items.c)))
 
        Return doc.ToString

Open in new window

0
nick210577
Asked:
nick210577
  • 5
  • 3
  • 2
1 Solution
 
Ken FayalCommented:
Try this out.  Very simple.  You build the dataset the way you want it and the code does the rest.  Just a suggestion.
XmlReader xmlRdr = XmlReader.Create('your xml file name here');
myDataSet.ReadXml(xmlRdr);
xmlRdr.Close();

Open in new window

0
 
nick210577Author Commented:
Thanks KaptainKenbo but I don't have to opertunity to manipulate the Data in.  The example I gave is simplified, but the stored procedure actually gives me a dozen columns and I'll have to group by a few columns in the end.  If I need to make another dataset with the tables how they should be it'll amount to lot more coding.  Anyone?
0
 
Ken FayalCommented:
I'm wondering why you can't modify the stored procedure to do the group by.  Is there another limitation there?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
nick210577Author Commented:
The Stored procedure is from another developer.  It contains everything i need, i just need to convert it to xml to pass to another developer.  If you like my app is just an interface between to two.  I have to give the data in a certain format, and I have to recieve it in such a format.
0
 
Ken FayalCommented:
I think the best bet is to do the grouping in the dataset first before trying to write out the XML.  You are going to have to write a bit of code no matter how you look at it..  Here's a MS article on building a group by class for datasets.

http://support.microsoft.com/kb/326145

0
 
nick210577Author Commented:
I've had a play with the function and i'm so close....


This splits out the name and amount into departments... however... i get all the namesin each department... not just the ones that should be in that department.

Do i need a distinct staement or a group by????

Public Function TextXml()
   Dim doc = New XElement("myHeader", _
                New XElement("mysubheader", _
                  New XElement("date", Textbox1.Text), _
                  From Items In myDataSet.myTable _
                    Select New XElement("mydetails", _
                            New XElement("Department", items.a), _
                              From Items In myDataSet.myTable _
                                Select New XElement("UserDetails", _
                                 New XElement("Name", items.b), _
                                 New XElement("Amount", items.c)))
 
        Return doc.ToString

Open in new window

0
 
nick210577Author Commented:
mistake in the second code snippet... the second from should be details and thus the elements details not items.
0
 
Fernando SotoCommented:
Hi nick210577;

Can you post the way you would like the XML document to be structured. For example the below code snippet will produce then following XML structure:

Return from the function TextXml()

<myHeader>
  <mySubHeader>
    <Date>04/22/2009</Date>
  </mySubHeader>
  <mydetails>
    <Department>1
          <Name>James
                <Amount>10</Amount>
          </Name>
          <Name>Simon
                <Amount>8</Amount>
          </Name>
          <Name>Steve
                <Amount>9</Amount>
          </Name>
    </Department>
  </mydetails>
  <mydetails>
    <Department>2
          <Name>James
                <Amount>12</Amount>
          </Name>
          <Name>Simon
                <Amount>3</Amount>
          </Name>
    </Department>
  </mydetails>
  <mydetails>
    <Department>3
          <Name>James
                <Amount>6</Amount>
          </Name>
    <Name>Steve
          <Amount>18</Amount>
    </name>
   </Department>
  </mydetails>
</myHeader>

When using the doc.Save(...) method in the function TextXml()

<?xml version="1.0" encoding="utf-8"?>
<myHeader>
  <mySubHeader>
    <Date>04/22/2009</Date>
  </mySubHeader>
  <mydetails>
    <Department>1
          <Name>James
                <Amount>10</Amount>
          </Name>
          <Name>Simon
                <Amount>8</Amount>
          </Name>
          <Name>Steve
                <Amount>9</Amount>
          </Name>
    </Department>
  </mydetails>
  <mydetails>
    <Department>2
          <Name>James
                <Amount>12</Amount>
          </Name>
          <Name>Simon
                <Amount>3</Amount>
          </Name>
    </Department>
  </mydetails>
  <mydetails>
    <Department>3
          <Name>James
                <Amount>6</Amount>
          </Name>
    <Name>Steve
          <Amount>18</Amount>
    </name>
   </Department>
  </mydetails>
</myHeader>

Fernando
Public Class Form1
 
    Dim myDataSet As DataSet
    Dim myTable As DataTable
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        myDataSet = New DataSet()
        myTable = New DataTable()
        Dim c As New DataColumn("A", GetType(Integer))
        myTable.Columns.Add(c)
        c = New DataColumn("B", GetType(String))
        myTable.Columns.Add(c)
        c = New DataColumn("C", GetType(Integer))
        myTable.Columns.Add(c)
        myDataSet.Tables.Add(myTable)
        Dim row As DataRow = myTable.NewRow()
        row(0) = 1
        row(1) = "James"
        row(2) = 10
        myTable.Rows.Add(row)
        row = myTable.NewRow()
        row(0) = 1
        row(1) = "Simon"
        row(2) = 8
        myTable.Rows.Add(row)
        row = myTable.NewRow()
        row(0) = 1
        row(1) = "Steve"
        row(2) = 9
        myTable.Rows.Add(row)
        row = myTable.NewRow()
        row(0) = 2
        row(1) = "James"
        row(2) = 12
        myTable.Rows.Add(row)
        row = myTable.NewRow()
        row(0) = 2
        row(1) = "Simon"
        row(2) = 3
        myTable.Rows.Add(row)
        row = myTable.NewRow()
        row(0) = 3
        row(1) = "James"
        row(2) = 6
        myTable.Rows.Add(row)
        row = myTable.NewRow()
        row(0) = 3
        row(1) = "Steve"
        row(2) = 18
        myTable.Rows.Add(row)
 
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        Dim XmlStr As String = TextXml()
 
        MessageBox.Show(XmlStr)
 
    End Sub
 
    Private Function TextXml() As String
 
        Dim doc = New XElement("myHeader", _
                               New XElement("mySubHeader", _
                                            New XElement("Date", TextBox1.Text)))
 
        Dim data = From ele In myDataSet.Tables(0) _
                   Group ele By key = ele.Field(Of Integer)("A") Into Group _
                   Select key, Group
 
        For Each dataGroup In data
            Dim dataEle As New XElement("mydetails")
            Dim dataEleDept As New XElement("Department", dataGroup.key.ToString())
            dataEle.Add(dataEleDept)
            For Each gp In dataGroup.Group
                Dim dataEleDetail As New XElement("Name", gp.Field(Of String)("B"), _
                                                  New XElement("Amount", gp.Field(Of Integer)("C").ToString()))
                dataEleDept.Add(dataEleDetail)
            Next
            doc.Add(dataEle)
        Next
 
        doc.Save("D:\Temp\XmlData.xml")
 
 
        Return doc.ToString
    End Function
 
End Class

Open in new window

0
 
nick210577Author Commented:
Thanks this was exactly what I was after.  My actual project was a lot more complicated than this but you helped me understand how to use the group by in LINQ and figure out how to apply it.  Thanks again.
0
 
Fernando SotoCommented:
Not a problem, glad to help.  ;=)
0

Featured Post

Independent Software Vendors: 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!

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now