Reading XML and return DataTable using XML VB,net

If I have an XML file I want to insert that in a Datatable. The format of the xml file is like below:

<userid ID="37729">
  <TestId ID="84" TimeRemaining="60" />
  <QuestId ID="1">
    <Answer1>
    </Answer1>
    <Answer2>B</Answer2>
    <Answer3>
    </Answer3>
    <Answer4>
    </Answer4>
  </QuestId>
</userid>

Select allOpen in new window

Now I want to insert that in a data table like below:
Question Id     Answer1      Answer2        Answer3        Answer4
1                        A                                                                D

2                                                   B              C    

3                                                                    C                    

 I would first create a DataTable with the columns that you require, then populate it via Linq-to-XML.
You could use a Select query to create an object that represents each row, then use the standard approach for creating DataRows for each item ...

XDocument doc = XDocument.Load(yourXMLString);

var rows = doc.Descendants("QuestId").Select(el => new()
    {
      Answer1 = el.Element("Answer1").Value,
      Answer2 = el.Element("Answer2").Value,
      Answer3 = el.Element("Answer3").Value,
      Answer4 = el.Element("Answer4").Value,
    });

// iterate over the rows and add to DataTable ...

Select allOpen in new window

Dear!!!

Follow this link to solve your problem.

http://msdn.microsoft.com/en-us/library/fx29c3yd(v=vs.71).aspx

Let me know also how you need the table .i.e structure so that I can help you more.

To better understand what's going on you need to analyze your xml and resulting  (possible) dataset.

The thing is that, first, you cannot put your xml into one table. So, your dataset, after reading the xml will contain a number of tables. Data types for columns depenf on a schema file (not provided in your examople) or will be defaulted automatically, etc.

So, after you have a data set you probably want to save the data into a database? So, you have to decide with tables, or you already have the tables?

Etc.

Anyway, I used your xml (just removed namespaces) to write and run the following code.
The output can be useful for further thoughts.

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim ds As New DataSet
        ds.ReadXml("XMLFile1.xml")
        Dim tableCount As Integer = ds.Tables.Count

        For Each dt As DataTable In ds.Tables
            Debug.WriteLine("====DataTable.Name = " & dt.TableName & "  =================")
            For Each dc As DataColumn In dt.Columns
                Debug.WriteLine("--- Column name = " & dc.ColumnName & "; data type = " & dc.DataType.ToString)
            Next
            Debug.WriteLine("")
        Next

        For Each dr As DataRelation In ds.Relations
            Debug.WriteLine("data relation parent table - " & dr.ParentTable.TableName & "; relation name = " & dr.RelationName)
        Next
    End Sub

Select allOpen in new window

The output is like below:


====DataTable.Name = Envelope  =================
--- Column name = Envelope_Id; data type = System.Int32
--- Column name = envelopeId; data type = System.String
--- Column name = majorVersion; data type = System.String
--- Column name = minorVersion; data type = System.String

====DataTable.Name = Header  =================
--- Column name = Header_Id; data type = System.Int32
--- Column name = Envelope_Id; data type = System.Int32

====DataTable.Name = TransactionBlk  =================
--- Column name = EncodingTypeCd; data type = System.String
--- Column name = actor; data type = System.String
--- Column name = mustUnderstand; data type = System.String
--- Column name = name; data type = System.String
--- Column name = majorVersion; data type = System.String
--- Column name = minorVersion; data type = System.String
--- Column name = Header_Id; data type = System.Int32

====DataTable.Name = Body  =================
--- Column name = Body_Id; data type = System.Int32
--- Column name = Envelope_Id; data type = System.Int32

====DataTable.Name = Request  =================
--- Column name = Request_Id; data type = System.Int32
--- Column name = Body_Id; data type = System.Int32

====DataTable.Name = BasicGrp  =================
--- Column name = BasicGrp_Id; data type = System.Int32
--- Column name = Request_Id; data type = System.Int32

====DataTable.Name = PCNGrp  =================
--- Column name = PCNIssueDt; data type = System.String
--- Column name = BasicGrp_Id; data type = System.Int32

====DataTable.Name = SuppGrp  =================
--- Column name = SuppNbr; data type = System.String
--- Column name = SuppGrp_Id; data type = System.Int32
--- Column name = BasicGrp_Id; data type = System.Int32

====DataTable.Name = PersonelInfoGrp  =================
--- Column name = SfxNm; data type = System.String
--- Column name = CustBirthDt; data type = System.String
--- Column name = SuppGrp_Id; data type = System.Int32

====DataTable.Name = AddrInfoGrp  =================
--- Column name = AddrTypeCd; data type = System.String
--- Column name = IPAddr; data type = System.String
--- Column name = SuppGrp_Id; data type = System.Int32

====DataTable.Name = PhoneGrp  =================
--- Column name = TypeCd; data type = System.String
--- Column name = SuppGrp_Id; data type = System.Int32

data relation parent table - Header; relation name = Header_TransactionBlk
data relation parent table - SuppGrp; relation name = SuppGrp_PersonelInfoGrp
data relation parent table - SuppGrp; relation name = SuppGrp_AddrInfoGrp
data relation parent table - SuppGrp; relation name = SuppGrp_PhoneGrp
data relation parent table - BasicGrp; relation name = BasicGrp_PCNGrp
data relation parent table - BasicGrp; relation name = BasicGrp_SuppGrp
data relation parent table - Request; relation name = Request_BasicGrp
data relation parent table - Body; relation name = Body_Request
data relation parent table - Envelope; relation name = Envelope_Header
data relation parent table - Envelope; relation name = Envelope_Body

THanks anarki_jimbel.

I have been doing exactly the same but When I am making joins it is giving me only One Row INstead of Two.. Here are my Joins:

Dim obj = (From t1 In _dataset.Tables("SuppGrp") _
                    Join t2 In _dataset.Tables("PersonelInfoGrp") On t1.Item("SuppGrp_Id") Equals t2.Item("SuppGrp_Id") _
                   Join t3 In _dataset.Tables("CustinfoGrp") On t1.Item("SuppGrp_Id") Equals t3.Item("SuppGrp_Id") _
                   Join t4 In _dataset.Tables("CompanyInfoGrp") On t1.Item("SuppGrp_Id") Equals t4.Item("SuppGrp_Id") _
                   Join t5 In _dataset.Tables("AppDecGrp") On t1.Item("SuppGrp_Id") Equals t5.Item("SuppGrp_Id") _
                   Select SuppNbr = t1.Item("SuppNbr").ToString, _
                    MidNm = t2.Item("MidNm"), SfxNm = t2.Item("SfxNm"), PersonelInfoGrp_SuppGrp_Id = t2.Item("SuppGrp_Id"), FirstNm = t2.Item("FirstNm"), LstNm = t2.Item("LstNm"), CustBirthDt = t2.Item("CustBirthDt"), _
                    CustIdTypeCd = t3.Item("CustIdTypeCd"), CompanyInfo_GrpSuppGrp_Id = t3.Item("SuppGrp_Id"), CustId = t3.Item("CustId"), Billind = t3.Item("Billind"), _
                    CompanyInfoGrp_SuppGrp_Id = t4.Item("SuppGrp_Id"), CompanyNm = t4.Item("CompanyNm"), _
                    AppDecGrp_SuppGrp_Id = t5.Item("SuppGrp_Id"), FinalDecRsnCd1 = t5.Item("FinalDecRsnCd1")).ToList

Scratching My Head.. What I am doing Wrong Here......

Would it be possible for you to help me out...here........?

As you can See there are Two Rows Avaliable in ParentTable i.e. SuppGrp but When I am making joins I am getting only One Row...Pls Assist.

13:  Table Name Is   :SuppGrp
ColumnName  :SuppNbr
ColumnValue  :00
ColumnName  :SuppGrp_Id
ColumnValue  :0
ColumnName  :BasicGrp_Id
ColumnValue  :0
ColumnName  :SuppNbr
ColumnValue  :01
ColumnName  :SuppGrp_Id
ColumnValue  :1
ColumnName  :BasicGrp_Id
ColumnValue  :0
End OF Table Column
                   
                   
14:  Table Name Is   :PersonelInfoGrp
ColumnName  :SfxNm
ColumnValue  :
ColumnName  :LstNm
ColumnValue  :MIMS
ColumnName  :FirstNm
ColumnValue  :MILTON
ColumnName  :MidNm
ColumnValue  :
ColumnName  :CustBirthDt
ColumnValue  :1980284
ColumnName  :SuppGrp_Id
ColumnValue  :0
ColumnName  :SfxNm
ColumnValue  :RAY
ColumnName  :LstNm
ColumnValue  :CHOWDARY
ColumnName  :FirstNm
ColumnValue  :MILTONS
ColumnName  :MidNm
ColumnValue  :MAX
ColumnName  :CustBirthDt
ColumnValue  :0000000
ColumnName  :SuppGrp_Id
ColumnValue  :1
End OF Table Column
                   
                   
15:  Table Name Is   :CustinfoGrp
ColumnName  :CustIdTypeCd
ColumnValue  :S
ColumnName  :CustId
ColumnValue  :666330468
ColumnName  :Billind
ColumnValue  :1
ColumnName  :SuppGrp_Id
ColumnValue  :0
ColumnName  :CustIdTypeCd
ColumnValue  :
ColumnName  :CustId
ColumnValue  :
ColumnName  :Billind
ColumnValue  :1
ColumnName  :SuppGrp_Id
ColumnValue  :1
End OF Table Column
                   
                   
16:  Table Name Is   :CompanyInfoGrp
ColumnName  :CompanyNm
ColumnValue  :BURDETTE CORP
ColumnName  :SuppGrp_Id
ColumnValue  :0
End OF Table Column
                   
                   
17:  Table Name Is   :AppDecGrp
ColumnName  :FinalDecRsnCd1
ColumnValue  :O5
ColumnName  :SuppGrp_Id
ColumnValue  :0
ColumnName  :FinalDecRsnCd1
ColumnValue  :33
ColumnName  :SuppGrp_Id
ColumnValue  :1
End OF Table Column

Hi anarki_jimbel.,

I got the Issue i.e. "CompanyInfoGrp" contains Single Record becaue of which the resultant Table Contains Single record....

Join t4 In _dataset.Tables("CompanyInfoGrp") On t1.Item("SuppGrp_Id") Equals t4.Item("SuppGrp_Id") _

How Can I Avoid This.... Is it POssible to Make Left OUter or Right Outer Join here.. or any other way Around....

Looking forward to hearing from you....

I will try to think later - busy today a bit...

Thanks anarki_jimbel.... I am waiting for your reply.. In the Meanwhile I am also trying on the same.... Pls Help....

Thanx.. It cdn't resolve my problem.. but you help me in understanding the manipulation ways.. thanks a alot.. and Aplogies for my late reply