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.
Step1. Create a Stored procedure in Sql server which takes one input parameter and returns “Success” /”Failure”
Step2.Execute the procedure(F5)
Step3. Testing - Execute the above stored procedure by passing the xml string
Output
The output will be in tabular format as below
Execute below command in sql server database
Select * from Employee

<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>
<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)
ASBEGINSET @retValue='Failed';
INSERT INTO [Employee]([id],[firstName],[lastName],[company])
SELECTCOALESCE([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
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 OUTPUTprint @retValue1
Output
The output will be in tabular format as below
Execute below command in sql server database
Select * from Employee
No comments:
Post a Comment