MySQL 5.1 - XPath Question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Pesho
Forum Newbie
Posts: 12
Joined: Fri Jan 27, 2006 9:43 am

MySQL 5.1 - XPath Question

Post by Pesho »

Hi all,

For my project I have to use the new MySQL 5 built-in XPath Support.

However, I've come across the following problem:
Sometimes XML documents have several tags of the same type. For example:

<book>
<author>a1</author>
<author>a2</author>
<author>a3</author>
<book>

to query for the authors I use: select ExtractValue(column_name, 'book/author') from table_name;

it returns all the authors, separated with white space as follows: a1 a2 a3

This is pretty inconvenient for parsing if a single author has two names.

So, do you know any way to make MySQL return the result with any other separator string, except for white space.


I'd be very grateful if you can help!!!


Regards,
Pesho
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The manual states it will be a space delimited string. It does not offer a way to change that.

However

Code: Select all

mysql> SELECT ExtractValue('<a><b><![CDATA[la la]]></b><b>lo lo</b><b>li li</b></a>', 'count(//b)');
+---------------------------------------------------------------------------------------+
| ExtractValue('<a><b><![CDATA[la la]]></b><b>lo lo</b><b>li li</b></a>', 'count(//b)') |
+---------------------------------------------------------------------------------------+
| 3                                                                                     |
+---------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT ExtractValue('<a><b><![CDATA[la la]]></b><b>lo lo</b><b>li li</b></a>', '//b[position()=2]');
+----------------------------------------------------------------------------------------------+
| ExtractValue('<a><b><![CDATA[la la]]></b><b>lo lo</b><b>li li</b></a>', '//b[position()=2]') |
+----------------------------------------------------------------------------------------------+
| lo lo                                                                                        |
+----------------------------------------------------------------------------------------------+
Pesho
Forum Newbie
Posts: 12
Joined: Fri Jan 27, 2006 9:43 am

Post by Pesho »

Cooool, thanks!

That should work as well in my case.



Thanks again!
Pesho
Post Reply