将xml中的数据导入到数据库

最简单的读取，把xml的一级子元素当做一条记录插入数据库



代码
static void InputDict() { var conn = new Connection(SharedMembers.localConnectString); XmlReaderSettings settings = new XmlReaderSettings(); settings.IgnoreWhitespace = true; using (XmlReader reader = XmlReader.Create("简明英汉词典.xml")) { reader.MoveToContent(); reader.Read(); var i = 0; while (reader.IsStartElement()) { i++; var cmd = new Command("INSERT INTO [dict].[dbo].[simplec]([ck])VALUES (@ck)"); cmd.AddParameter("@ck",System.Data.SqlDbType.Xml,false,reader.ReadOuterXml()); conn.Execute(cmd); Console.WriteLine("插入第{0}行数据",i); }; } }



将上一例中插入的数据记录读取出来，经分析后拆分成dc,js保存到另一个表中。



代码
static void parsedict() { using (var connection = new SqlConnection(SharedMembers.localConnectString)) { using (var cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT [ck] FROM [dict].[dbo].[simplec]"; connection.Open(); var rdr = cmd.ExecuteReader(); while (rdr.Read()) { var ck = (string)rdr["ck"]; var doc = new XmlDocument(); doc.LoadXml(ck); var Navigator = doc.CreateNavigator(); var dc = Navigator.SelectSingleNode("/CK/DC").Value; Navigator.MoveToNext(); string js = Navigator.SelectSingleNode("/CK/JS").OuterXml; using (var connection2 = new SqlConnection(SharedMembers.localConnectString)) { using (var inscmd = connection2.CreateCommand()) { inscmd.CommandType = CommandType.Text; inscmd.CommandText = "INSERT INTO [dict].[dbo].[jmyh2]([dc],[js])VALUES(@DC,@JS)"; var pdc = inscmd.CreateParameter(); pdc.ParameterName = "@DC"; pdc.SqlDbType = SqlDbType.NVarChar; pdc.Value = dc; inscmd.Parameters.Add(pdc); var pjs = inscmd.CreateParameter(); pjs.ParameterName = "@JS"; pjs.SqlDbType = SqlDbType.Xml ; pjs.Value = js; inscmd.Parameters.Add(pjs); connection2.Open(); inscmd.ExecuteNonQuery(); Console.WriteLine(dc); } } } } } }



事实上，可以直接的解析xml，再插入到数据库表。这里使用了xpath技术。



代码
static void InputDict2() { var conn = new Connection(SharedMembers.localConnectString); XmlReaderSettings settings = new XmlReaderSettings(); settings.IgnoreWhitespace = true; const string dictname = "OxfordCollocations"; using (XmlReader reader = XmlReader.Create(dictname+".xml")) { reader.MoveToContent(); reader.Read(); long i = 0; while (reader.IsStartElement()) { var ck = new XmlDocument(); ck.LoadXml(reader.ReadOuterXml()); var Navigator = ck.CreateNavigator(); var dc = Navigator.SelectSingleNode("/CK/DC").Value; Navigator.MoveToNext(); var js = Navigator.SelectSingleNode("/CK/JS").OuterXml; i++; var cmd = new Command("INSERT INTO dict.dbo."+dictname+"(id, dc,js)VALUES(@id, @dc,@js)"); cmd.AddParameter("@id", System.Data.SqlDbType.BigInt, false, i); cmd.AddParameter("@dc", System.Data.SqlDbType.NVarChar, false, dc); cmd.AddParameter("@js", System.Data.SqlDbType.Xml, false, js); conn.Execute(cmd); if (i % 1000==0) Console.WriteLine("插入第{0:#,##0,000}行数据", i); }; } }