MySQL XML Querying

MySQLsql

I kind of having problems with ExtractValue Function in MySQL.

Here is my sample XML:

<As>
    <A>
        <B>Chan</B>
    </A>
    <A>
        <B>Shey</B>
    </A>
    <A>
        <B>Bob</B>
    </A>
</As>

Here is my current query:

SELECT ExtractValue(@XML, '/As/A/B')

Here is the result:

CHAN SHEY BOB

Here is what i want:

CHAN
SHEY
BOB

Can someone please help me achieve this.. thanks.

Best Answer

A solution to your problem would require the usage of a numbers table: a table of integers, 1,2,3,.... up to some reasonable value, say 1024.

You would then use String Walking to solve the problem.

Here is the CREATE TABLE statement for the numbers table:

CREATE TABLE numbers (
  `n` smallint unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`n`)
)
;
INSERT INTO numbers VALUES (NULL);
INSERT INTO numbers SELECT NULL FROM numbers;
INSERT INTO numbers SELECT NULL FROM numbers;
INSERT INTO numbers SELECT NULL FROM numbers;
INSERT INTO numbers SELECT NULL FROM numbers;
INSERT INTO numbers SELECT NULL FROM numbers;
INSERT INTO numbers SELECT NULL FROM numbers;
INSERT INTO numbers SELECT NULL FROM numbers;
INSERT INTO numbers SELECT NULL FROM numbers;
INSERT INTO numbers SELECT NULL FROM numbers;
INSERT INTO numbers SELECT NULL FROM numbers;

The above populates with values 1..1024

And now the query:

SELECT 
  SUBSTRING_INDEX(SUBSTRING_INDEX(ExtractValue(@XML, '/As/A/B'), ' ', n), ' ', -1) AS value
FROM
  numbers
WHERE
  n BETWEEN 1 AND ExtractValue(@XML, 'count(/As/A/B)')
;


+-------+
| value |
+-------+
| Chan  |
| Shey  |
| Bob   |
+-------+
3 rows in set (0.02 sec)

We use ExtractValue(@XML, 'count(/As/A/B)') to get the value 3 -- the number of matching XML elements.

Running through numbers 1, 2, 3, we extract token #1, token #2, token #3 from the text CHAN SHEY BOB, splitting by space.

Notes:

  • ExtractXML returns values space delimited. But if there's a space within the returned text - no go. It would be indistinguishable from the delimiting spaces.

  • It is possible to avoid creating the numbers table and generate the numbers on the fly. I advise against -- it would create a lot of overhead. Having a 1024 row numbers table is always nice to have.

Good luck!

Related Topic