MYSQL ExtractValue from XML returning large blank

Question

I am attempting to use the ExtractValue MYSQL function to return a segment from the xml stored in one column of my datbase. Here is how I have everything set up.

Table:

    create table documents  
    (  
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,  
    application_id int NOT NULL,    
    content MEDIUMTEXT NOT NULL  
    );

Insert:

insert into documents values (null, 1,
    '<?xml version="1.0" encoding="ISO-8859-1"?>

    <bookstore>

    <book>
      <title lang="eng">Harry Potter</title>
      <price>29.99</price>
    </book>

    <book>
      <title lang="eng">Learning XML</title>
      <price>39.95</price>
    </book>

    </bookstore>');

Query:

SELECT content from documents into @xml;  
SELECT ExtractValue(@xml, '/bookstore');

The second query returns a really large empty text space. It almost seems like the empty space is equal to what should be returned and interestingly when I use an xpath that should return a smaller result, the blank field shrinks.

I would greatly appreciate some help with this issue and would be happy to supply more info or try anything out.

@ikeWalker I am expecting everything within the bookstore node. I have also tested several other xpaths and they all return blank (of various sizes) — PFranchise
– PFranchise, Commented Mar 16, 2011 at 16:29

Ike Walker · Accepted Answer · 2011-03-16 17:32:34Z

5

That's not what ExtractValue is for. It's for extracting specific values, not entire elements.

Here's an example using ExtractValue with your sample XML string to extract the price values:

mysql> set @xml = '<?xml version="1.0" encoding="ISO-8859-1"?>
    '>     <bookstore>
    '>     <book>
    '>       <title lang="eng">Harry Potter</title>
    '>       <price>29.99</price>
    '>     </book>
    '>     <book>
    '>       <title lang="eng">Learning XML</title>
    '>       <price>39.95</price>
    '>     </book>
    '>     </bookstore>';
Query OK, 0 rows affected (0.00 sec)

mysql>     
mysql> SELECT ExtractValue(@xml, '/bookstore/book/price');
+---------------------------------------------+
| ExtractValue(@xml, '/bookstore/book/price') |
+---------------------------------------------+
| 29.99 39.95                                 |
+---------------------------------------------+
1 row in set (0.00 sec)

answered Mar 16, 2011 at 17:32

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PFranchise Over a year ago

Ok, thanks. I appreciate you taking the time to help me out. Have a great day!

Ted Hopp · Accepted Answer · 2011-03-16 15:20:19Z

3

Perhaps it has to do with the behavior of ExtractValue:

[ExtractValue()] returns the text (CDATA) of the first text node which is a child of the element(s) matched by the XPath expression.

and

ExtractValue() returns only CDATA, and does not return any tags that might be contained within a matching tag, nor any of their content (see the result returned as val1 in the following example).

The first text node under /bookstore is the white space before <book>.

answered Mar 16, 2011 at 15:20

Ted Hopp

235k48 gold badges412 silver badges533 bronze badges

Collectives™ on Stack Overflow

MYSQL ExtractValue from XML returning large blank

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related