mysql extractvalue_MySQL使用ExtractValue（XML，'Value / Values'）获取所有多个值（将一列拆分为行）...

最新推荐文章于 2023-10-16 21:56:09 发布

熔岩巨兽墨菲特

最新推荐文章于 2023-10-16 21:56:09 发布

阅读量457

点赞数

文章标签： mysql extractvalue

本文链接：https://blog.csdn.net/weixin_34408918/article/details/113191959

版权

本文介绍了一种在MySQL中将XML格式的数据拆分成多行的方法。通过创建自定义函数STRSPLIT和存储过程GetNameValues，可以有效地从XML字段中提取多个值，并将其存储在临时表中以便进一步处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

I have a non-normal field containing multiple values because it is Xml data that wasn't intended to be queried, until now. Can MySQL split this xml column into multiple rows?

Table

NameA | 123

NameB | 12

NameC | 1234

I want

NameA | 1

NameA | 2

NameA | 3

NameB | 1

Like this MSSQL/TSQL solution

SELECT

I.Name,

Value.value('.','VARCHAR(30)') AS Value

FROM

Item AS I

CROSS APPLY

Xml.nodes('/Xml/Values/Value') AS T(Value)

WHERE

I.TypeID = 'A'

But in MySQL I can only get

NameA | 123

NameB | 12

NameC | 1234

with

SELECT

I.`Name`,

ExtractValue(Xml,'/Xml/Values/Value') AS ListOfValues

FROM

Item AS I

WHERE

I.TypeID = 'A'

;

Are there any elegant ways to split xml in MySQL?

解决方案

No. You must solve this just like other mysql split column problems.

I.e. Specifically based on this answer

DROP FUNCTION IF EXISTS STRSPLIT;

DELIMITER $$

CREATE FUNCTION STRSPLIT($Str VARCHAR(20000), $delim VARCHAR(12), $pos INTEGER)

RETURNS VARCHAR(20000)

BEGIN

DECLARE output VARCHAR(20000);

SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX($Str, $delim, $pos)

, LENGTH(SUBSTRING_INDEX($Str, $delim, $pos - 1)) + 1)

, $delim

, '');

IF output = ''

THEN SET output = null;

END IF;

RETURN output;

END $$

You can iterate through the values like so

DROP PROCEDURE IF EXISTS GetNameValues $$

CREATE PROCEDURE GetNameValues()

BEGIN

DECLARE i INTEGER;

DROP TEMPORARY TABLE IF EXISTS TempList;

CREATE TEMPORARY TABLE TempList(

`Name` VARCHAR(256) COLLATE utf8_unicode_ci NOT NULL,

`ValueList` VARCHAR(20000) COLLATE utf8_unicode_ci NOT NULL

);

DROP TEMPORARY TABLE IF EXISTS Result;

CREATE TEMPORARY TABLE Result(

`Name` VARCHAR(256) COLLATE utf8_unicode_ci NOT NULL,

`Value` VARCHAR(128) COLLATE utf8_unicode_ci NOT NULL

);

INSERT INTO

TempList

SELECT

I.`Name`,

ExtractValue(Xml,'/Xml/Values/Value') AS ValueList

FROM

Item AS I

WHERE

I.TypeID = 'A'

;

SET i = 1;

REPEAT

INSERT INTO

Result

SELECT

`Name`,

CAST(STRSPLIT(ValueList, ' ', i) AS CHAR(128)) AS Value

FROM

TempList

WHERE

CAST(STRSPLIT(ValueList, ' ', i) AS CHAR(128)) IS NOT NULL

;

SET i = i + 1;

UNTIL ROW_COUNT() = 0

END REPEAT;

SELECT * FROM Result ORDER BY `Name`;

END $$

DELIMITER ;

CALL GetNameValues();

Hope this helps someone one day.