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