Hi,
My requirement is to read a XML string, Manipulate and store it in MS SQL Server.
XML:
<?xml version="1.0" encoding="UTF-8" ?>
<Data>
<Table1>
<Col1>Value1</Col1>
<Col2>Value2</Col2>
<Col3>Value3</Col3>
</Table1>
<Table1>
<Col1>Value1a</Col1>
<Col2>Value2a</Col2>
<Col3>Value3a</Col3>
</Table1>
...
...
...
...
</Data>
its available in string
string sXML = "<?xml version=\"1.0\" encoding=\"UTF-8\" ?><Data><Table><Col1>Value1</Col1><Col2>Value2</Col2><Col3>Value3</Col3></Table><Table><Col1>Value1a</Col1><Col2>Value2a</Col2><Col3>Value3a</Col3></Table></Data>"
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.