Insert Multiple Row In Database Using XML From A ASP.Net Project



Published On Thursday June 4, 2020 Reading Time: < 1 minute

For inserting multiple row one of the convenient method is using xml we need to start by creating a xml string in c#:

string[,] values= new string[2,2]
{
   {"code","description"},
   {"code","description"}
};
var xmlString = new StringBuilder();
xmlString.AppendLine("&amp;lt;root&amp;gt;");
foreach (var item in values)
{
  xmlString.AppendLine("<row" +
                        " Code=\"" + item.code + "\"" +
                        " Description=\"" + item.description+ "\"" +
                        " />");
}
BankLists.AppendLine("</root>");

Now once the string is created we need to pass the data to sql server via any method so below is the code snippets for sql server end

declare @list XML = NULL
INSERT INTO dbo.table( Value, Description)
 SELECT
   b.value('@Code','VARCHAR(200)'),
   b.value('@Description','VARCHAR(200)')
 FROM @list.nodes('/root/row') AS lists(b);

So, this is how we can insert multiple values using xml in sql server.

0 0 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x