Oracle从xml截断值中提取 [英] Oracle extract from xml truncating value
问题描述
我们有一个CLOB列,用于存储相当简单的XML,我需要从中提取其中一个标记的属性.我相信select语句是正确的,因为我得到了该值的一部分,但是它会被截断为80个字符.
We have a CLOB column where we're storing fairly simple XML, from which I need to extract the property of one of the tags. I believe the select statement is correct, since I get a portion of the value, but it's truncating it at 80 characters.
这是XML的摘要...
Here's a snippet of the XML...
<?xml version="1.0" encoding="UTF-8"?>
<service_orders count="1">
<service_order order_number="fakefakefake" id="fakefakefake">
<customer>
<customer_id>ABCDE12346</customer_id>
<first_name>FAKE</first_name>
<last_name>CUSTOMER</last_name>
</customer>
...
<images count="2">
<image src="https://s3.amazonaws.com/some_subfolder/deeper/deeper_still/ever_deeper/really_big_long_url_goes_here" thumbnail="https://s3.amazonaws.com/some_subfolder/deeper/deeper_still/ever_deeper/really_big_long_url_goes_here_thumb"/>
</images>
...
...这是SQL的摘要...
...and here's a snippet of the SQL...
select
xmltype(contact_data).extract('/service_order/@order_number').getStringVal ordnum
,extract(xmltype(contact_data) ,'//images/image/@src') imgsrc
from
my_table
where
trunc(contact_date) = trunc(sysdate)
and extractvalue(xmltype(contact_data) ,'/service_order/@order_number') = '&ordnum'
返回URL的开头,但是,如前所述,URL被截断为80个字符.
The beginning of the URL returns but, as previously stated, the URL is truncated at 80 characters.
我在做什么错了?
推荐答案
您是否在sqlplus中执行此操作?提取返回XMLType实例,并根据 long 变量,默认值为80.如果增加该值,则可以看到完整的URL.
Are you executing this in sqlplus? Extract returns a XMLType instance, and is displayed according to long variable, which is defaulted to 80. If you increase the value you can see the full URL.
SQL> with x(contact_data) as (
select '<?xml version="1.0" encoding="UTF-8"?>
<service_orders count="1">
<service_order order_number="fakefakefake" id="fakefakefake">
<images count="2">
<image src="https://s3.amazonaws.com/some_subfolder/deeper/deeper_still/ever_deeper/really_big_long_url_goes_here"/>
</images>
</service_order>
</service_orders>' from dual
)
select
length(extract(xmltype(contact_data),'/service_orders/service_order/images/image/@src')) as url_length,
extract(xmltype(contact_data),'/service_orders/service_order/images/image/@src') as url
from
x;
URL_LENGTH
----------
URL
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
101
https://s3.amazonaws.com/some_subfolder/deeper/deeper_still/ever_deeper/really_b
增加long变量的值后,
After increasing the value for long variable,
SQL> set long 120
SQL> with x(contact_data) as (
select '<?xml version="1.0" encoding="UTF-8"?>
<service_orders count="1">
<service_order order_number="fakefakefake" id="fakefakefake">
<images count="2">
<image src="https://s3.amazonaws.com/some_subfolder/deeper/deeper_still/ever_deeper/really_big_long_url_goes_here"/>
</images>
</service_order>
</service_orders>' from dual
)
select
length(extract(xmltype(contact_data),'/service_orders/service_order/images/image/@src')) as url_length,
extract(xmltype(contact_data),'/service_orders/service_order/images/image/@src') as url
from
x;
URL_LENGTH
----------
URL
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
101
https://s3.amazonaws.com/some_subfolder/deeper/deeper_still/ever_deeper/really_big_long_url_goes_here
您可以使用getStringVal函数将XMLType转换为不依赖于long变量的varchar2.
You can use the getStringVal function to convert the XMLType to varchar2, which doesn't depend on long variable.
SQL> set long 80
SQL> with x(contact_data) as (
select '<?xml version="1.0" encoding="UTF-8"?>
<service_orders count="1">
<service_order order_number="fakefakefake" id="fakefakefake">
<images count="2">
<image src="https://s3.amazonaws.com/some_subfolder/deeper/deeper_still/ever_deeper/really_big_long_url_goes_here"/>
</images>
</service_order>
</service_orders>' from dual
)
select
length(extract(xmltype(contact_data),'/service_orders/service_order/images/image/@src')) as url_length,
extract(xmltype(contact_data),'/service_orders/service_order/images/image/@src').getStringval() as url
from
x;
URL_LENGTH
----------
URL
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
101
https://s3.amazonaws.com/some_subfolder/deeper/deeper_still/ever_deeper/really_big_long_url_goes_here
这篇关于Oracle从xml截断值中提取的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!