Read XML string, Manipulate, Store in Dataset & SqlBulkCopy

Murugesan K S

13y2.2kVisual Basic .NET

Hi,

My requirement is to read a XML string, Manipulate and store it in MS SQL Server.

XML:

Value1

Value2

Value3

Value1a

Value2a

Value3a

...

its available in string

string sXML = "Value1Value2Value3

Value1aValue2aValue3a

MS SQL Server table structure and xml mapping

MyTable - XML

Column1->Col1

Column2->Col2

Column3->Col3

Column4->Col1+Col2

Column5->Col3+200

Column6-> {current system date}

Column-> {Default constraint stores '0' always}

Number of rows inserted will be max of 100 rows at a time

current clients accessing this app and passing XML would be around 20

Frequency of calling is 1 min

I use .NET 4.0, C# and MS SQL Server 2008

My Approach is read, xml file, manipulate it, store in dataset/datatable and do the bulk copy.

My Question is :

1. In this approach, How to manipulate and store it in dataset/datatable

1. Is there any better way to do it?

Thank you.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

13y ago
your can use xmlSerializer to add, modify and store XML data into dataset or table,see the bellow link i have use xmlserializer class to add modify and display data of xmal file in screen using console application. in the linked blogs i have displayed xml data in the screen but you can save this data into dataset or directly insert into database.

http://www.c-sharpcorner.com/Blogs/11481/serialize-objects-in-custom-xml-format-using-xml-serializati.aspx
13y ago
Hi,
Try the following code.

* First load the xml into dataset
* Extract the datatable from the dataset
* Add the required or the computational columns. You can use expression property to aggregate / sum / to create computed columns.
* Use the default value property for the datetime and for the column 7.
* Send the datatable to sqlbulk load class to insert into database.

DataSet ds = new DataSet();
ds.ReadXml(@"c:\data.xml");

System.Data.DataTable table = ds.Tables[0];

DataColumn col4 = new DataColumn();
col4.DataType = "System.String".GetType();
col4.Expression = "col1 + col2";
col4.ColumnName = "Col4";

DataColumn col5 = new DataColumn();
col5.DataType = "System.String".GetType();
col5.Expression = "col3 + 200";
col5.ColumnName = "Col5";

DataColumn col6 = new DataColumn();
col6.DataType = "System.DateTime".GetType();
col6.DefaultValue = System.DateTime.Now;
col6.ColumnName = "Col6";

DataColumn col7 = new DataColumn();
col7.DataType = "System.Int32".GetType();
col7.DefaultValue = 0;
col7.ColumnName = "Col7";

table.Columns.Add(col4);
table.Columns.Add(col5);
table.Columns.Add(col6);
table.Columns.Add(col7);
dataGridView1.DataSource = table;

You can use SQL bulk load to insert the records into sql server. That will be faster.
Please let me know, if you need further information.