mysql 替换SqlServer 的openXML 方法 （利用的是存储过程）

写入存储过程

代码如下

CREATE  PROCEDURE `prcd_update`(IN xmlData text, OUT importSuccess int)
    COMMENT 

DROP TABLE IF EXISTS shop_level_tmptable;
-- 不存在则创建临时表
CREATE TEMPORARY TABLE
IF
NOT EXISTS shop_level_tmptable (
	ShopNO VARCHAR ( 100 ) PRIMARY KEY, 
	ShopName VARCHAR ( 255 ), 
	ShopType VARCHAR ( 255 ), 
	ShopClass VARCHAR ( 255 ), 
	DataStatus INTEGER 
);


TRUNCATE TABLE shop_level_tmptable;
-- 使用前先清空临时表

-- 从XML字串中抽取数据
SET
@shopNos := EXTRACTVALUE (xmlData, '/root/record/ShopNO');
SET
@shopNames := EXTRACTVALUE (xmlData, '/root/record/ShopName');
SET
@shopTypes := EXTRACTVALUE (xmlData, '/root/record/ShopType');
SET
@shopClasses := EXTRACTVALUE (xmlData, '/root/record/ShopClass');

-- 数据索引
SET @INDEX = 1;

-- 通过计算去除的空格数量统计有多少行数据
SET @shopNoCount = CHAR_LENGTH(@shopNos) - CHAR_LENGTH( REPLACE (@shopNos, ' ', '' ) ) + 1;

-- 循环向临时表插入数据
WHILE
@INDEX <= @shopNoCount DO

-- 从字串中截取单行数据，插入表中
SET @shopId = SUBSTRING_INDEX( SUBSTRING_INDEX( @shopNos, ' ',  @INDEX ), ' ',- 1 );
SET @shopName = SUBSTRING_INDEX( SUBSTRING_INDEX( @shopNames, ' ',  @INDEX ), ' ',- 1 );
SET @shopType = SUBSTRING_INDEX( SUBSTRING_INDEX( @shopTypes, ' ',  @INDEX ), ' ',- 1 );
SET @shopClass = SUBSTRING_INDEX( SUBSTRING_INDEX( @shopClasses, ' ',  @INDEX ), ' ',- 1 );

-- 执行插入 
INSERT INTO shop_level_tmptable(ShopNO, ShopName, ShopType, ShopClass, DataStatus)
VALUES
(@shopId, @shopName, @shopType, @shopClass, null);

-- 递增索引
SET @INDEX = @INDEX + 1;
END WHILE;

-- 验证数据
UPDATE shop_level_tmptable SET DataStatus = 1 WHERE ShopNo NOT IN (SELECT ShopID FROM Lenovo_ShopInfo);

-- UPDATE shop_level_tmptable SET DataStatus = 2 WHERE ShopType NOT IN ();

set importSuccess = 2;
END

SUBSTRING_INDEX的用法： •SUBSTRING_INDEX(str,delim,count) 在定界符 delim 以及count 出现前，从字符串str返回自字符串。若count为正值,则返回最终定界符(从左边开始)

第一步得到的是  @shopNos 中如 3 4 5 利用空格分割的字符串

SET
@shopNos := EXTRACTVALUE (xmlData, '/root/record/ShopNO');

第二步计算得到 数据的行数

SET @shopNoCount = CHAR_LENGTH(@shopNos) - CHAR_LENGTH( REPLACE (@shopNos, ' ', '' ) ) + 1;

第三步利用循环将数据存入数据库

SET @shopId = SUBSTRING_INDEX( SUBSTRING_INDEX( @shopNos, ' ',  @INDEX ), ' ',- 1 );

-1是从右往左遇到第一个
2是从左往右遇到第二个
substring_index(@shopNos, '-', 2)表示截取 @shopNos 字符串的第二个 - 之 前 的所有字段 例如 1 2 3 截取 1 2

substring_index(str, '-', -1) 表示截取str字符串的从右往左第一个 - 之后的所有字段 例如 1 2 截取 2

举例：

CALL prcd_shop_level_update('
<?xml version="1.0" encoding="UTF-8"?>
<root>
	<version>
		<t>BasicInformation</t>
		<v>2</v>
	</version>
	<record>
		<RowIndex name="序号">3</RowIndex>
		<ShopNO>454</ShopNO>
		<ShopName>1专卖店</ShopName>
		<ShopType>POP专卖店</ShopType>
		<ShopClass>星级</ShopClass>
	</record>
	<record>
		<RowIndex name="序号">4</RowIndex>
		<ShopNO>119</ShopNO>
		<ShopName>109店</ShopName>
		<ShopType>数码店</ShopType>
		<ShopClass>钻石级</ShopClass>
	</record>

</root>
', @res);
select @res;
select * from shop_level_tmptable;