How to read data from XML String and insert in to table in SQL Server


In this Article, i will explain you , how to insert the below xml string into SQL Server Database table.


<Customers>

  <customer>

    <ID>111589</ID>

    <FirstName>name1</FirstName>

    <LastName>Lname1</LastName>

    <Company>ABC</Company>

  </customer>

  <customer>

    <ID>12345</ID>

    <FirstName>name2</FirstName>

    <LastName>Lname2</LastName>

    <Company>ABC</Company>

  </customer>

  <customer>

    <ID>14567</ID>

    <FirstName>name3</FirstName>

    <LastName>Lname3</LastName>

    <Company>DEF</Company>

  </customer>

</Customers>



Step1. Create a Stored procedure in Sql server which takes one input parameter and returns “Success” /”Failure”

Create PROCEDURE [dbo].[SP_Insert_MultipleRows] (
 @xmlData XML ,
 @retValue varchar(20) OUTPUT
)

AS
BEGIN
SET @retValue='Failed';

 

INSERT INTO  [Employee](
[id],
[firstName],
[lastName],
[company]
)

SELECT
COALESCE([Table].[Column].value('ID[1]', 'int'),0) as 'ID',
[Table].[Column].value('FirstName [1]', 'varchar(20)') as ' FirstName ',
[Table].[Column].value(' LastName[1]', 'varchar(20)') as ' LastName',
[Table].[Column].value(' Company [1]', 'varchar(50)') as ' Company'

 FROM @xmlData.nodes('/ Customers / customer') as [Table]([Column])
IF(@@ROWCOUNT > 0 )
  SET @retValue='SUCCESS';


END




Step2.Execute the procedure(F5)

Step3. Testing - Execute the above stored procedure by passing the xml string

Declare @retValue1 varchar(50);
Declare @XmlStr XML;
SET @XmlStr='<Customers>
 <customer>
    <ID>111589</ID>
    <FirstName>name1</FirstName>
    <LastName>Lname1</LastName>
    <Company>ABC</Company>
  </customer>
  <customer>
    <ID>12345</ID>
    <FirstName>name2</FirstName>
    <LastName>Lname2</LastName>
    <Company>ABC</Company>
  </customer>
  <customer>
    <ID>14567</ID>
    <FirstName>name3</FirstName>
    <LastName>Lname3</LastName>
    <Company>DEF</Company>
  </customer>
</Customers>';

EXEC [SP_Insert_MultipleRows] @xmlData=@XmlStr,@retValue=@retValue1 OUTPUT
print @retValue1



Output


The output will be in tabular format as below
Execute below command in sql server database

Select * from Employee


Insert xml into Database table sql

Zooming in Winforms Webbrowser Control in .NET
Filtering Datatable with Select Conditions and Sorting in C#.NET
Hiding a property from displaying in Datagridview in C#.NET
Logic to detect if Application is started from Citrix/Terminal Server/PC in C#
Winforms Application.DoEvents Method
Modify XML file with tagname in c#.net
Read Machine IP Address in C#
Check if IIS is running using C#
Implementing IEnumerable in C#
Built in Delegates in .NET Framework
Changing Winform webbrowser control IE version in C#.NET
Set the dropdown width of any combobox based on the data
Difference between Const and ReadOnly
Example for Property Change Notification in C#.NET
Example Program using Delegates and Events in C#
AutoComplete in .NET Winforms TextBox
WebBrowser.navigate Data Submission by Get or Post Methods
Datagridview Paging
Copy Datagridview cell value using ContextMenu
Inserting data in Datatable in C#
Cookie in .NET
Using Control's Child Index and Dock property for achieving desired layout in a form.
Accessing section group inside configuration of web.config
Create File in .NET C#
Change button shape in .net
Dispose vs Finalize in .net




How to read data from XML String and insert in to table in SQL Server