top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to fetch values from xml into table in sql

+4 votes
453 views
How to fetch values from xml into table in sql
posted Mar 27, 2015 by Muskan

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+2 votes
 
Best answer

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

enter image description here

answer May 25, 2015 by Manikandan J
Similar Questions
+2 votes

I want to update data from one table to another table with year condition.
(OR)
To Update one table based on another table using INNER JOIN

+3 votes

This is the Sales_Import table, where the account number field needs to be updated:
LeadID AccountNumber

147 ********** 
150 ********** 
185 7006100100007267039

And this is the RetrieveAccountNumber table, where I need to update from:
LeadID AccountNumber

147 7006100100007266957 
150 7006100100007267039 
...