CODEWITHSUNDEEP
mysqlxml
PFranchise
PFranchise

Reputation: 6752

MYSQL ExtractValue from XML returning large blank

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.

Upvotes: 2

Views: 7536

Answers (2)

Ike Walker
Ike Walker

Reputation: 65567

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)

Upvotes: 5

Ted Hopp
Ted Hopp

Reputation: 234847

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>.

Upvotes: 3

Related Questions