CODEWITHSUNDEEP
mysqlsql
Sheila SanDiego
Sheila SanDiego

Reputation: 191

MySQL XML Querying

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.

Upvotes: 5

Views: 17628

Answers (4)

Pinaki Mukherjee
Pinaki Mukherjee

Reputation: 185

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

Upvotes: 0

Shawn
Shawn

Reputation: 3379

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

Upvotes: 4

Shlomi Noach
Shlomi Noach

Reputation: 9414

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!

Upvotes: 5

Emil Vikstr&#246;m
Emil Vikstr&#246;m

Reputation: 91983

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.

Upvotes: -1

Related Questions