Alternate of Openxml, merge statement of SQL server in MySQL

I have thousands of items in XML format. It consists ItemID and ItemName. My requirement is, I need to insert all the records into database in ItemMaster table. If any of these items is already exists then it should not be inserted rather it should update the ModifiedOn column in ItemMaster table. Currently I am using SQL Server and with the help of temporary table,OpenXML and Merge statement I am able to do it successfully in stored procedure.

My problem is I have do it in MySQL by using stored procedure. Looping is not required. I have no idea how to do it as I don't have much more knowledge of MySQL.

My SQL Server procedure is :

declare @XML xml =null,@XMLDOC Int

declare @TempTable Table(ItemID int, ItemName nvarchar(50))

EXEC sp_xml_preparedocument @XMLDOC output,

INSERT INTO @TempTable(ItemID, ItemName)

SELECT UOMID from openXML (@XMLDOC,'NewDataSet/Table1',2)

with (ItemID int, ItemName nvarchar(50))

EXEC sp_xml_removedocument @XMLDOC

MERGE ItemMaster as Target

Using(select ItemID,ItemName FROM @TempTable) as Source

ON(Target.Item_ID=Source.ItemID)

WHEN MATCHED THEN

Update set Target.Item_ModifiedOn=GETDATE()

WHEN NOT MATCHED THEN

INSERT(ItemName,Item_CreatedOn)

Values(Source.ItemName,GETDATE());

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Gokhul Varman9y ago
Refer the llink for detailed explanation:https://stackoverflow.com/questions/234/sql-server-2005-implementation-of-mysql-replace-into
Manas Mohapatra9y ago
To read data from xml file:

http://www.databasejournal.com/features/mysql/article.php/3846526/Working-with-XML-Data-in-MySQL.htm

http://download.nust.na/pub6/mysql/tech-resources/articles/xml-in-mysql5.1-6.0.html

http://sqlfiddle.com/#!2/d41d8/12093

To insert records and update records follow below links:

https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/

https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html