How to fetch values from xml into table in sql

Question

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

Answer 1

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