How to Read Xml in DataTable or DataSet

Feroz Khan

Hi,

I am trying to use read xml file in dataset but showing some error i want to read same file in dataset, can anyone help me for this.

below is my code..

XmlReader xmlFile;
xmlFile = XmlReader.Create(@"C:\Users\Feroz\Desktop\xml Data\file.xml", new XmlReaderSettings());
DataSet ds = new DataSet("tbl");
ds.ReadXml(xmlFile);
int i = 0;
for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
MessageBox.Show(ds.Tables[0].Rows[i].ItemArray[2].ToString());
}

showing error - Column name 'lWorkstationNmbr' is defined for different mapping types.

xml file.

xml version="1.0" encoding="UTF-8"?>
<ITX_CLOSE_EXPORT>
<STORE_INFO>
<storeId>6644storeId>
<closeDate>20180401closeDate>
<sessionDateFrom>20180331sessionDateFrom>
<sessionTimeFrom>220334sessionTimeFrom>
<sessionDateTo>20180401sessionDateTo>
<sessionTimeTo>221204sessionTimeTo>
<version>3.5.4.1version>
STORE_INFO>
<VALID_TICKETS>
-<SALE_LINE_ITEMS>
-<ITEM lTaNmbr="2915" lWorkstationNmbr="1" lRetailStoreId="6644">
<lRetailStoreID>6644lRetailStoreID>
<lWorkstationNmbr>1lWorkstationNmbr>
<lTaNmbr>2915lTaNmbr>
<szItxOperationType>1szItxOperationType>
<szItxSerial>SszItxSerial>
<lItxCurrentValue>1367lItxCurrentValue>
<szDate>20180401szDate>
<szTime>112629szTime>
<lOperatorID>11532lOperatorID>
<lItxLineNmbr>1lItxLineNmbr>
<szItemLookupCode>4844001370632szItemLookupCode>
<lItxYear>2018lItxYear>
<szItxCampaign>VszItxCampaign>
<lItxFamilyCode>92lItxFamilyCode>
<lItxSubFamilyCode>102lItxSubFamilyCode>
<szItxPeriodDesc>SszItxPeriodDesc>
<szMerchHierarchyLevelCode>1szMerchHierarchyLevelCode>
<dTaQty>1.0000dTaQty>
<dTaPrice>17.0000dTaPrice>
<dOrgPrice>0.0000dOrgPrice>
<dTaDiscount>0.0000dTaDiscount>
<dItxTaxPercent>18.0000dItxTaxPercent>
<bIsVoidReceiptSLI>0bIsVoidReceiptSLI>
<szType>ART_SALEszType>
<szTaType>SAszTaType>
<lItxOriginalRetailStoreID>0lItxOriginalRetailStoreID>
<lItxOriginalWorkstationNmbr>0lItxOriginalWorkstationNmbr>
<lItxOriginalTaNmbr>0lItxOriginalTaNmbr>
<lItxOriginalCurrentValue>0lItxOriginalCurrentValue>
<szItxOriginalSerial>0szItxOriginalSerial>
<szItxOriginalDate>0szItxOriginalDate>
<szEmployeeNumber>-1szEmployeeNumber>
ITEM>

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar6y ago
Hi Firoz,

Your XML is not in correct formate thats why it is giving error,
You can verify your XML using online tool like XML is valid or not. Here iis one web url where you can validate your xml
https://codebeautify.org/xmlvalidator
xml version="1.0" encoding="UTF-8"?>
<ITX_CLOSE_EXPORT>
    <STORE_INFO>
        <storeId>6644storeId>
        <closeDate>20180401closeDate>
        <sessionDateFrom>20180331sessionDateFrom>
        <sessionTimeFrom>220334sessionTimeFrom>
        <sessionDateTo>20180401sessionDateTo>
        <sessionTimeTo>221204sessionTimeTo>
        <version>3.5.4.1version>
    STORE_INFO>
    <VALID_TICKETS>
        <SALE_LINE_ITEMS>
            <ITEM lTaNmbr="2915" lWorkstationNmbr="1" lRetailStoreId="6644">
                <lRetailStoreID>6644lRetailStoreID>
                <lWorkstationNmbr>1lWorkstationNmbr>
                <lTaNmbr>2915lTaNmbr>
                <szItxOperationType>1szItxOperationType>
                <szItxSerial>SszItxSerial>
                <lItxCurrentValue>1367lItxCurrentValue>
                <szDate>20180401szDate>
                <szTime>112629szTime>
                <lOperatorID>11532lOperatorID>
                <lItxLineNmbr>1lItxLineNmbr>
                <szItemLookupCode>4844001370632szItemLookupCode>
                <lItxYear>2018lItxYear>
                <szItxCampaign>VszItxCampaign>
                <lItxFamilyCode>92lItxFamilyCode>
                <lItxSubFamilyCode>102lItxSubFamilyCode>
                <szItxPeriodDesc>SszItxPeriodDesc>
                <szMerchHierarchyLevelCode>1szMerchHierarchyLevelCode>
                <dTaQty>1.0000dTaQty>
                <dTaPrice>17.0000dTaPrice>
                <dOrgPrice>0.0000dOrgPrice>
                <dTaDiscount>0.0000dTaDiscount>
                <dItxTaxPercent>18.0000dItxTaxPercent>
                <bIsVoidReceiptSLI>0bIsVoidReceiptSLI>
                <szType>ART_SALEszType>
                <szTaType>SAszTaType>
                <lItxOriginalRetailStoreID>0lItxOriginalRetailStoreID>
                <lItxOriginalWorkstationNmbr>0lItxOriginalWorkstationNmbr>
                <lItxOriginalTaNmbr>0lItxOriginalTaNmbr>
                <lItxOriginalCurrentValue>0lItxOriginalCurrentValue>
                <szItxOriginalSerial>0szItxOriginalSerial>
                <szItxOriginalDate>0szItxOriginalDate>
                <szEmployeeNumber>-1szEmployeeNumber>
            ITEM>
        SALE_LINE_ITEMS>
    VALID_TICKETS>
ITX_CLOSE_EXPORT>
Rajanikant Hawaldar6y ago
This may help you please check it https://stackoverflow.com/questions/4461123/how-to-read-xml-into-a-datatable
Riddhi Valecha6y ago
Dear All,
How do I get the xml output in a datatable ?
- column names
and
"); width: 10px; height: 10px; margin-left: -10px; vertical-align: bottom; display: inline-block; cursor: pointer; user-select: none;"> Index="0"> - vales
Altaf Ansari8y ago
Use following code :

public DataTable CreateDataTableXML(string XmlFile)
{
    XmlDocument doc = new XmlDocument();

    doc.Load(XmlFile);

    DataTable Dt = new DataTable();

    try
    {
        Dt.TableName = GetTableName(XmlFile);
        XmlNode NodoEstructura = doc.DocumentElement.ChildNodes.Cast().ToList()[0];
        progressBar1.Maximum = NodoEstructura.ChildNodes.Count;
        progressBar1.Value = 0;
        foreach (XmlNode columna in NodoEstructura.ChildNodes)
        {
            Dt.Columns.Add(columna.Name, typeof(String));
            Progress();
        }

        XmlNode Filas = doc.DocumentElement;
        progressBar1.Maximum = Filas.ChildNodes.Count;
        progressBar1.Value = 0;
        foreach (XmlNode Fila in Filas.ChildNodes)
        {
            List<string> Valores = Fila.ChildNodes.Cast().ToList().Select(x => x.InnerText).ToList();
            Dt.Rows.Add(Valores.ToArray());
            Progress();
        }
    }
    catch (Exception ex)
    {

    }
    return Dt;
}
Feroz Khan8y ago
I am using oracle database 10g but not yet created tables in database but first i need to import in dataset or datatable then i will make the table to import from datatable to database.
Jayakumar Balasubramaniam8y ago
Okay, I beleive we do have a ColumnType called XML in SQL server. Will your project model allow to save the RAW xml in database ?
Feroz Khan8y ago
Hi Jaykakumar,

Yes you are write i already did same delete node of duplicate then its working but i do not want to change any thing in xml becasue its generating auto from our server and i need to import in database.
please is there any solution for this?
Jayakumar Balasubramaniam8y ago
Hi Feroz,

Problem is caused by your Itrem Node, becaseu the attribute name in Item node and sub elements in name in Item nodes are same that's the issue. If we remove/update the names and try the same code it works.
Feroz Khan8y ago
Hi Jayakumar,

thank you for your reply i trying below code which you provide me but problem is still shwoing one error please see below.

string xmlFile = @"C:\Users\feroz.khan\Desktop\xml file\file.xml";
DataSet dataSet = new DataSet();
dataSet.ReadXml(xmlFile, XmlReadMode.InferSchema);
dataGridView1.DataSource = dataSet;

Error: Column name 'lWorkstationNmbr' is defined for different mapping types.
Jayakumar Balasubramaniam8y ago
Hi Feroz,

Please try below code snippet.

string xmlFile = "Data.xml";
DataSet dataSet = new DataSet();
dataSet.ReadXml(xmlFile, XmlReadMode.InferSchema);