Avatar of dearnemo

asked on 

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

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");

Open in new window

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

Open in new window

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


Open in new window

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)";

Open in new window

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.


Open in new window

C#.NET ProgrammingMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Christopher Kile
Avatar of Christopher Kile
Christopher Kile
Flag of United States of America image

Well, SQL Server doesn't accept arrays from ADO.NET as input.  You'll have to insert each record one at a time, or pass your "array" as a delimited string.  What are you planning to store, what's the table(s) you're planning to store it in?
Avatar of dearnemo


Thanks for your suggestion but I have found a solution to my problem. Using table value parameter in C# and Sql SErver, you can  achieve it. Thanks.
Avatar of Christopher Kile
Christopher Kile
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
LOL I think you should get the points, but I don't think they ever give them to the asker.  Thanks very much.
.NET Programming
.NET Programming

The .NET Framework is not specific to any one programming language; rather, it includes a library of functions that allows developers to rapidly build applications. Several supported languages include C#, VB.NET, C++ or ASP.NET.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo