MySQL XML Querying

Question

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.

There is no newline in the `B` nodes... If the values are space-less strings, you can use a simple `REPLACE`, otherwise, it's hard to distinguish a 'natural' space from a delimiting space. — Wrikken, Jul 01 '12 at 11:12
I think [http://stackoverflow.com/questions/10808149/mysql-use-extractvaluexml-value-values-to-get-all-multiple-values-split-on][1] is the solution you are looking for. [1]: http://stackoverflow.com/questions/10808149/mysql-use-extractvaluexml-value-values-to-get-all-multiple-values-split-on — sel, Jul 09 '12 at 09:10

score 5 · Answer 1 · answered Jul 09 '12 at 10:31

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!

score 4 · Accepted Answer · edited May 23 '17 at 10:29

4

This problem was answered here:

Parse an XML string in MySQL

The solution from that article should workd if you change child to "B":

DECLARE i INT DEFAULT 1;
DECLARE count DEFAULT ExtractValue(xml, 'count(//child)');

WHILE i <= count DO
SELECT ExtractValue(xml, '//child[$i]');
SET i = i+1;
END WHILE

edited May 23 '17 at 10:29

Community

1
1

answered Jul 11 '12 at 20:32

Shawn

3,583
8
46
63

The above solution will produce multiple result sets, which is not something easy to manage. One could create a temporary table, iterate and write row by row to that table, then `SELECT` content of s temporary tables as result set. But this type of solution turns a `SELECT` query into a writing one, which is also problematic. – Shlomi Noach Jul 15 '12 at 05:09

score 0 · Answer 3 · edited Dec 30 '22 at 18:46

DROP PROCEDURE IF EXISTS `test223`$$ CALL test223()

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test223`()
BEGIN
  DECLARE xmlDoc TEXT;
  DECLARE i INT ;
  DECLARE coun INT;
  DECLARE child1 VARCHAR(400);
  DECLARE child2 VARCHAR(400);

  SET i =1;

  SET xmlDoc = '<Data><parent><child1>Example 1</child1><child2>Example 2</child2></parent><parent><child1>Example 3</child1><child2>Example 5</child2></parent><parent><child1>Example 5</child1><child2>Example 6</child2></parent></Data>';

  SET coun = ExtractValue(xmlDoc, 'count(/Data/parent/child1)');

  DROP TEMPORARY TABLE  IF EXISTS `parent`; 
  CREATE TEMPORARY TABLE parent (     child1 VARCHAR(400),   
    child2 VARCHAR(400) ); 

  WHILE i <= coun DO

    INSERT INTO parent
    SELECT ExtractValue(xmlDoc, '//parent[$i]/child1'), ExtractValue(xmlDoc,   '//parent[$i]/child2');

    SET i = i+1;

  END WHILE;

  SELECT * FROM  parent; 

END$$

DELIMITER ;

Response Table :

+-----------------------+
| Child1    | Child2    |
|-----------|-----------|
|Example 1  | Example 2 |
|-----------|-----------|
|Example 3  | Example 5 |
|-----------|-----------|
|Example 5  | Example 6 |
|-----------|-----------|

Source: http://pinaki-mukherjee.blogspot.in/2014/07/mysql-xml-querying.html

score -1 · Answer 4 · answered Jul 01 '12 at 11:16

You may need to rethink your database design. Relational database systems are not built to support multiple values in one field. ExtractValue is meant as a filter to fetch one value from XML data in a field, not for getting multiple rows of data.

You should read up on database normalization. All tables should at least adhere to the first normal form (1NF), i.e., only one value in each field. Tables which do not fulfill 1NF is usually pretty hard to query because SQL in general and implementations such as MySQL in particular does not give you any good tools for such querying.

If you still want to struggle along with your non-1NF table I think you may need to fetch your data out of the database and do the work in your application code.

the values there were just example but i intend to use them for id parsing that could be used in insertion, updating and removal of certain ids in a junction table. I also know how to use 5 normal forms. any way thanks for the answer.. just a newbie in MySQL i've been using SQL Server 2008 R2 before.. — Sheila SanDiego, Jul 01 '12 at 11:20

MySQL XML Querying

4 Answers4

Linked