Any alternate of openxml and merge statement of SQL server in mysql

Question

1.60/5 (3 votes)

See more:

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.

What I have tried:

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

Posted 6-Aug-17 23:52pm

Prashant Vishnoi

Updated 7-Aug-17 3:03am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rohit Pai (rodude123) · Answer 1 · 2017-08-07T03:03:00

You can do this in c#.net itself like this
Firstly you will have download the MYSQL library which can be found through the nugget packages.

2. Get the MySQL db information e.g. username, password, host etc. and any other info required. Then make something like this which will allow you to connect to the server
Here is an example:

C#

MySqlConnection makeConnection()
{
    try
    {
        //connection information
        server = "localhost";
        database = "dbname";
        uid = "user";
        dbPassword = "password";
        string connectionString = "SERVER=" + server + ";" + "DATABASE=" +
        database + ";" + "UID=" + uid + ";" + "PASSWORD=" + dbPassword + ";";
        //connect to the database
        connection = new MySqlConnection();
        connection.ConnectionString = connectionString;
        connection.Open();
        return connection;
    }
    catch (MySqlException e)
    {
        //if anything goes wrong
        MessageBox.Show(@"Something has gone wrong here (Your own message here if you want)");
        Console.WriteLine(e.ToString());
        return connection;
    }
}

3. You will need to check if the data is on the database.
How do you do it?
Loop through each value and check if it is in the database.
Here is an example:

C#

//checking each value in the database
MySqlConnection conn = makeConnection(); //using the connection method above
string[] values = new string[] { "string1", "string2", "string3", "string4", "string5", };
for (int i = 0; i < values.Length; i++)
{
    //.ExecuteScalar is used only to get single values
    string checkQuery = "SELECT col1 FROM table1 WHERE col1 = '" + values[i] + "'";
    MySqlCommand checkCommand = new MySqlCommand(checkQuery, conn);
    string checkValue = checkCommand.ExecuteScalar().ToString();
    if(checkValue == values[i])
    {
        //.ExecuteNonQuery only used for updating values inserting values etc.
        //things that don't need to return a value.
        string updateColumnQuery = "UPDATE table1 SET col2 = 'already here'";
        MySqlCommand updateColumnCommand = new MySqlCommand(updateColumnQuery, conn);
        updateColumnCommand.ExecuteNonQuery();
    }
    else
    {
        //Insert new value
        string insertColumnQuery = "INSERT INTO table1 (col1) VALUES('" 
+ values[i] +"')";
        MySqlCommand insertColumnCommand = new MySqlCommand(insertColumnQuery, conn);
        insertColumnCommand.ExecuteNonQuery();
    }
}

Hope this helps, I haven't tried the code myself but as per my suggestion I think that this code should work. Just remember to get the correct db information otherwise it could be giving lots of errors. That happened to me the first time I was using the MySQL c# library. Hope your final work gets completed.
Rohit Pai, C#, HTML, CSS, JS, PHP, (Python still incomplete)