检查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屋！