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) 
 Database db = new Database(server, "MyDatabaseName"); 
if (db.Tables["TechOpsProjectTracker"] != null) 
    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); 
DataType dt2 = new DataType(SqlDataType.NVarChar, 50); 
Column Column2 = new Column(TechOpsProjectTracker, "Project Name", dt2); 

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:
   <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' />


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);
// 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.


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