none
how to load datatable with xml RRS feed

  • Question

  • <tables>
        <row>
            <table_name>ticket</table_name>
            <record_key>68</record_key>
        </row>
        <row>
            <table_name>sales</table_name>
            <record_key>3001</record_key>    
        </row>
     </tables>

    how can i load a datatable with above xml string?
    Wednesday, August 9, 2006 10:40 PM

Answers

  • you could use a StringReader to read the xml from the string into the dataset. Using your example, this works for me:

     


    string theXmlString = "<tables><row><table_name>ticket</table_name><record_key>68</record_key></row><row>";
    theXmlString += "<table_name>sales</table_name><record_key>3001</record_key></row></tables>"; //you can put these 2 lines in one, this was just for formatting issues here on the reply
    StringReader theReader = new StringReader(theXmlString);
    DataSet theDataSet = new DataSet();
    theDataSet.ReadXml(theReader);
     
    //Data will be stored in Table[0]:
    this.theDataGridView.DataSource = theDataSet.Tables[0].DefaultView;


     

     

    does this help?

    Thursday, August 10, 2006 3:21 AM
    Moderator

All replies

  • you could load the xml into a dataset:

     



    DataSet theDataSet = new DataSet();
    theDataSet.ReadXml("XMLfile");
     

     

     

    does this help?

    Wednesday, August 9, 2006 10:46 PM
    Moderator
  • Just to add, I'm unsure if that would be a valid xml file for the dataset, in which case you would probably have to read the xml yourself and manually create the datatable
    Wednesday, August 9, 2006 10:47 PM
    Moderator
  • i have the the xml stored in a string variable and not a file.. Does that datatable support xml?
    Wednesday, August 9, 2006 10:51 PM
  • you could use a StringReader to read the xml from the string into the dataset. Using your example, this works for me:

     


    string theXmlString = "<tables><row><table_name>ticket</table_name><record_key>68</record_key></row><row>";
    theXmlString += "<table_name>sales</table_name><record_key>3001</record_key></row></tables>"; //you can put these 2 lines in one, this was just for formatting issues here on the reply
    StringReader theReader = new StringReader(theXmlString);
    DataSet theDataSet = new DataSet();
    theDataSet.ReadXml(theReader);
     
    //Data will be stored in Table[0]:
    this.theDataGridView.DataSource = theDataSet.Tables[0].DefaultView;


     

     

    does this help?

    Thursday, August 10, 2006 3:21 AM
    Moderator
  • How  do we put the xml

    <a loan="1">

    <b Payee="A" amt="300" />

    <b Payee="B" amt="409" />

    <b Payee="C" amt="390" />

    </a>

    <a loan="2">

    <b Payee="A" amt="200" />

    <b Payee="B" amt="450" />

    <b Payee="C" amt="300" />

    </a>

     

    into a datatable?

     

    SAM

    Friday, August 17, 2007 2:49 PM
  • Hi ahmedilyas,

    I tried that, but it creates me a table for each tag (ones with values inside). I tried this:

    Dim
    dsXML As New DataSet Dim dtXML As New DataTable("Tabla1") dsXML.Tables.Add(dtXML) '[...] Dim s As New StringReader(webservice.vote("XXXX", "CCCC")) dsXML.Tables(0).ReadXmlSchema(s) <br/>dsXML.Tables(0).ReadXml(s)
    The XML string: "
    <response>
    <result>xxx</result>
    <data.lst>
              <i.0>
                        <code>xxxx1111</code>
                        <desc>xxxxx</desc>
              </i.0>
    </data.lst>
    </response>"

    But with an error when it tries to read the schema: "DataTable 'Tabla1' does not match to any DataTable in source."

    How can I create a datatable that creates me an automatic schema? i.e. three columns for each three values.

    Thank you in advance
    Programmer
    Monday, April 6, 2009 7:40 AM
  • has there been an answer to this?
    this is driving me nuts.
    the error message is 'unhelpful' at best. I've tried every combination, at first thinking that this needed a table on the side and could not new one directly into the DataSet (which makes no sense, and that wasn't the problem):

    _dsRDus =

    new DataSet( );

    _dtOrgCat =

    new DataTable( "OrgCat" );

    _dtOrgCat.ReadXmlSchema( MapPath(

    "App_Data/OrganizationCategories.xsd" ) ); //  error,

    _dtOrgCat.ReadXml( MapPath(

    "App_Data/OrganizationCategories.xml" ) );

    the error message to which I reply, well, yeah, I'm trying to make a new one and define it for you.

    the XML and XSD files were generated from an Access DataBase.


    Mitch "Success is not the result of spontaneous combustion; you must set yourself on fire." www.BeingA.com/Teacher/FacultyRoom: a site where Teachers are helping other Teachers become better Teachers www.BeingA.com/TeachersAide: 'Quiz/Test by Game Playing' a Web-Based System
    Tuesday, May 12, 2009 12:15 AM
  • well, my question above certainly looks interesting. I didn't format it that way.
    well, on purpose anyway...
    Mitch "Success is not the result of spontaneous combustion; you must set yourself on fire." www.BeingA.com/Teacher/FacultyRoom: a site where Teachers are helping other Teachers become better Teachers www.BeingA.com/TeachersAide: 'Quiz/Test by Game Playing' a Web-Based System
    Tuesday, May 12, 2009 12:18 AM
  • Man, I have been looking for a lot of time this code.And finally I found you, this code is perfect
    thank you
    Friday, February 12, 2010 8:05 PM
  • Imports System.Xml Public Class Form1 Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load Dim DataGridView1 As New DataGridView Me.Controls.Add(DataGridView1) Dim sXML As String = "<tables><row><table_name>ticket</table_name><record_key>68</record_key></row><row><table_name>sales</table_name><record_key>3001</record_key></row> </tables>" DataGridView1.DataSource = XMLtoDataTable(sXML, "Table") End Sub Public Function XMLtoDataTable(sXML As String, TableName As String) As DataTable Try Dim rstTable As DataTable = New DataTable(TableName) Dim myXMLDOC As New Xml.XmlDocument myXMLDOC.LoadXml(sXML) Dim myNode As XmlNode = myXMLDOC.FirstChild For Each n As XmlNode In myNode.ChildNodes(0).ChildNodes rstTable.Columns.Add(n.Name) Next For Each n As XmlNode In myNode.ChildNodes Dim myRow As DataRow = rstTable.Rows.Add For Each c In n.ChildNodes

    'Added to handle missing elements in 1st row

    If rstTable.Columns.Item(c.name) Is Nothing Then rstTable.Columns.Add(c.Name) End If

    myRow.Item(c.Name) = c.InnerText Next Next Return rstTable Catch ex As Exception MsgBox(Err.Description) Return Nothing End Try End Function End Class



    • Edited by jhersey Thursday, August 9, 2012 11:49 PM
    Thursday, August 9, 2012 11:33 PM
  • I know this is old but I found all the answers here a bit unclear. An XML file is very simply loaded into a DATASET and can also be save back again.  Here is some sample code...

    using System;
    using System.Data;
    using System.IO;
    using System.Xml;
    
    namespace DataSetToXML
    {
        class Program
        {   
            // Create a Data Set for the program to use
            private static DataSet ds = new DataSet();
    
            // Our basic XML file text to use if file doesn't exist
            private static String BaseXMLString =
    @"<?xml version=""1.0"" encoding=""utf-8""?>
    <DataSet_01>
      <Table_01>
        <Column_01>Tbl 1 Col 1 Row 1</Column_01>
        <Column_02>Tbl 1 Col 2 Row 1</Column_02>
      </Table_01>
      <Table_01>
        <Column_01>Tbl 1 Col 1 Row 2</Column_01>
        <Column_02>Tbl 1 Col 2 Row 2</Column_02>
      </Table_01>
      <Table_01>
        <Column_01>Tbl 1 Col 1 Row 3</Column_01>
        <Column_02>Tbl 1 Col 2 Row 3</Column_02>
      </Table_01>
      <Table_01>
        <Column_01>Tbl 1 Col 1 Row 4</Column_01>
        <Column_02>Tbl 1 Col 2 Row 4</Column_02>
      </Table_01>
      <Table_02>
        <Column_01>Tbl 2 Col 1 Row 1</Column_01>
        <Column_02>Tbl 2 Col 2 Row 1</Column_02>
        <Column_03>Tbl 2 Col 3 Row 1</Column_03>
      </Table_02>
      <Table_02>
        <Column_01>Tbl 2 Col 1 Row 2</Column_01>
        <Column_02>Tbl 2 Col 2 Row 2</Column_02>
        <Column_03>Tbl 2 Col 3 Row 2</Column_03>
      </Table_02>
    </DataSet_01>";
    
            // Program's main entry point
            static void Main(string[] args)
            {
                ReadXMLIntoDataSet();
                DisplayDataSet();
                ChangeDataSet();
                SaveToXML();
    
                // Dispose of Data Set while waiting for console to close
                ds.Dispose();
    
                // Wait for user to close console
                Console.WriteLine();
                Console.WriteLine("Press any key to close.");
                Console.ReadKey();
    
                // Exit without error
                Environment.Exit(0);
            }
    
    
            private static void ReadXMLIntoDataSet()
            {
                try
                {
                    // If example XML file doesn't exist create it
                    if (!File.Exists("Example.xml"))
                    {
                        File.WriteAllText("Example.xml", BaseXMLString, System.Text.Encoding.UTF8);
                    }
                    // Read XML file into our Data Set
                    ds.ReadXml("Example.xml");
                }
                catch (Exception ex)
                {
                    DisplayException(ex);
                }
            }
    
    
            private static void DisplayDataSet()
            {
                try
                {
                    // Loop through our Data Set's tables
                    for (int tIdx = 0; tIdx < ds.Tables.Count; tIdx++)
                    {
                        // Create a Data Table in memory
                        using (DataTable dt = ds.Tables[tIdx])
                        {
                            // Display Table Name
                            Console.WriteLine("Table Name: " + dt.TableName);
    
                            // Display column names in tab delimited form
                            for (int cIdx = 0; cIdx < dt.Columns.Count; cIdx++)
                            {
                                Console.Write(dt.Columns[cIdx].ColumnName);
                                if (cIdx != dt.Columns.Count -1) Console.Write("\t");
                            }
                            Console.WriteLine();
    
                            // Display rows in tab delimited form
                            foreach (DataRow dr in dt.Rows)
                            {
                                for (int cIdx = 0; cIdx < dt.Columns.Count; cIdx++)
                                {
                                    Console.Write(dr[cIdx]);
                                    if (cIdx != dt.Columns.Count - 1) Console.Write("\t");
                                }
                                Console.WriteLine();
                            }
                        }
                        Console.WriteLine();
                        Console.WriteLine();
                    }
                }
                catch (Exception ex)
                {
                    DisplayException(ex);
                }
            }
    
    
            private static void ChangeDataSet()
            {
                try
                {
                    // Create a Data Table in memory of our 'Table_01'
                    using (DataTable dt = ds.Tables["Table_01"])
                    {
                        // Modify Row 1 of our Table
                        dt.Rows[0].ItemArray = new Object[] { String.Empty, DateTime.Now.ToString() };
                        // Save changes to our Data Set
                        ds.AcceptChanges();
                    }
    
                    // You could also avoid creating another Data Table and using more memory
                    // by directly modifying the Data Set like so...
                    ds.Tables["Table_01"].Rows[0].ItemArray = new Object[] { String.Empty, DateTime.Now.ToString() };
                    ds.AcceptChanges();
                }
                catch (Exception ex)
                {
                    DisplayException(ex);
                }
            }
    
    
            private static void SaveToXML()
            {
                try
                {
                    // Write XML file from our Data Set
                    ds.WriteXml("Example.xml",XmlWriteMode.IgnoreSchema);
                }
                catch (Exception ex)
                {
                    DisplayException(ex);
                }
            }
    
            
            private static void DisplayException(Exception exception)
            {
                Console.WriteLine();
                Console.WriteLine(exception.Message);
                Console.WriteLine();
            }
        }
    }

    Friday, August 23, 2013 4:23 PM