importing data from an xml into sql server 2005
-
hi all. i have an XML file say Data.xml & i have to import data from this xml file into sql server 2005. i havr only this not XSD & TXD file. can any help me?
-
hi all. i have an XML file say Data.xml & i have to import data from this xml file into sql server 2005. i havr only this not XSD & TXD file. can any help me?
hi,I have write a similar code .I hope my code can help you! using System; using System.IO; using System.Xml; using System.Collections; using System.Data.SqlClient; using System.Data; using System.Configuration; namespace InsertSql { /// <summary> /// Class1 ?????? /// </summary> class Class1 { private static SqlConnection myConnection; public static ArrayList myfilelist = new ArrayList(); //public static ArrayList DataType = new ArrayList(); public static int insercount; public static int lostnum; public static string TableName = ConfigurationSettings.AppSettings["TableName"].ToString(); /// <summary> /// ?????????? /// </summary> [STAThread] static void Main(string[] args) { string filepath; filepath = Console.ReadLine(); //filepath = @"f:\result\test\"; DirectoryInfo di = new DirectoryInfo (@filepath); CheckDir(di); Console.WriteLine("????????,?????????y/n"); string flag =Console.ReadLine(); if(flag.ToLower().Equals("y")) { //GetArrayList(DataType,string appname) IEnumerator filelist = myfilelist.GetEnumerator(); while(filelist.MoveNext()) { ReadXml(filelist.Current.ToString()); } Console.ReadLine(); } Console.WriteLine("????????,??????"+insercount+"???,????"+lostnum+"???!"); Console.ReadLine(); } public static ArrayList GetArrayList(ArrayList arrname,string appname) { string Datafile = ConfigurationSettings.AppSettings[appname].ToString(); string[] temp = Datafile.Split('|'); for(int i=0;i<temp.Length;i++) { arrname.Add(temp[i].ToString()); } return arrname; } public static void ReadXml(string filepath) { ArrayList XmlNodeNamelist = new ArrayList(); XmlDocument doc = new XmlDocument(); try { doc.Load(filepath); if(doc.HasChildNodes) { XmlNodeList mylist = doc.GetElementsByTagName("item"); GetArrayList(XmlNodeNamelist,"XmlNodeName"); string[] datalist = new string[XmlNodeNamelist.Count]; foreach(XmlNode personElement in mylist) { for(int i=0;i<XmlNodeNamelist.Count;i++) { if(XmlNodeNamelist[i].ToString()==personElement.Attributes["name"].Value) { datalist[i]=personElement.FirstChild.Value; } } } InsertDate(datalist,filepath); } } catch(Exception xmlerr) { Console.WriteLine(xmlerr); } } private static void InsertDate(string[] dat
-
hi,I have write a similar code .I hope my code can help you! using System; using System.IO; using System.Xml; using System.Collections; using System.Data.SqlClient; using System.Data; using System.Configuration; namespace InsertSql { /// <summary> /// Class1 ?????? /// </summary> class Class1 { private static SqlConnection myConnection; public static ArrayList myfilelist = new ArrayList(); //public static ArrayList DataType = new ArrayList(); public static int insercount; public static int lostnum; public static string TableName = ConfigurationSettings.AppSettings["TableName"].ToString(); /// <summary> /// ?????????? /// </summary> [STAThread] static void Main(string[] args) { string filepath; filepath = Console.ReadLine(); //filepath = @"f:\result\test\"; DirectoryInfo di = new DirectoryInfo (@filepath); CheckDir(di); Console.WriteLine("????????,?????????y/n"); string flag =Console.ReadLine(); if(flag.ToLower().Equals("y")) { //GetArrayList(DataType,string appname) IEnumerator filelist = myfilelist.GetEnumerator(); while(filelist.MoveNext()) { ReadXml(filelist.Current.ToString()); } Console.ReadLine(); } Console.WriteLine("????????,??????"+insercount+"???,????"+lostnum+"???!"); Console.ReadLine(); } public static ArrayList GetArrayList(ArrayList arrname,string appname) { string Datafile = ConfigurationSettings.AppSettings[appname].ToString(); string[] temp = Datafile.Split('|'); for(int i=0;i<temp.Length;i++) { arrname.Add(temp[i].ToString()); } return arrname; } public static void ReadXml(string filepath) { ArrayList XmlNodeNamelist = new ArrayList(); XmlDocument doc = new XmlDocument(); try { doc.Load(filepath); if(doc.HasChildNodes) { XmlNodeList mylist = doc.GetElementsByTagName("item"); GetArrayList(XmlNodeNamelist,"XmlNodeName"); string[] datalist = new string[XmlNodeNamelist.Count]; foreach(XmlNode personElement in mylist) { for(int i=0;i<XmlNodeNamelist.Count;i++) { if(XmlNodeNamelist[i].ToString()==personElement.Attributes["name"].Value) { datalist[i]=personElement.FirstChild.Value; } } } InsertDate(datalist,filepath); } } catch(Exception xmlerr) { Console.WriteLine(xmlerr); } } private static void InsertDate(string[] dat
Your code is unnecessarily complicated and inefficient. SQL Server has had the ability to directly handle XML data since version 2000. This article[^]explains how to use the
OPENXMLclause to pass XML data to a stored procedure.Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
-
Your code is unnecessarily complicated and inefficient. SQL Server has had the ability to directly handle XML data since version 2000. This article[^]explains how to use the
OPENXMLclause to pass XML data to a stored procedure.Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
yeah,I know that I did not optimize my code,but I do not think that it is complicated and unnecessarily . Thanks for your advice ,can you tell me ,if I want use the openxml ,since version 2000,all have this function itself? if you say yeah,I would be very happay, opposition,if need me install it,I do not think so! and if anybody need my help,please give me message
my english is very bad!
-
yeah,I know that I did not optimize my code,but I do not think that it is complicated and unnecessarily . Thanks for your advice ,can you tell me ,if I want use the openxml ,since version 2000,all have this function itself? if you say yeah,I would be very happay, opposition,if need me install it,I do not think so! and if anybody need my help,please give me message
my english is very bad!
suyuan1984 wrote:
but I do not think that it is complicated and unnecessarily
You're wrong. It is. By using the
OPENXMLclause, you could have cut out most of your code. It's a core part of the T-SQL language. Read the documentation to learn how to use it.Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
-
Your code is unnecessarily complicated and inefficient. SQL Server has had the ability to directly handle XML data since version 2000. This article[^]explains how to use the
OPENXMLclause to pass XML data to a stored procedure.Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
pmarfleet wrote:
Your code is unnecessarily complicated and inefficient
Did you actually look through it?
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon