检查XMLTable中的列长 [英] Check length of column in XMLTable
问题描述
这是我在XMLTYPE列中的XML:
This is my XML in an XMLTYPE column:
<customers>
<customer>
<name>abc</name>
<surname>abc</surname>
<address>abc</address>
</customer>
<customer>
<name>abc</name>
<surname>abc</surname>
<address>abc</address>
</customer>
</customers>
我希望如果字段的长度超过允许的长度(15个字符),那么请使用默认值.
I want that if the length of a field exceeds the allowed length, 15 charcaters, then use a default value instead.
在DB2指南中,我找到了这个示例:
In the guide for DB2 I found this example:
select ...from .., XMLTABLE
('$INFO/customerinfo*' passing
columns
city varchar(16) path a'addr/city(if(string-lenght(.)<=16) then . else "Error!")'
) ..
我如何在Oracle中做到这一点?这是我当前的XMLTABLE尝试:
How can I do it in Oracle? This is my current XMLTABLE attempt:
select ..XMLTABLE
('customers/*' passing ..
columns
...
"address" varchar(15) path 'indirizzo/(if(lenght(.)<=15) then . else "Error!")'
) data;
但是得到了
ORA-19237.无法解决对函数-fn:length
ORA-19237. unable to resolve call to function -fn:length
推荐答案
您有错别字和命名不一致,因此很难准确地知道自己在做什么,以得到该错误,但是如果使用string-length()
DB2示例中的函数,而不是length()
(或lenght()
):
You have typos and inconsistent naming so it's hard to tell exactly what you're really doing to get that error, but it works if you use the string-length()
function from your DB2 example, rather than length()
(or lenght()
):
"address" varchar2(15) path 'address/(if(string-length(.)<=15) then . else "Error!")'
使用修改后的数据触发错误行为:
With modified data to trigger the error behaviour:
select *
from XMLTABLE ('customers/*'
passing xmltype('<customers>
<customer>
<name>abc</name>
<surname>abc</surname>
<address>abc def ghi jkl mno</address>
</customer>
<customer>
<name>abc</name>
<surname>abc</surname>
<address>abc</address>
</customer>
</customers>')
columns
"address" varchar(15) path 'address/(if(string-length(.)<=15) then . else "Error!")'
) data;
address
---------------
Error!
abc
这篇关于检查XMLTable中的列长的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!