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