如何在MVC中将XML数据插入SQL表 [英] How to insert XML data to SQL table in MVC

问题描述

大家好，

我想将xml文件插入到sql表中，xml文件将如下所示：

 < span class =code-summarycomment><？  xml     version   =  1.0   编码  =  UTF-8    ？ >  
 <  发票   < span class =code-attribute> BatchNo     =  01_040115_000009    BatchDate   =  2016- 03-25    type   = 我 >  
 < ;   invoice     InvoiceMasterID  < span class =code-keyword> =  7576 >  
 <   InvoiceImagePage  >  
 <   InvoiceImagePageNo  >  1 <   / InvoiceImagePageNo  >  
 <   InvoiceImagePath  >  /Invoices/0000000750.jpg <   / InvoiceImagePath  >  
 <   / InvoiceImagePage  >  
 <   InvoiceImagePage  >  
 <   InvoiceImagePageNo  >  2 <   / InvoiceImagePageNo  >  
 <   InvoiceImagePath  >  /发票/ 0000000751.jpg <   / InvoiceImagePath  >  
 <   / InvoiceImagePage  >  
 <   / invoice  > ;  
 <   invoice     InvoiceMasterID   =  7577 >  
 <   InvoiceImagePage  >  
 <   InvoiceImagePageNo  >  1 <   / InvoiceImagePageNo  < span class =code-keyword>>  
 <   InvoiceImagePath  >  /Invoices/0000000752.jpg <   / InvoiceImagePath  >  
 <   / InvoiceImagePage  >  
 <   / invoice  >  
 <   /发票 >  

我想解析XML，如：



InvoiceMasterID = 7576，

InvoiceImagePageNo = 1，InvoiceImagePath = / Invoices / 0000000750.jpg

InvoiceImagePageNo = 2，InvoiceImagePath = /发票/ 0000000751.jpg



&&



InvoiceMasterID = 7577，
InvoiceImagePageNo = 1，InvoiceImagePath = / Invoices / 0000000752.jpg



我想将这些数据存储在数据库表中作为InvoiceMasterID。如下所示，

< TR>

InvoiceMasterID	InvoiceImagePageNo	InvoiceImagePath
7576	1	/Invoices/0000000750.jpg
7576	2	/Invoices/0000000751.jpg
7577	1	/Invoices/0000000750.jpg

请帮帮我



我尝试了什么：



i已经尝试过像这样的slqbulkcopy代码

< pre lang =c＃> for （ int x = 0 ; x < file.Length; x ++）
{
使用（SqlConnection con = new SqlConnection（cs））
{
DataSet ds = new DataSet（）;
ds.ReadXml（file [x] .FullName）;

DataTable dtinvoices = ds.Tables [ 发票];
DataTable dtinvoice = ds.Tables [ invoice];
DataTable dtEmp = ds.Tables [ Employee];
con.Open（）;
if （dtinvoices！= null ）
{
使用（SqlBulkCopy bc = new SqlBulkCopy（con））
{
bc.DestinationTableName = 部门;
// bc.ColumnMappings.Add（ID，ID）;
bc.ColumnMappings.Add（ BatchNo， BatchNo）;
bc.ColumnMappings.Add（ BatchDate， BatchDate）;
bc.ColumnMappings.Add（ type， type）;
bc.WriteToServer（dtinvoices）;
}
}
如果（dtinvoice！= null ）
{
使用（SqlBulkCopy bc = new SqlBulkCopy（con））
{
bc.DestinationTableName = 部门;
bc.ColumnMappings.Add（ InvoiceMasterID， InvoiceMasterID）;
bc.ColumnMappings.Add（ InvoiceImagePageNo， InvoiceImagePageNo）;
bc.ColumnMappings.Add（ InvoiceImagePath， InvoiceImagePath）;
bc.WriteToServer（dtinvoice）;
}
}
}
}

问题是InvoiceImagePageNo和InvoiceImagePath这个节点没有显示在dtinvoice表中。



请指导我。

解决方案

你好，



我认为您可以使用XML Reader来实现这一目标。请点击以下链接：

asp.net mvc 3 - 如何使用mvc4将xml数据保存到数据库 - Stack Overflow [ ^ ]



XML Reader [ ^ ]



您可以从XML读取数据，然后保存数据。我希望这会有所帮助。



谢谢

Hi all,
I want to insert an xml file into sql table, xml file will looks like the following:

<?xml version="1.0" encoding="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("ID", "ID");
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.

解决方案

Hello,

I think you can use XML Reader in order to achieve this. Please follow the below links:
asp.net mvc 3 - How to save xml data to database using mvc4 - Stack Overflow[^]

XML Reader[^]

You can read the data from XML and then save the data. I hope this helps someway.

Thanks

这篇关于如何在MVC中将XML数据插入SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！