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

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

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