troubleshooting Question

C# :INSERT xml attribute values in a SQL table?

Avatar of dearnemo
dearnemo asked on
.NET ProgrammingC#Microsoft SQL Server 2008
5 Comments1 Solution295 ViewsLast Modified:
1.) I have database and table created using SMO in C# console application.

Server server = new Server("pwd12"); 
 if (server.Databases["MyDatabaseName"] != null) 
     server.Databases["MyDatabaseName"].Drop(); 
 Database db = new Database(server, "MyDatabaseName"); 
     db.Create(); 
 
if (db.Tables["TechOpsProjectTracker"] != null) 
    db.Tables["TechOpsProjectTracker"].Drop(); 
    Table TechOpsProjectTracker = new Table(db, "TechOpsProjectTracker");

2.) Using SMO, created columns.For simplicity I just showed two columns here.

DataType dt = new DataType(SqlDataType.Int); 
Column idColumn = new Column(TechOpsProjectTracker, "ID", dt); 
TechOpsProjectTracker.Columns.Add(idColumn); 
 
DataType dt2 = new DataType(SqlDataType.NVarChar, 50); 
Column Column2 = new Column(TechOpsProjectTracker, "Project Name", dt2); 
TechOpsProjectTracker.Columns.Add(Column2); 

I m using the SharePoint service using a web reference to access a SharePoint 2010 list. I have a C# console application that returns SharePoint list data in the form of xml.


3. small fraction of xml returned by my code:
rs:data>
   <z:row ows_ID='360' ows_LinkTitle='GEI Survey data to Sharepoint' ows_Project_x0020_Priority='0' ows_AssignedTo='615;#Jeremy, Ron' ows_Status='In Progress' ows_Priority='(2) Normal' ows_DueDate='2012-04-27 00:00:00' ows_PercentComplete='0.700000000000000' ows_Modified='2012-04-30 10:44:15' ows_Alignment='TSS Delivery Mgmt' ows_SME='44;#Lewis, Clark' />

   <z:row ows_ID='378' ows_LinkTitle='Create back end and environment to support User demographic reporting' ows_Project_x0020_Priority='0' ows_AssignedTo='615;#Sam, Johns' ows_Status='In Progress' ows_Priority='(2) Normal' ows_DueDate='2012-05-11 00:00:00' ows_PercentComplete='0.800000000000000' ows_Modified='2012-05-09 13:50:17' ows_Alignment='Team Internal' ows_SME='7;#CORP\sscer;#9;#CORP\vreer' />

   <z:row ows_ID='249' ows_LinkTitle='Training Material to Muti Media' ows_AssignedTo='620;#Jenkins, Kristen' ows_Status='Not Started' ows_Priority='(2) Normal' ows_DueDate='2012-08-10 00:00:00' ows_PercentComplete='0' ows_Modified='2012-05-16 11:20:29' ows_Alignment='Diver Support' ows_SME='1;#CORP\vsswer;#7;#CORP\adder' />

</rs:data>

4.)I need to fill in my table with the values of the attributes returned by the above xml

string connectionString = "Server=pwd12;Database=MyDatabaseName;Trusted_Connection=True;";
           
            System.Data.SqlClient.SqlConnection sqlConnection1 =
            new System.Data.SqlClient.SqlConnection(connectionString);

            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = "INSERT TechOpsProjectTracker (ID,[Project Name]) VALUES (@ID, @ProjectName)";

5.)What I m doing is in the code below:

foreach (System.Xml.XmlNode node in nodes)
            {
                if (node.Name == "rs:data")
                {
                    for (int i = 0; i < node.ChildNodes.Count; i++)
                    {
                        if (node.ChildNodes[i].Name == "z:row")
                        {
			    
                            //Console.Write(node.ChildNodes[i].Attributes                             ["ows_Title"].Value);
                            //Console.Write(node.ChildNodes[i].Attributes["ows_ID"].Value);
			
// I want to be able to store the values of attributes ["ows_ID"] and ["ows_Title"] into some sort of arrays and be able to pass them as parameters while inserting data to the table TechOpsProjectTracker.

//Please let me know how is it possible.

                        }
                    }
                }
            } 

TIA!
ASKER CERTIFIED SOLUTION
Christopher Kile
Senior Software Analyst

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros