如何转换存储在VARCHAR中的xml文本并使用XQUERY表达式进行选择 [英] How to convert xml text stored in a VARCHAR and select using XQUERY expression

问题描述

我将xml文档以文本形式存储在VARCHAR列中.我想将文本转换为XML文档，以便在文档上执行XQUERY选择.

I have xml documents stored as text in a VARCHAR column. I'd like to convert the text to XML documents in order to perform XQUERY selects on the documents.

示例文档如下:

<a>
   <b foor="bar"/>
   <c/>
</a>

我的查询如下:

SELECT XMLQUERY('//c' PASSING 
  XMLCAST('<a><b foor="bar"/><c/></a>' AS XML)) 
FROM SYSIBM.sysdummy1

但是我没有返回一个元素，而是得到了以下错误消息:

But instead of returning a element I get the following error message:

SQL错误[10507]:轴步骤中的上下文项必须是节点.SQLCODE = -16012，SQLSTATE = 10507

SQL Error [10507]: The Context item in an axis step must be a node. SQLCODE=-16012, SQLSTATE=10507

如何使它正常工作?

========================================
更新#1:以防万一有人偶然发现相同的问题.至少在我的环境中，下面的查询起作用了，但是我没有返回匹配的xml节点的文本，而是仅将列显示为值"[le]".我不得不将结果投射到例如VARCHAR XMLCAST()以获取XML作为文本.因此，我将Stavr00的查询更改为:

===========================================
Update #1: Just in case anyone stumbles upon the same problem. At least in my environment the queries below work but instead of returning the text of the matching xml nodes I only got columns showing up as value "[le]". I had to cast the result to e.g. VARCHAR XMLCAST() to get the xml as text. So I changed Stavr00's query to:

SELECT 
    XMLCAST(XMLQUERY('string(/a/b/@foo)' PASSING X) AS VARCHAR(20)) 
FROM (
SELECT xmlparse('<a><b foo="bar"/><c/></a>')
FROM sysibm.sysdummy1
) AS X(X)

推荐答案

同意@data_henrik.但是，我建议使用CTE嵌套来解析XML仅一次:

Agree with @data_henrik. However I recommend using CTE nesting as to parse XML only once:

SELECT 
    XMLQUERY('string(/a/b/@foo)' PASSING X) 
   ,XMLQUERY('//c' PASSING X)
FROM (
SELECT xmlparse('<a><b foo="bar"/><c/></a>')
FROM sysibm.sysdummy1
) AS X(X)

这篇关于如何转换存储在VARCHAR中的xml文本并使用XQUERY表达式进行选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！