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?
我有一个包含多个值的非正常字段,因为它是Xml数据,直到现在才打算查询。 MySQL能否将此xml列拆分为多行?
Table
表
NameA | <Xml><Values<Value>1</Value><Value>2</Value><Value>3</Value></Values></Xml>
NameB | <Xml><Values<Value>1</Value><Value>2</Value></Values></Xml>
NameC | <Xml><Values<Value>1</Value><Value>2</Value><Value>3</Value><Value>4</Value></Values></Xml>
I want
我想要
NameA | 1
NameA | 2
NameA | 3
NameB | 1
Like this MSSQL/TSQL solution
像这样的MSSQL / TSQL解决方案
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
但在MySQL中我只能得到
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?
有什么优雅的方法可以在MySQL中拆分xml吗?
1 个解决方案
#1
8
No. You must solve this just like other mysql split column problems.
不,你必须像其他mysql拆分列问题一样解决这个问题。
- Can MySQL split a column?
- MySQL可以拆分列吗?
- Mysql string split
- Mysql字符串拆分
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.
希望有一天能帮助别人。
#1
8
No. You must solve this just like other mysql split column problems.
不,你必须像其他mysql拆分列问题一样解决这个问题。
- Can MySQL split a column?
- MySQL可以拆分列吗?
- Mysql string split
- Mysql字符串拆分
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.
希望有一天能帮助别人。