Mysql converts xml data or json data into tables.

I need to convert a string of xml data or json data into a tabular form in mysql.
the json_extract function can only deal with a single json data, but not the json array. The data taken by the ExtractValue function are spliced together. I don"t know how to separate them. Is there any other way? please consult the gods

data sample:
[{"fCategoryId": "796", "fCondition": "0.8"}, {"fCategoryId": "730", "fCondition": "0.05"}, {"fCategoryId": "731", "fCondition": "0.05"}]

select CONVERT(json_extract("{"fCategoryId":"796","fCondition":"0.8"}","$.fCondition"),DECIMAL(5,2)) AS fcid;

if you enter an json array, there is no output

< jrt >

<item>
    <fCategoryId>1006</fCategoryId>
    <fCondition>0.40</fCondition>
</item>
<item>
    <fCategoryId>1007</fCategoryId>
    <fCondition>0.30</fCondition>
</item>
<item>
    <fCategoryId>1008</fCategoryId>
    <fCondition>0.30</fCondition>
</item>

< / jrt >

SET @xmlstring = "<jrt><item><fCategoryId>1006</fCategoryId><fCondition>0.40</fCondition></item><item><fCategoryId>1007</fCategoryId><fCondition>0.30</fCondition></item><item><fCategoryId>1008</fCategoryId><fCondition>0.30</fCondition></item></jrt>"
;

SELECT ExtractValue(@xmlstring, "/jrt/item/fCategoryId") as fCategoryId,ExtractValue(@xmlstring, "/jrt/item/fCondition") as fCondition;

the data obtained is in the form of
fCategoryId | fCondition
1006 1007 1008 | 0.400.300.30

CREATE TABLE t1 (rec_num INT, jdoc JSON); INSERT INTO t1 VALUES (1, '{"fish": ["red", "blue"]}'), (2, '{"fish": ["one", "two", "three"]}'); SELECT rec_num, idx, JSON_EXTRACT(jdoc, CONCAT('$.fish[', idx, ']')) AS fishes FROM t1 -- Inline table of sequential values to index into JSON array JOIN ( SELECT 0 AS idx UNION SELECT 1 AS idx UNION SELECT 2 AS idx UNION -- ... continue as needed to max length of JSON array SELECT 3 ) AS indexes WHERE JSON_EXTRACT(jdoc, CONCAT('$.fish[', idx, ']')) IS NOT NULL ORDER BY rec_num, idx;

CREATE TABLE `t_list_row` ( `_row` int(10) unsigned NOT NULL, PRIMARY KEY (`_row`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT t_list_row VALUES (0), (1), (2) .... (65535) big enough; SET @j = '[1, 2, 3]'; SELECT JSON_EXTRACT(@j, CONCAT('$[', B._row, ']')) FROM (SELECT @j AS B) AS A INNER JOIN t_list_row AS B ON B._row < JSON_LENGTH(@j);