Problem with OPENXML function in SQLSERVER during migration to MYSQL

[vinay varma]

Hi All,

We are migrating our application from SQL SERVER to MYSQL. We are having trouble migrating a StoredProcedure from SQLSERVER to MYSQL since that SP in SQL SERVER contains a method called "OPENXML".

We have done a lot of googling to find a solution for this problem but no use.

1. We have found many alternatives for OPENXML in MYSQL like LOAD XML, LOAD FILE, LOAD DATA etc.
2. When we tried to use LOAD XML in our SP it throwed syntax error. To fix this error we have also granted FILE privilege to the users. But still the syntax error occurs.We are using MYSQL SERVER 5.5 version.

We have converted most of the SP to MYSQL except for the OPENXML function.

We would be grateful if anyone could be able to give us a feasible solution for our problem.

Please find the SP below and provide us a solution....

Thanks in advance.


CREATE PROCEDURE MS_LayoutInfo_Insert(v_XMLFileNames LONGTEXT,
v_XMLData LONGTEXT,
v_LayoutId INT,
v_StyleId INT,
v_XMLFileNamesRowsCount INT,
v_XMLDataRowsCount INT,
INOUT v_ReturnErrorCode INT)
BEGIN

DECLARE v_var1 INT;
DECLARE v_var2 INT;
DECLARE v_Var3 INT;
DECLARE v_Var4 INT;
DECLARE v_var5 INT;
DECLARE v_var6 INT;
DECLARE v_ErrorCode1 INT;
DECLARE v_ErrorCode2 INT;
DECLARE v_Error INT;
DECLARE v_XMLFileNamesDoc INT;
DECLARE v_XMLDoc INT;

BEGIN
SET @SWV_Error = 1;
END;

SET @SWV_Error = 0;
SET v_var1 = v_XMLFileNamesRowsCount;
SET v_var2 = v_XMLDataRowsCount;
SET v_Error = 1;

select COUNT(*) INTO v_Var3 FROM MSLayoutFileinfo;
select COUNT(*) INTO v_Var4 FROM MSLayoutInfo;

START TRANSACTION;


CALL sp_xml_preparedocument(v_XMLFileNamesDoc,v_XMLFileNames);

INSERT INTO MSLayoutFileinfo(MSLFLayoutId, MSLFFileName, MSLFFilePath)
SELECT v_LayoutId AS MSLFLayoutId, CellType, FilePath
FROM OPENXML(v_XMLFileNamesDoc,'SQLWAYS_EVAL# t/XMLData',2)
WITH(CellType VARCHAR(50),FilePath VARCHAR(250));


SET @SWV_Error = 0;
CALL sp_xml_removedocument(v_XMLFileNamesDoc);

SET v_ErrorCode1 = @SWV_Error;
SET v_Error = v_ErrorCode1;


CALL sp_xml_preparedocument(v_XMLDoc,v_XMLData);

INSERT INTO MSLayoutInfo(MSLIObjectTypeId, MSLIStyleId, MSLIOreintationId, MSLIHeight, MSLIWidth, `MSLIX-Co`, `MSLIY-Co`, MSLIAngle, MSLIFileName)
SELECT ObjType.MSObjectTypeId AS MSLIObjectTypeId, v_StyleId AS MSLIStyleId, Ori.MSOrientationId AS MSLIOreintationId,
Height AS MSLIHeight, Width AS MSLIWidth, `X-Coord` AS `MSLIX-Co`, `Y-Coord` AS `MSLIY-Co`, Angle as MSLIAngle, MSLFId AS MSLIFileName
FROM OPENXML(v_XMLDoc,'SQLWAYS_EVAL# t/XMLData',2)
WITH(CellType VARCHAR(50),SizeShape VARCHAR(4),Height NUMERIC(7,4),Width NUMERIC(7,4),
`x-Coord` NUMERIC(7,4),`y-Coord` NUMERIC(7,4),Angle NUMERIC(7,4),
FileName VARCHAR(50))

CT INNER JOIN MSObjectType ObjType ON ObjType.MSObjectTypeShortName = CASE(CT.CellType)
WHEN 'Ba' THEN 'Ba' WHEN 'Fr1' THEN 'Fr' WHEN 'Photo1' THEN 'Photo' WHEN 'Fr2' THEN 'Fr'
WHEN 'Photo2' THEN 'Photo' WHEN 'Fr3' THEN 'Fr' WHEN 'Photo3' THEN 'Photo' WHEN 'Fr4' THEN 'Fr'

WHEN 'Photo4' THEN 'Photo' WHEN 'Photo5' THEN 'Photo' WHEN 'Fr5' THEN 'Fr' WHEN 'Photo6' THEN 'Photo' WHEN 'Fr6' THEN 'Fr'
WHEN 'Fr7' THEN 'Fr' WHEN 'Photo7' THEN 'Photo' WHEN 'Fr8' THEN 'Fr' WHEN 'Photo8' THEN 'Photo'
WHEN 'TT' THEN 'TT' WHEN 'TTText' THEN 'TTText' WHEN 'JT' THEN 'JT'
WHEN 'JTText' THEN 'JTText' WHEN 'BL1' THEN 'BL' WHEN 'BL2' THEN 'BL' WHEN 'BL3' THEN 'BL'
WHEN 'BL4' THEN 'BL' WHEN 'BL5' THEN 'BL' WHEN 'BL6' THEN 'BL' WHEN 'BL7' THEN 'BL' WHEN 'BL8' THEN 'BL'
WHEN 'BL9' THEN 'BL' WHEN 'BL10' THEN 'BL' WHEN 'BL11' THEN 'BL' WHEN 'BL12' THEN 'BL' WHEN 'BL13' THEN 'BL'
WHEN 'EM1' THEN 'EM' WHEN 'EM2' THEN 'EM' WHEN 'EM3' THEN 'EM' WHEN 'EM4' THEN 'EM' WHEN 'EM5' THEN 'EM'
WHEN 'EM6' THEN 'EM' WHEN 'EM7' THEN 'EM' WHEN 'EM8' THEN 'EM'


WHEN 'Bu1' THEN 'Bu' WHEN 'Bu2' THEN 'Bu' WHEN 'Bu3' THEN 'Bu'
WHEN 'Bu4' THEN 'Bu' WHEN 'Bu5' THEN 'Bu' WHEN 'Bu6' THEN 'Bu' WHEN 'Bu7' THEN 'Bu' WHEN 'Bu8' THEN 'Bu'
WHEN 'Bu9' THEN 'Bu' WHEN 'Bu10' THEN 'Bu' WHEN 'Bu11' THEN 'Bu' WHEN 'Bu12' THEN 'Bu' WHEN 'Bu13' THEN 'Bu' END
INNER JOIN MSOrientation Ori ON CT.SizeShape = Ori.MSOrientationName
INNER JOIN MSLayoutFileinfo MSLFI ON CT.FileName = MSLFI.MSLFFileName;


CALL sp_xml_removedocument(v_XMLDoc);

select COUNT(*) INTO v_var5 FROM MSLayoutFileinfo;
select COUNT(*) INTO v_var6 FROM MSLayoutInfo;

SET v_ErrorCode2 = @SWV_Error;

IF (v_ErrorCode1 <> 0 OR v_ErrorCode2 <> 0) then

ROLLBACK;
SET v_Error = 50004;

ELSE
COMMIT;
SET v_Error = 50001;
end if;
SELECT v_Error;
SET v_ReturnErrorCode = v_Error;
END;