[SOLVED] Select value in xml field with mysql

Select value in xml field with mysql

I have a field in a sulu cms database.

Table phpcr_nodes and field props

This field is stocked in XML like this:

<?xml version="1.0" encoding="UTF-8"?>
<sv:node xmlns:sv="http://www.jcp.org/jcr/sv/1.0" xmlns:jcr="http://www.jcp.org/jcr/1.0" xmlns:mix="http://www.jcp.org/jcr/mix/1.0" xmlns:nt="http://www.jcp.org/jcr/nt/1.0" xmlns:rep="internal" xmlns:xs="http://www.w3.org/2001/XMLSchema">
   <sv:property sv:name="i18n:en-description" sv:type="String" sv:multi-valued="0">
      <sv:value length="2560">MY FIRST ITEM</sv:value>
   </sv:property>
   <sv:property sv:name="i18n:en-subtitle" sv:type="String" sv:multi-valued="0">
      <sv:value length="28">MY SECOND ITEM</sv:value>
   </sv:property>
</sv:node>

I would like to catch the value

<sv:value length="2560">MY FIRST ITEM</sv:value>

and

<sv:value length="28">MY SECOND ITEM</sv:value>

I have tried something like this:

SELECT
    SUBSTRING_INDEX(ExtractValue(props, '//sv:name="i18n:en-subtitle"'), ' ', 1) AS `subtitle`
from phpcr_nodes

It's a MySQL database, how can I get the value in SQL ?

Thanks in advance

Solution

If you need to write queries to PHPCR you should use the jackalope abstraction and its query builder so you can write normal SQL 2 queries and jackalope will convert them based on your adapter into the correct SQL query.

If you are interested how jackalope is converting SQL 2 query into SQL queries have a look at its QOMWalker implementation:

https://github.com/jackalope/jackalope-doctrine-dbal/blob/master/src/Jackalope/Transport/DoctrineDBAL/Query/QOMWalker.php

EXTRACTVALUE(props, '//sv:property[@sv:name="i18n:en-subtitle"]/sv:value')

The "EXTRACTVALUE" is a XPATH so you can also copy your XML into a website like http://xpather.com/ to find the correct xpath for your property selection.