Partager via

Case Study: Import data from XML to SQL (Part-2)

  • Article

Hello again…

In Case Study: Import data from XML to SQL (Part-1) we tried to save XML data to SQL using c# .net code

In part one we took care of creating tables, columns, assigning appropriate data type, creating xsd file at run time and bulk insert data in SQL

 

In this part we are going to take care of

1)      Duplicate nodes in XML file

2)      Creating new XML file with replaced node names

3)      Enumerate through all XML files in given folder

4)      Get how many rows are there in database and decide next ID value accordingly

 

-> Resolve Duplicate nodes in XML file by renaming nodes.

If you have multiple nodes with same name then we will get error

{"A column named '<column name>' already belongs to this DataTable: cannot set a nested table name to the same name."}

 

So to resolve this we have to rename this node in XML file and append name of it’s parent to actual nod name, now some time we have to deeper in hierarchy as parent along with child might also not be unique and in such case we have to go one more level deep.

 

So code will look something like,

 

 var nodes_dates = xd.Descendants("Dates"); //find all nodes with name Dates
 foreach (XElement n in nodes_dates)
 { 
 n.Name = n.Parent.Parent.Name + "_" + n.Parent.Name + "_" + n.Name; //change name with parent_parent_node (as it is unique and parent_node is not unique)
 }
 var nodes = xd.Descendants("Contacts"); //find all nodes with name contacts
 foreach (XElement n in nodes)
 {
 n.Name = n.Parent.Parent.Parent.Parent.Name + "_" + n.Parent.Parent.Parent.Name + "_" + n.Parent.Parent.Name + "_" + n.Parent.Name + "_" + n.Name; //change name with parent_parent_node (as it is unique and parent_node is not unique)
 }
 xr = xd.CreateReader(); //Update xmlreader

So here name of node will get changed so that we don’t have any duplicate XML nodes. Only trick is finding which all nodes are duplicate. For that we have to rely on errors, so we have to test code enough number of times to make sure we have handled almost all duplicate nodes.

 

 

-> Enumerating through folder to process all XML files

This is easiest part, it just needs one for each loop

 

 foreach (var file in Directory.EnumerateFiles(@"D:\XML\FAC_SSIS_Identities\", "*.xml"))

 

Then you can use this file variable wherever you want to mention file name along with path.

 File variable will have complete path of XML files.

 

-> Find number of rows in table and decide next identity value

With enumeration comes next problem reseeding identity value.

When you process new XML file identity value gets reseeded to 1 so you have to change that so that it gets right identity value and it does not create any duplicates.

For this you have to go through all tables and find how many rows are already present and then change default value of Dataset to this new values.

 

Code for that will look something like,

 

 foreach (DataRelation dr in ds1.Relations)
 {
 SqlConnection conn = new SqlConnection("Server=dil\\denali;Initial Catalog=Ken_XML_data;Trusted_Connection=True;");
 conn.Open();
 SqlCommand cmd = new SqlCommand("SELECT * FROM sysobjects where name = '" + dr.ParentTable.TableName + "'", conn);
 var val = cmd.ExecuteScalar();
 if (val != null)
 {
 cmd.CommandText = "select max([" + dr.ParentColumns[0].ColumnName + "]) from [" + dr.ParentTable.TableName + "]";
 long max = 0;
 try
 {
 long.TryParse(cmd.ExecuteScalar().ToString(), out max);
 }catch (Exception ex) { max = 0; }
 dr.ParentColumns[0].AutoIncrementSeed = max + 1;
 }
 cmd.CommandText = "SELECT * FROM sysobjects where name = '" + dr.ChildTable.TableName + "'";
 val = cmd.ExecuteScalar();
 if (val != null)
 {
 cmd.CommandText = "select max([" + dr.ChildColumns[0].ColumnName + "]) from [" + dr.ChildTable.TableName + "]";
 long max = 0;
 try
 {
 long.TryParse(cmd.ExecuteScalar().ToString(), out max);
 }catch (Exception ex) { max = 0; }
 dr.ChildColumns[0].AutoIncrementSeed = max + 1;
 }
 conn.Close();
 }
 //constraints will be ignored as it was giving some error
 ds1.EnforceConstraints = false;

If you look at code carefully you will see that I’m enumerating through each relation and running query to find max value of parent table and child table and then reseeding table identity column with this max value.

This is as dynamic as possible so it can handle as many relations as we have.

 

So with above changes we should be able to take care of most of the difficulties that we face with complex XML files.

 

With this, this case study ends…

 

I hope this is helpful.

 

Link for solution file:

https://sdrv.ms/17vlQLL

 

It does not have schema and xml files as XML file has some sensitive data and replacing all will take good amount of time.

 

Let me know if you have any question.

Comments

  • Anonymous
    October 30, 2013
    I was searching this on GoogleBing and unfortunately did not find much interesting hits. (I can say

  • Anonymous
    November 26, 2013
    The comment has been removed

  • Anonymous
    November 26, 2013
    It does work for me. (I have not logged into skydrive) Could you try again and let me know? If doesn't work give your email id I will email that to you.

  • Anonymous
    November 27, 2013
    hmm, now getting "The proxy server is refusing connections" error... might be something my end, will check

  • Anonymous
    November 27, 2013
    looks like,,, check that.. if does not work send me an email and I will forward that to you.