使用动态XPATH从MySql中提取XML

Question

我在一个传统数据库中有两个表。其中一个包含一个字段，该字段包含一些XML。另一个表包含构成XML的标记。

例如，考虑一个列出语言列表的表（例如en，fr，it），以及一个具有类似字段的表：

<en>Something</en><fr>Quelque chose</fr><it>Qualcosa</it>

我想提取所有的翻译。我的查询大概是这样的：

SELECT GROUP_CONCAT(extractvalue(table.field, languages.sigla))
FROM table, languages
GROUP BY table.id

但是我收到以下错误

[HY000][1105] Only constant XPATH queries are supported

我猜这是 MySql（我使用的版本是5.6）的限制。有没有其他方法可以得到我要找的东西？

- marcosh

Answer 1

我曾经遇到同样的问题，并在这篇博客文章中找到了一个简单的解决方法：http://sql-debug.blogspot.com/2012/05/extractvalue-only-constant-xpath.html

这个解决方案真的很惊人，但看起来很傻，它完美地解决了问题...

它包括创建一个函数，只是“包装”ExtractValue函数，以便将xpath作为已生成的字符串传递给它。

delimiter ##
create function exv(xml text, xpath text) returns text charset utf8 
begin 
return cast(extractvalue(xml, xpath) as char); 
end ##
delimiter ;

然后，只需在您尝试运行时得到 [HY000][1105] 错误的查询中将 extractvalue 替换为 exv。

当然，这种解决方法会带来性能上的代价...

- Flo

Answer 2

你可以尝试的一个选项是（根据需要进行调整）：

mysql> SELECT
    ->     GROUP_CONCAT('SELECT ExtractValue(@`xml`, \'', `der`.`lang`, '\') `lang`' SEPARATOR ' UNION ALL ') INTO @`query`
    -> FROM (
    ->     SELECT 'en' `lang`
    ->     UNION
    ->     SELECT 'fr'
    ->     UNION
    ->     SELECT 'it'
    -> ) `der`;
Query OK, 1 row affected (0.00 sec)

mysql> SET @`xml` := '<en>Something</en><fr>Quelque chose</fr><it>Qualcosa</it>';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @`query` := CONCAT('SELECT GROUP_CONCAT(`der`.`lang`)
    '>                         FROM (', @`query`, ') `der`');
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE `stmt` FROM @`query`;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE `stmt`;
+----------------------------------+
| GROUP_CONCAT(`der`.`lang`)       |
+----------------------------------+
| Something,Quelque chose,Qualcosa |
+----------------------------------+
1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE `stmt`;
Query OK, 0 rows affected (0.00 sec)

- wchiquito