我在配置单元日志表中存储了一个xml blob（如下所示）。

<user>
    <uid>1424324325</uid>
    <attribs>
        <field>
        ...
        </field>
        <field>
            <name>first</name>
            <value>Joh,n</value>
        </field>
        <field>
        ...
        </field>
        <field>
            <name>last</name>
            <value>D,oe</value>
        </field>
        <field>
        ...
        </field>
    </attribs>
</user>

配置单元表中的每一行都有关于不同用户的信息，我想提取uid、first name和last name的值（从名称中删除任何逗号）。

1424324325  John    Doe
1424435463  Jane    Smith

我能够从xml中提取值。

SELECT uid, fn, ln
FROM log_table
LATERAL VIEW explode(xpath(logs['users_updates'], '/user/uid/text()')) uids as uid
LATERAL VIEW explode(xpath(logs['users_updates'], '/user/attribs/field[name = "first_name"]/value/text()')) fns as fn
LATERAL VIEW explode(xpath(logs['users_updates'], '/user/attribs/field[name = "last_name"]/value/text()')) lns as ln;

然而，我在试图从first name和last name中删除不必要的逗号（如果存在的话）时遇到了麻烦。
当我尝试使用下面显示的任何方法提取名字时，结果是空的。

LATERAL VIEW explode(xpath(logs['users_updates'], '/users/attribs/field[name = "first_name"]/value/replace(text(),",","")')) fns as fn

LATERAL VIEW explode(xpath(logs['users_updates'], '/users/attribs/field[name = "first_name"]/value/translate(text(),",","")')) fns as fn

当我按如下所示尝试时，replace会抱怨函数无效，而translate会在不删除多余逗号的情况下提取数据。

LATERAL VIEW explode(xpath(logs['users_updates'], replace('/subscriberUpdates/updates/field[name = "first_name"]/value/text()',",",""))) fns as fn

LATERAL VIEW explode(xpath(logs['users_updates'], translate('/subscriberUpdates/updates/field[name = "first_name"]/value/text()',",",""))) fns as fn

如果名称值中没有逗号，如何提取信息？

1424324325  John    Doe
1424435463  Jane    Smith

最终解决方案：这是jens建议之后的最终工作查询

SELECT uid, regexp_replace(fn,","," ") as fname, regexp_replace(ln,","," ") as lname
FROM log_table
LATERAL VIEW explode(xpath(logs['users_updates'], '/user/uid/text()')) uids as uid
LATERAL VIEW explode(xpath(logs['users_updates'], '/user/attribs/field[name = "first_name"]/value/text()')) fns as fn
LATERAL VIEW explode(xpath(logs['users_updates'], '/user/attribs/field[name = "last_name"]/value/text()')) lns as ln;