怎样把XML文件导入到SQL某张表中?急··在线等··
XML代码如下:<?xml version="1.0" encoding="GB2312"?><DataExport>
<Table TableName="SEALLOG">
<Item>
<Field Name="ClerkID" Type="129" Value="0998162"/>
<Field Name="DepartID" Type="129" Value="80723"/>
<Field Name="Operate" Type="3" Value="33"/>
<Field Name="ActNO" Type="129" Value="807230200004969"/>
<Field Name="BillNo" Type="129" Value=""/>
<Field Name="BillSum" Type="129" Value=""/>
<Field Name="BillType" Type="129" Value=""/>
<Field Name="Ratio" Type="3" Value="0"/>
<Field Name="OperateTime" Type="135" Value="2010-01-11 13:51:09"/>
<Field Name="Remark" Type="129" Value="添加印鉴卡:1001110998162015"/>
</Item>
<Item>
<Field Name="ClerkID" Type="129" Value="0998162"/>
<Field Name="DepartID" Type="129" Value="80723"/>
<Field Name="Operate" Type="3" Value="24"/>
<Field Name="ActNO" Type="129" Value="807230200004969"/>
<Field Name="BillNo" Type="129" Value=""/>
<Field Name="BillSum" Type="129" Value=""/>
<Field Name="BillType" Type="129" Value=""/>
<Field Name="Ratio" Type="3" Value="0"/>
<Field Name="OperateTime" Type="135" Value="2010-01-11 13:52:02"/>
<Field Name="Remark" Type="129" Value="印鉴编号:1,备注:财务章,印章类型:1,印章形状:2,印鉴卡编号:1001110998162015"/>
</Item>
<Item>
<Field Name="ClerkID" Type="129" Value="0998162"/>
<Field Name="DepartID" Type="129" Value="80723"/>
<Field Name="Operate" Type="3" Value="24"/>
<Field Name="ActNO" Type="129" Value="807230200004969"/>
<Field Name="BillNo" Type="129" Value=""/>
<Field Name="BillSum" Type="129" Value=""/>
<Field Name="BillType" Type="129" Value=""/>
<Field Name="Ratio" Type="3" Value="0"/>
<Field Name="OperateTime" Type="135" Value="2010-01-11 13:53:08"/>
<Field Name="Remark" Type="129" Value="印鉴编号:2,备注:杨逸坤,印章类型:2,印章形状:2,印鉴卡编号:1001110998162015"/>
</Item>
<Item>
<Field Name="ClerkID" Type="129" Value="0998162"/>
<Field Name="DepartID" Type="129" Value="80723"/>
<Field Name="Operate" Type="3" Value="36"/>
<Field Name="ActNO" Type="129" Value="807230200004969"/>
<Field Name="BillNo" Type="129" Value=""/>
<Field Name="BillSum" Type="129" Value=""/>
<Field Name="BillType" Type="129" Value=""/>
<Field Name="Ratio" Type="3" Value="0"/>
<Field Name="OperateTime" Type="135" Value="2010-01-11 13:53:11"/>
<Field Name="Remark" Type="129" Value=""/>
</Item>
<Item>
<Field Name="ClerkID" Type="129" Value="0998162"/>
<Field Name="DepartID" Type="129" Value="80723"/>
<Field Name="Operate" Type="3" Value="29"/>
<Field Name="ActNO" Type="129" Value="807230200004969"/>
<Field Name="BillNo" Type="129" Value=""/>
<Field Name="BillSum" Type="129" Value=""/>
<Field Name="BillType" Type="129" Value=""/>
<Field Name="Ratio" Type="3" Value="0"/>
<Field Name="OperateTime" Type="135" Value="2010-01-11 13:56:31"/>
<Field Name="Remark" Type="129" Value="印鉴组号:1,包含印鉴:1,2,备注:all,表达式:,最小金额:0.00,最大金额:0.00,小账号:"/>
</Item>
<Item>
<Field Name="ClerkID" Type="129" Value="0998162"/>
<Field Name="DepartID" Type="129" Value="80723"/>
<Field Name="Operate" Type="3" Value="37"/>
<Field Name="ActNO" Type="129" Value="807230200004969"/>
<Field Name="BillNo" Type="129" Value=""/>
<Field Name="BillSum" Type="129" Value=""/>
<Field Name="BillType" Type="129" Value=""/>
<Field Name="Ratio" Type="3" Value="0"/>
<Field Name="OperateTime" Type="135" Value="2010-01-11 13:56:53"/>
<Field Name="Remark" Type="129" Value=""/>
</Item>
<Item>
<Field Name="ClerkID" Type="129" Value="0000009"/>
<Field Name="DepartID" Type="129" Value="80723"/>
<Field Name="Operate" Type="3" Value="101"/>
<Field Name="ActNO" Type="129" Value="807230200004969"/>
<Field Name="BillNo" Type="129" Value=""/>
<Field Name="BillSum" Type="129" Value=""/>
<Field Name="BillType" Type="129" Value=""/>
<Field Name="Ratio" Type="3" Value="0"/>
<Field Name="OperateTime" Type="135" Value="2010-01-11 14:55:53"/>
<Field Name="Remark" Type="129" Value=""/>
</Item>
<Item>
<Field Name="ClerkID" Type="129" Value="0998162"/>
<Field Name="DepartID" Type="129" Value="80723"/>
<Field Name="Operate" Type="3" Value="24"/>
<Field Name="ActNO" Type="129" Value="807230200004969"/>
<Field Name="BillNo" Type="129" Value=""/>
<Field Name="BillSum" Type="129" Value=""/>
<Field Name="BillType" Type="129" Value=""/>
<Field Name="Ratio" Type="3" Value="0"/>
<Field Name="OperateTime" Type="135" Value="2010-01-11 14:58:35"/>
<Field Name="Remark" Type="129" Value="印鉴编号:3,备注:钟中林,印章类型:2,印章形状:15,印鉴卡编号:1001110998162015"/>
</Item>
<Item>
<Field Name="ClerkID" Type="129" Value="0998162"/>
<Field Name="DepartID" Type="129" Value="80723"/>
<Field Name="Operate" Type="3" Value="29"/>
<Field Name="ActNO" Type="129" Value="807230200004969"/>
<Field Name="BillNo" Type="129" Value=""/>
<Field Name="BillSum" Type="129" Value=""/>
<Field Name="BillType" Type="129" Value=""/>
<Field Name="Ratio" Type="3" Value="0"/>
<Field Name="OperateTime" Type="135" Value="2010-01-11 14:59:34"/>
<Field Name="Remark" Type="129" Value="印鉴组号:2,包含印鉴:1,2,3,备注:all,表达式:,最小金额:0.00,最大金额:0.00,小账号:"/>
</Item>
<Item>
<Field Name="ClerkID" Type="129" Value="0998162"/>
<Field Name="DepartID" Type="129" Value="80723"/>
<Field Name="Operate" Type="3" Value="34"/>
<Field Name="ActNO" Type="129" Value="807230200004969"/>
<Field Name="BillNo" Type="129" Value=""/>
<Field Name="BillSum" Type="129" Value=""/>
<Field Name="BillType" Type="129" Value=""/>
<Field Name="Ratio" Type="3" Value="0"/>
<Field Name="OperateTime" Type="135" Value="2010-01-11 15:00:07"/>
<Field Name="Remark" Type="129" Value=""/>
</Item>
<Item>
<Field Name="ClerkID" Type="129" Value="0998162"/>
<Field Name="DepartID" Type="129" Value="80723"/>
<Field Name="Operate" Type="3" Value="29"/>
<Field Name="ActNO" Type="129" Value="807230200004969"/>
<Field Name="BillNo" Type="129" Value=""/>
<Field Name="BillSum" Type="129" Value=""/>
<Field Name="BillType" Type="129" Value=""/>
<Field Name="Ratio" Type="3" Value="0"/>
<Field Name="OperateTime" Type="135" Value="2010-01-11 15:00:13"/>
<Field Name="Remark" Type="129" Value="印鉴组号:1,包含印鉴:1,2,3,备注:,表达式:,最小金额:0.00,最大金额:0.00,小账号:"/>
</Item>
<Item>
<Field Name="ClerkID" Type="129" Value="0998162"/>
<Field Name="DepartID" Type="129" Value="80723"/>
<Field Name="Operate" Type="3" Value="37"/>
<Field Name="ActNO" Type="129" Value="807230200004969"/>
<Field Name="BillNo" Type="129" Value=""/>
<Field Name="BillSum" Type="129" Value=""/>
<Field Name="BillType" Type="129" Value=""/>
<Field Name="Ratio" Type="3" Value="0"/>
<Field Name="OperateTime" Type="135" Value="2010-01-11 15:00:27"/>
<Field Name="Remark" Type="129" Value=""/>
</Item>
<Item>
<Field Name="ClerkID" Type="129" Value="0000009"/>
<Field Name="DepartID" Type="129" Value="80723"/>
<Field Name="Operate" Type="3" Value="103"/>
<Field Name="ActNO" Type="129" Value="807230200004969"/>
<Field Name="BillNo" Type="129" Value=""/>
<Field Name="BillSum" Type="129" Value=""/>
<Field Name="BillType" Type="129" Value=""/>
<Field Name="Ratio" Type="3" Value="0"/>
<Field Name="OperateTime" Type="135" Value="2010-01-11 15:27:07"/>
<Field Name="Remark" Type="129" Value=""/>
</Item>
<Item>
<Field Name="ClerkID" Type="129" Value="0101676"/>
<Field Name="DepartID" Type="129" Value="80723"/>
<Field Name="Operate" Type="3" Value="120"/>
<Field Name="ActNO" Type="129" Value="807230200004969"/>
<Field Name="BillNo" Type="129" Value="2010031801016760006"/>
<Field Name="BillSum" Type="129" Value="24000000"/>
<Field Name="BillType" Type="129" Value="299"/>
<Field Name="Ratio" Type="3" Value="0"/>
<Field Name="OperateTime" Type="135" Value="2010-03-18 10:43:42"/>
<Field Name="Remark" Type="129" Value=""/>
</Item>
<Item>
<Field Name="ClerkID" Type="129" Value="0101676"/>
<Field Name="DepartID" Type="129" Value="80723"/>
<Field Name="Operate" Type="3" Value="126"/>
<Field Name="ActNO" Type="129" Value="807230200004969"/>
<Field Name="BillNo" Type="129" Value="2010031801016760006"/>
<Field Name="BillSum" Type="129" Value="24000000"/>
<Field Name="BillType" Type="129" Value="299"/>
<Field Name="Ratio" Type="3" Value="0"/>
<Field Name="OperateTime" Type="135" Value="2010-03-18 10:43:52"/>
<Field Name="Remark" Type="129" Value=""/>
</Item>
<Item>
<Field Name="ClerkID" Type="129" Value="0101676"/>
<Field Name="DepartID" Type="129" Value="80723"/>
<Field Name="Operate" Type="3" Value="124"/>
<Field Name="ActNO" Type="129" Value="807230200004969"/>
<Field Name="BillNo" Type="129" Value="2010031801016760006"/>
<Field Name="BillSum" Type="129" Value="24000000"/>
<Field Name="BillType" Type="129" Value="299"/>
<Field Name="Ratio" Type="3" Value="914"/>
<Field Name="OperateTime" Type="135" Value="2010-03-18 10:44:08"/>
<Field Name="Remark" Type="129" Value=""/>
</Item>
<Item>
<Field Name="ClerkID" Type="129" Value="0101676"/>
<Field Name="DepartID" Type="129" Value="80723"/>
<Field Name="Operate" Type="3" Value="124"/>
<Field Name="ActNO" Type="129" Value="807230200004969"/>
<Field Name="BillNo" Type="129" Value="2010031801016760006"/>
<Field Name="BillSum" Type="129" Value="24000000"/>
<Field Name="BillType" Type="129" Value="299"/>
<Field Name="Ratio" Type="3" Value="100"/>
<Field Name="OperateTime" Type="135" Value="2010-03-18 10:44:27"/>
<Field Name="Remark" Type="129" Value=""/>
</Item>
</Table>
</DataExport>
代码不会写…………
内容过长……略去4段<Item></Item> --------------------编程问答-------------------- // 装载我们提交的XML文件到DataSet
dataSetXml.ReadXml(HttpContext.Current.Server.MapPath(textBoxXml.Text));
// 得到DataSet中的第一个表
dataTableXml = dataSetXml.Tables[0];
// 生成表名
tableName = textBoxXml.Text.Substring(0,textBoxXml.Text.Length -4);
// 遍历DataTable中的Rows
foreach(DataRow dr in dataTableXml.Rows)
{
string sqlCmd = "insert into [" + tableName + "] (";
// 遍历Datatable的列
for(int i = 0;i < dataTableXml.Columns.Count;i++)
{
// 添加column name
sqlCmd = sqlCmd + dataTableXml.Columns[i].ColumnName.ToString() + ",";
}
sqlCmd = sqlCmd.Substring(0,sqlCmd.Length - 1) + ") values (";
// 遍历 DataTable columns
for(int x = 0;x < dataTableXml.Columns.Count;x++)
{
// 添加column value到row
sqlCmd = sqlCmd + "'" + dr[x].ToString().Replace("'","''") + "',";
}
sqlCmd = sqlCmd.Substring(0,sqlCmd.Length - 1) + ");";
OleDbCommand oledbCmd = new OleDbCommand(sqlCmd,oledbConn);
oledbCmd.ExecuteNonQuery();
} --------------------编程问答-------------------- 坐等高手,·· --------------------编程问答--------------------
谢谢!! 我正在敲一遍试试! --------------------编程问答-------------------- 直接用LINQ搞定不就好了? --------------------编程问答--------------------
方便吗?怎么使用呢? 我QQ 270435772,
高手最好能加我QQ麻烦传授一下· --------------------编程问答-------------------- 你的Item可以建一个Model类的
然后用XDocument doc = XDocument.Load(xmlPath);载入你的xml文件~~
剩下的直接用linq分析成一个IList就好了哇~
有了IList。。插入还是问题? --------------------编程问答-------------------- 给个官方的例子吧~~举一反三~~
public void XLinq23() {
string xml = "<order >" +
"<item price='150'>Motor</item>" +
"<item price='50'>Cable</item>" +
"<item price='50'>Modem</item>" +
"<item price='250'>Monitor</item>" +
"<item price='10'>Mouse</item>" +
"</order>";
XElement order = XElement.Parse(xml);
var query =
from
i in order.Elements("item")
where
(int)i.Attribute("price") > 100
select i;
foreach (var result in query)
Console.WriteLine("Expensive Item {0} costs {1}",
(string)result,
(string)result.Attribute("price"));
}
--------------------编程问答-------------------- 技术不扎实··~有些东西还是不会写哇········ --------------------编程问答--------------------
不会Linq,那些中文是? --------------------编程问答--------------------
谁都是从不会到会的。。。 --------------------编程问答--------------------
我想请教你些问题诶……我是把"<item price='150'>Motor</item>" 可以换成我的XML里的标签属性吧?
--------------------编程问答-------------------- 你的XML结构不用改呀,LINQ的语句改改就好了,无非就是select一下就好了~ --------------------编程问答-------------------- 等下,我给你完整代码。 --------------------编程问答--------------------
激动中…… --------------------编程问答--------------------
这个LINQ我正在百度看诶·· --------------------编程问答-------------------- 唯一的不好是,你的节点名都是Field ,这样就不好用节点去赋值,得一个个名字去判断,比较费时。 --------------------编程问答--------------------
--------------------编程问答-------------------- 17楼的代码,你把
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
string xml = "<DataExport>" +
"<Table TableName='SEALLOG'>" +
"<Item>" +
"<Field Name=\"ClerkID\" Type=\"129\" Value=\"0998162\"/>" +
"<Field Name=\"DepartID\" Type=\"129\" Value=\"80723\"/>" +
"<Field Name=\"Operate\" Type=\"3\" Value=\"33\"/>" +
"<Field Name=\"ActNO\" Type=\"129\" Value=\"807230200004969\"/>" +
"<Field Name=\"BillNo\" Type=\"129\" Value=\"\"/>" +
"<Field Name=\"BillSum\" Type=\"129\" Value=\"\"/>" +
"<Field Name=\"BillType\" Type=\"129\" Value=\"\"/>" +
"<Field Name=\"Ratio\" Type=\"3\" Value=\"0\"/>" +
"<Field Name=\"OperateTime\" Type=\"135\" Value=\"2010-01-11 13:51:09\"/>" +
"<Field Name=\"Remark\" Type=\"129\" Value=\"添加印鉴卡:1001110998162015\"/>" +
"</Item>" +
"<Item>" +
"<Field Name=\"ClerkID\" Type=\"129\" Value=\"0998162\"/>" +
"<Field Name=\"DepartID\" Type=\"129\" Value=\"80723\"/>" +
"<Field Name=\"Operate\" Type=\"3\" Value=\"24\"/>" +
"<Field Name=\"ActNO\" Type=\"129\" Value=\"807230200004969\"/>" +
"<Field Name=\"BillNo\" Type=\"129\" Value=\"\"/>" +
"<Field Name=\"BillSum\" Type=\"129\" Value=\"\"/>" +
"<Field Name=\"BillType\" Type=\"129\" Value=\"\"/>" +
"<Field Name=\"Ratio\" Type=\"3\" Value=\"0\"/>" +
"<Field Name=\"OperateTime\" Type=\"135\" Value=\"2010-01-11 13:52:02\"/>" +
"<Field Name=\"Remark\" Type=\"129\" Value=\"印鉴编号:1,备注:财务章,印章类型:1,印章形状:2,印鉴卡编号:1001110998162015\"/>" +
"</Item>" +
"</Table>" +
"</DataExport>";
XElement item = XElement.Parse(xml);
var query =
from
i in item.Descendants("Item")
select i;
int j = 0;
foreach (var result in query)
{
Console.WriteLine("Item {0}", j);
foreach (var field in result.Descendants("Field"))
{
Console.WriteLine("name {0},type {1}", field.Attribute("Name").Value, field.Attribute("Type").Value);
}
}
Console.Read();
}
}
}
XDocument doc = XDocument.Load(xmlPath);
换成
XElement item = XElement.Parse(xml);
就好了。。。。 --------------------编程问答--------------------
public static List<Control> LoadxmlNode(string filename, string parentpath)--------------------编程问答--------------------
{
XElement content = XElement.Load(filename);
var values = from el in content.Descendants(parentpath).Elements()
select el;
List< Control> Lc = new List<Control>();
Type ty = typeof(Control);
Control obj = null;
values.ToList<XElement>().ForEach(ex =>
{
obj = new Control();
ty.GetProperties().ToList<PropertyInfo>().ForEach(p =>
{
if(p.Name==ex.Attribute(p.Name).Name)
p.SetValue(obj, ex.Attribute(p.Name).Value,null);
});
Lc.Add(obj);
});
return Lc;
}
//数据源结构
public class Control
{
public string Name { get; set; }
public string Type{ get; set; }
public string Value { get; set; }
}
LinqToXml.LoadxmlNode("control.xml", "Item");调用方式 --------------------编程问答-------------------- 然后你可以处理这个实体数组了,或者你直接在这里拼接sql语句,不过考虑耦合性,这里做的工作最好就是这个。 --------------------编程问答--------------------
你好,感谢耐心指教。我把foreach里的输出加上了Value{2},用Attribute也得到Value的值,并显示出来了。
可是。。显示出来的只是前两段Item里的节点。
我是要把这所有的Item字段导入到SealLog这张表里呀· --------------------编程问答-------------------- 我嫌麻烦没写那么多。。。。你自己加上后边的字段就好了呀。。。
--------------------编程问答-------------------- 这个是有多少就循环多少的,我懒得都加上了。。。。
Console.WriteLine("name {0},type {1},value {2}",
field.Attribute("Name").Value,
field.Attribute("Type").Value,
field.Attribute("Value").Value);
你直接用XDocument doc = XDocument.Load(xmlPath);把XML文件读进来就好了,后边的不用改 --------------------编程问答--------------------
对呀,我就是这么写的,可我要把他导入到SQL表里去……我也没看到有啊·· --------------------编程问答--------------------
--------------------编程问答--------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
XDocument doc = XDocument.Load("d:\data.xml");
var query =
from
i in doc.Descendants("Item")
select i;
int j = 0;
foreach (var result in query)
{
Console.WriteLine("Item {0}", j);
foreach (var field in result.Descendants("Field"))
{
Console.WriteLine("name {0},type {1}", field.Attribute("Name").Value, field.Attribute("Type").Value);
}
}
Console.Read();
}
}
}
晕死。。。你把这个Console.WriteLine变成一个insert方法不就好了?
难道值都得到了,插入还不会? --------------------编程问答--------------------
public static int Insertvalues(List<Control> co)--------------------编程问答--------------------
{
Type ty = typeof(Control);
StringBuilder sb = new StringBuilder();
sb.Append(string.Format("insert into {0} (" ,ty.Name));
Control obj = null;
co.ForEach(c=>
{
obj= new Control();
Dictionary<string,string> dic=new Dictionary<string,string>();
ty.GetProperties().ToList<PropertyInfo>().ForEach(p=>
{
dic[p.Name] = p.GetValue(c, null).ToString();
});
sb.Append(string.Concat(string.Join(", ",dic.Keys.ToArray()),")"," Values("));
sb.Append(string.Concat(string.Join(", ",dic.Values.ToArray()),")"));
///执行sql语句
});
return 0;//返回影响行数,执行那一块我不就不做了,你自己写写吧。
}
我不敢问了。 --------------------编程问答--------------------
谢谢!~ --------------------编程问答--------------------
问倒是不怕,自己也要动动脑子的,要不,拿着代码没得进步~ --------------------编程问答--------------------
谢了!麻烦了!我会继续努力的 --------------------编程问答--------------------
补充:.NET技术 , C#