If you are given an xml (as a table) as input and you want to store that XML in the database. How can be work with update/ insert from the XML

In SQL you are given a functionality that you can convert xml inputs from the application/ or Json or any other input can be opened in the database and it can be used in the SQL statements to the database:

---Table create

CREATE TABLE xmlEmploy

(FirstName VARCHAR(50),

ID nvarchar(20),

Department VARCHAR(50)

)

 DECLARE @insert_h int ,@update_h int

DECLARE @insert_xmldoc VARCHAR(1000) ,@update_xmldoc VARCHAR(1000)

--xmldoc is set with the xml elements which are to be inserted into the table Employs with FirstName,ID,Department as table columns -- insert for

SET @insert_xmldoc =

'

'

  SET @update_xmldoc =

'

'

EXEC sp_xml_preparedocument @insert_h OUTPUT, @insert_xmldoc

  EXEC sp_xml_preparedocument @update_h OUTPUT, @update_xmldoc

--This sp_xml_preparedocument is an internal system procedure. which takes the xmldoc as input and gives an output in @h

--which contains the data which is to be manipulated further

INSERT INTO xmlEmploy

SELECT * FROM OpenXML(@insert_h,'/root/Employ')

WITH xmlEmploy

 select * from xmlEmploy

 -----to update the table we can join by applying the with clause

UPDATE xmlEmploy

SET

FirstName = x.FirstName

,Department = x.Department

FROM OpenXML(@update_h,'/root/Employ')

WITH (FirstName nvarchar(20),ID nvarchar(20),Department nvarchar(20)) x where xmlEmploy.ID= x.ID

   select * from xmlEmploy

EXEC sp_xml_removedocument @insert_h

EXEC sp_xml_removedocument @update_h

--sp_xml_removedocument delete the document from the memory.