[TYPO3-dev] flexform, xml query, XPath, MySQL 5.1

[Fabien Udriot]

Hi,

Flexforms are a easy mean to generate interfaces and to store data into 
the database. *BUT* is there a way to query these data ?  Does anyone 
has experience with that ?

It seems that the next version of MySQL (5.1) will provide a way to 
query XML according to theses 2 links.

http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html
http://dev.mysql.com/tech-resources/articles/mysql-5.1-xml.html


I tested successfully these XML functions on flexdata. It is a great 
improvement being able to retrieve data directly from the database. For 
my point of view, it opens new coding perspective. Where you had to 
analyze a whole set of data with PHP, a good SQL request will do the job 
for you.

Here are some examples :

***** General syntax *****

SELECT extractValue(field_with_XML,'/request/XPath') FROM table_XYZ;

***** select all records where value of sheet 'sWriter' is not null ****

SELECT 
EXTRACTVALUE(pi_flexform,'/T3FlexForms/data/sheet[@index="sWriter"]/language/field/value]') 
as writer FROM tt_content GROUP BY uid HAVING writer != ''


***** select all records where value of sheet 'sWriter' contains 3 ****

SELECT 
EXTRACTVALUE(pi_flexform,'/T3FlexForms/data/sheet[@index="sWriter"]/language/field/value[self:text()]') 
as writer FROM tt_content GROUP BY uid HAVING writer LIKE '%3%'


**** the same as above, but, additionally, filter on list_type field ***

SELECT 
EXTRACTVALUE(pi_flexform,'/T3FlexForms/data/sheet[@index="sWriter"]/language/field/value[text()]') 
as writer FROM tt_content WHERE list_type = 
'tx_ecobox_controllers_content' AND pi_flexform != '' GROUP BY uid 
HAVING writer LIKE '%3%'



The question that remains is the interoperability with other DBMS. I 
don't think these requests would run out of the box in Postgres or in 
Oracle. They might need some adaptation to be runnable... But after a 
quick googling it appears that EXTRACTVALUE() and UPDATEXML() are not 
totally unknown for them.

best regards,

Fabien