MySQL use ExtractValue(XML, 'Value/Values') to get all multiple values (split one column into rows)

mysql, xml

Solution

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

- Can MySQL split a column?

- Mysql string split

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.

Problem

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 | <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 ``` 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?

Original source

Related problems