Hi all,
I want to insert an xml file into sql table, xml file will looks like the following:
="1.0"="UTF-8"
<invoices BatchNo ="01_040115_000009" BatchDate="2016-03-25" type="I">
<invoice InvoiceMasterID="7576">
<InvoiceImagePage>
<InvoiceImagePageNo>1</InvoiceImagePageNo>
<InvoiceImagePath>/Invoices/0000000750.jpg</InvoiceImagePath>
</InvoiceImagePage>
<InvoiceImagePage>
<InvoiceImagePageNo>2</InvoiceImagePageNo>
<InvoiceImagePath>/Invoices/0000000751.jpg</InvoiceImagePath>
</InvoiceImagePage>
</invoice>
<invoice InvoiceMasterID="7577">
<InvoiceImagePage>
<InvoiceImagePageNo>1</InvoiceImagePageNo>
<InvoiceImagePath>/Invoices/0000000752.jpg</InvoiceImagePath>
</InvoiceImagePage>
</invoice>
</invoices>
I want to parse XML like:
InvoiceMasterID=7576,
InvoiceImagePageNo=1, InvoiceImagePath=/Invoices/0000000750.jpg
InvoiceImagePageNo=2, InvoiceImagePath=/Invoices/0000000751.jpg
&&
InvoiceMasterID=7577,
InvoiceImagePageNo=1, InvoiceImagePath=/Invoices/0000000752.jpg
I want to store this data in database table as InvoiceMasterID wise. like the following,
InvoiceMasterID | InvoiceImagePageNo | InvoiceImagePath |
7576 | 1 | /Invoices/0000000750.jpg |
7576 | 2 | /Invoices/0000000751.jpg |
7577 | 1 | /Invoices/0000000750.jpg |
Please help me
What I have tried:
i have tried through slqbulkcopy code like this
for (int x = 0; x < file.Length; x++)
{
using (SqlConnection con = new SqlConnection(cs))
{
DataSet ds = new DataSet();
ds.ReadXml(file[x].FullName);
DataTable dtinvoices = ds.Tables["invoices"];
DataTable dtinvoice = ds.Tables["invoice "];
DataTable dtEmp = ds.Tables["Employee"];
con.Open();
if(dtinvoices !=null)
{
using (SqlBulkCopy bc = new SqlBulkCopy(con))
{
bc.DestinationTableName = "Departments";
bc.ColumnMappings.Add("BatchNo", "BatchNo");
bc.ColumnMappings.Add("BatchDate", "BatchDate");
bc.ColumnMappings.Add("type", "type");
bc.WriteToServer(dtinvoices );
}
}
if(dtinvoice !=null)
{
using (SqlBulkCopy bc = new SqlBulkCopy(con))
{
bc.DestinationTableName = "Departments";
bc.ColumnMappings.Add("InvoiceMasterID", "InvoiceMasterID");
bc.ColumnMappings.Add("InvoiceImagePageNo", "InvoiceImagePageNo");
bc.ColumnMappings.Add("InvoiceImagePath", "InvoiceImagePath");
bc.WriteToServer(dtinvoice );
}
}
}
}
the problem is InvoiceImagePageNo and InvoiceImagePath this nodes not show in dtinvoice tables.
Please guide me.