Converting procedure from MS SQL to MySQL

Jagdish Devaku
Jagdish Devaku used Ask the Experts™
on
hi,

currently we are migrating our database to MS SQL Server to MYSQL. I am facing some difficulty while migrating procedures having xml functionality.

Please check the sample MS SQL procedure below.

Can help me out in migrating this to MYSQL.

Thanks in advance.
CREATE PROCEDURE [dbo].[AddOrUpdateActivity] 
@activityXML XML,@ActivityId BIGINT OUTPUT,@errorStatus INT OUTPUT
AS
BEGIN
	
	DECLARE @academicSessionId BIGINT
	DECLARE @activityName NVARCHAR(50)
    DECLARE @newActivityId BIGINT
	
	SET NOCOUNT ON;
	SET @academicSessionId=(SELECT newActivity.value('@AcademicSessionId','BIGINT') FROM @activityXML.nodes('/Activity-Info') node(newActivity))
	print @academicSessionId
	SET @newActivityId=(SELECT newActivity.value('@ActivityId','BIGINT') FROM @activityXML.nodes('/Activity-Info') node(newActivity))   
	print @newActivityId
	SET @activityName=(SELECT newActivity.value('@ActivityName','NVARCHAR(50)') FROM @activityXML.nodes('/Activity-Info') node(newActivity))
	print @activityName
	
IF @newActivityId = 0
	BEGIN
		BEGIN TRY --begin the TRY block
			BEGIN TRANSACTION ADD_ACTIVITY
			-- Insert statements for procedure here
			INSERT INTO tbl_activity_master(academic_session_id,activity_name) values(@academicSessionId,@activityName)
			COMMIT TRANSACTION ADD_ACTIVITY
			SET @activityId=@@IDENTITY	
		END TRY -- end of TRY block
		BEGIN CATCH -- control would come here only in case of an exception
			SET @errorStatus =  @@ERROR
						
				  ROLLBACK TRANSACTION ADD_ACTIVITY
		
		END CATCH -- end of CATCH block	
	END
ELSE
	BEGIN
		BEGIN TRY --begin the TRY block
			BEGIN TRANSACTION UPDATE_ACTIVITY
			-- Update statements for procedure here
			UPDATE tbl_activity_master set  activity_name =@activityName where activity_id=@newActivityId
			COMMIT TRANSACTION UPDATE_ACTIVITY
				
		END TRY -- end of TRY block
		BEGIN CATCH -- control would come here only in case of an exception
			SET @errorStatus =  @@ERROR
						
				  ROLLBACK TRANSACTION UPDATE_ACTIVITY
		
		END CATCH -- end of CATCH block	
	END
	PRINT @activityId
 
END

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2005

Commented:
If you're using MySQL 5.1, you can use the ExtractValue function.

http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html

In 5.0 and earlier, MySQL had no XML extraction abilities, beyond what you could roll yourself with LOCATE and SUBSTRING and things like that.
Jagdish DevakuSr DB Architect

Author

Commented:
sorry for delay in response...

Thanks for above info which i tried with no success..

how can i extract data from the below xml file. I need all the three columns with their values.

<Activity-Info AcademicSessionId="109" ActivityId="0" ActivityName="Test"/> 

Open in new window

Top Expert 2005

Commented:
You have not shown what you tried. You have not reported what version of MySQL you are running.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Jagdish DevakuSr DB Architect

Author

Commented:

below is the code we tried...

We are upgraded MySQL to 5.4.1 version to check the XML compatibility. We even tried the below code in 5.1 version.



 create PROCEDURE AddOrUpdateActivity 
(    
IN activityXML longtext,
IN ActivityId BIGINT ,
IN errorStatus INT 
)
BEGIN
    DECLARE academicSessionId BIGINT ;
    DECLARE activityName VARCHAR(50);
    DECLARE newActivityId BIGINT ;
      
    SELECT * FROM  activityXML LIMIT 1 INTO @xml ;
    
    SET academicSessionId=(select EXTRACTVALUE(@xml,'/Activity/Info/AcademicSessionId')) ;
 
    SET newActivityId=(select EXTRACTVALUE(@xml,'/Activity/Info/ActivityId') );   
 
    SET activityName=(select EXTRACTVALUE(@xml,'/Activity/Info/ActivityName'));
 
    
IF newActivityId = 0 
   THEN
        INSERT INTO tbl_activity_master(academic_session_id,activity_name) values(academicSessionId,activityName) ;
        SET activityId=IDENTITY  ;  
        
ELSE
            UPDATE tbl_activity_master
            SET  activity_name =activityName where activity_id=newActivityId;
    
        END IF;
    
    END 

Open in new window

Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
I don't have MySQL 5.1 on this machine to test, but try like this:
 create PROCEDURE AddOrUpdateActivity 
(    
IN activityXML longtext,
OUT ActivityId BIGINT ,
OUT errorStatus INT 
)
BEGIN
    DECLARE academicSessionId BIGINT ;
    DECLARE activityName VARCHAR(50);
    DECLARE newActivityId BIGINT ;
      
    SET academicSessionId=(select EXTRACTVALUE(activityXML,'/Activity/Info/AcademicSessionId')) ;
    SET newActivityId=(select EXTRACTVALUE(activityXML,'/Activity/Info/ActivityId') );   
    SET activityName=(select EXTRACTVALUE(activityXML,'/Activity/Info/ActivityName'));
  
IF newActivityId = 0 
   THEN
      INSERT INTO tbl_activity_master(academic_session_id,activity_name) 
      VALUES(academicSessionId,activityName) ;
      
      SET ActivityId=IDENTITY  ;    
   ELSE
      UPDATE tbl_activity_master
      SET  activity_name=activityName
      WHERE activity_id=newActivityId;
END IF;
 
END 

Open in new window

Top Expert 2005
Commented:
The snippet of XML you show does not contain the two tags <Activity><Info>, but rather the single tag <Activity-Info>. Further, AcademicSessionId is an attribute, rather than a tag. So the xpath you tried is incorrect.

Something like this should do the trick:

SELECT EXTRACTVALUE(@xml, '//Activity-Info/@AcademicSessionId')
mysql> SET @xml = '<Activity-Info AcademicSessionId="109" ActivityId="0" ActivityName="Test"/>';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select EXTRACTVALUE(@xml,'//Activity-Info/@AcademicSessionId');
+---------------------------------------------------------+
| EXTRACTVALUE(@xml,'//Activity-Info/@AcademicSessionId') |
+---------------------------------------------------------+
| 109                                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

Open in new window

Kevin CrossChief Technology Officer
Most Valuable Expert 2011
Commented:
Good catch.  I didn't see the post on the XML, was wondering if the XPATH was correct.
So using snoyes_jw's XPATH correction, the procedure could look like this:

(if you are going to use @xml, I think the syntax should be "SET @xml = activityXML;" since that is the XML string coming into the procedure.)
 create PROCEDURE AddOrUpdateActivity 
(    
IN activityXML longtext,
OUT ActivityId BIGINT ,
OUT errorStatus INT 
)
BEGIN
    DECLARE academicSessionId BIGINT ;
    DECLARE activityName VARCHAR(50);
    DECLARE newActivityId BIGINT ;
      
    SET academicSessionId=(select EXTRACTVALUE(activityXML,'///Activity-Info/@AcademicSessionId')) ;
    SET newActivityId=(select EXTRACTVALUE(activityXML,'//Activity-Info/@ActivityId') );   
    SET activityName=(select EXTRACTVALUE(activityXML,'//Activity-Info/@ActivityName'));
  
IF newActivityId = 0 
   THEN
      INSERT INTO tbl_activity_master(academic_session_id,activity_name) 
      VALUES(academicSessionId,activityName) ;
      
      SET ActivityId=IDENTITY  ;    
   ELSE
      UPDATE tbl_activity_master
      SET  activity_name=activityName
      WHERE activity_id=newActivityId;
END IF;
 
END 

Open in new window

Jagdish DevakuSr DB Architect

Author

Commented:
There is some issue with MySQL 5.1 on my system... will check it and let you know if the above works or not.
Jagdish DevakuSr DB Architect

Author

Commented:
hey sorry for the delay uys... mysql on my system got corrupted...

mwvisa1>> when i execute your code above, i got the following error...

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1

I checked the code regarding this error, but could not find anything...

can you suggest me anything regarding this issue...
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
I had to double check the IF syntax for MySQL as I am in my MS SQL Server more often than MySQL for stored procedures, so that is why this link is for IF; however, it reminded me about the delimiters.  Since ';' is the delimiter for MySQL, when building a multi-line statement, the code may try to execute too early unless you set the delimiter to something else like the exampled '//':
http://dev.mysql.com/doc/refman/5.1/en/if-statement.html

Just double checking that you changed delimiter.
Jagdish DevakuSr DB Architect

Author

Commented:
i think everything is fine with the syntax... but still why is this error occur...

i am using mysql 5.1....


Jagdish DevakuSr DB Architect

Author

Commented:
Though I am still facing issues... I would like to appreciate your efforts for trying...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial