关于XML文件写入SQL的问题
我有一个XML文件：
<?xml version="1.0" standalone="yes" ?>
- <ISCMDB>
- <Department>
<DepartmentID>D001,D002,D003,D004,D005,D006,D007,</DepartmentID>
<DepartmentName>人事部,销售部,仓管部,采购部,运输部,财务部,高管层,</DepartmentName>
</Department>
- <BackProductInfo>
<ID>197,211,214,</ID>
<BackInvoiceID>TH200782134026,TH200782134138,TH200782164339,</BackInvoiceID>
<BackFromID>5454,1227,5454,</BackFromID>
<BrandID>1,1,1,</BrandID>
<CustomerID>2,1,2,</CustomerID>
<TypeID>17,17,17,</TypeID>
<Saleslip>FH20078293350,XS200782105534,FH20078216845,</Saleslip>
<Quantity>5,10,10,</Quantity>
<AllWithdrawalMoney>640,1280,1280,</AllWithdrawalMoney>
<WithdrawalDate>2007-8-1 0:00:00,2007-8-1 0:00:00,2007-8-2 0:00:00,</WithdrawalDate>
<Proposer>啊,啊,啊,</Proposer>
<Assessor>啊,啊,,</Assessor>
<CheckDate>2007-8-2 13:41:11,2007-8-2 13:43:21,,</CheckDate>
<CheckState>,1,,</CheckState>
<CheckOpinion>,好!,,</CheckOpinion>
</BackProductInfo>
- <Shop>
<ShopID>1,5454,10006,10010,10011,10012,</ShopID>
<BrandID>1,2,1,1,1,2,</BrandID>
<AreaID>125,258,258,125,125,125,</AreaID>
<ShopName>亚贸中心,广埠屯中心,群光中心,洪山店,新世界中心,亚贸广场分店,</ShopName>
<DeportID>CK005,CK004,CK003,CK012,CK001,CK002,</DeportID>
<Remark>,,656,销售总额暂时领先,,很一般,</Remark>
</Shop>
</ISCMDB>
ISCMDB是数据库的名字，依次节点为数据库的表名、字段名，值为字段的相关值
如何将这个XML文件再写入到SQL的ISCMDB文件啊？
[解决办法]
你先定义一个模板文件XML格式，
格式应该如下
<?xml version="1.0" encoding="UTF-8" ?>
<tables>
<table name="Department " Path="Computer">
<field name="DepartmentID" />
<field name="DepartmentName" />
</table>
<tables>
<table name="BackProductInfo" Path="BackProductInfo">>
<field name="ID" />
………………………
</table>
</tables>
然后根据给定的模板，匹配你的XML每个节点的内容再循环构建SQL,插入对应的表中
给你一个例子
#region 写入表中
/// <summary>
/// 将单个XMl中的数据写入表中
/// </summary>
/// <returns></returns>
private bool WriteTableByXMLNew()
{
int lngComputerID=0;
System.Xml.XmlNodeList list ;
System.Xml.XmlNodeList EveryTable = doc.DocumentElement.SelectNodes("table");
//循环模板文件，得到每个表名
foreach(XmlNode tmpTable in EveryTable)
{
string strName=tmpTable.Attributes["name"].InnerText;
string sqlCmd="",val,field,sqlVal="";
string strPath="";
this.SetFieldMaxLen(strName);
strPath=tmpTable.Attributes["Path"].InnerText;
list = this.docNew.SelectNodes (strPath);
#region 循环所有子节点记录，写入全部数据
for (int i = 0;i < list.Count ;i ++)
{
//构建SQL语句
sqlCmd= "insert into [" + strName + "] (";
//写入一个表的数据
for (int ii=0;ii<tmpTable.ChildNodes.Count;ii++)
{
field = tmpTable.ChildNodes[ii].Attributes["name"].InnerText;
sqlCmd+="[" +tmpTable.ChildNodes[ii].Attributes["name"].InnerText+"]";
sqlCmd+=" ,";
val = this.GetFieldValue (list.Item (i),field);
sqlVal += "'"+val + "',";
}
sqlCmd = sqlCmd.Substring (0,sqlCmd.Length -1);
sqlCmd += ") values (" + sqlVal.Substring (0,sqlVal.Length -1)+")";
if (!this.Sqlca .ExecuteNonQuery (sqlCmd))
{
this.LastError = this.Sqlca .LastError ;
return false;
}
}
#endregion
}
return true;
}
#endregion
[解决办法]
用DataAdapter 的Update()方法
先从数据库把表架构读出来。在把xml里的数据读入到虚拟表
将虚拟表赋给架构表，再用Update（）方法更新数据库就行了
