Tuesday, 10 December 2013

XML String To SQL Server

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)
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



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


Insert xml into Database table sql

No comments:

Post a Comment