oracle xmltype内容只有一个字段，但是量太多，用xmltable解析报错，删成几百个就没问题，如何解决？

Question

select id from xmltable('xml/in' passing xmltype(:tablename) colnums id '/in/id')
大概是这样，xml参数做成了xmltable，:tablename是输入的xml参数，里面只有一个id字段，但是数量很多，几十万个，解析报错，但是输入几百个就能解析，怎么解决解析太多报错的问题

Answer 1

你给一小段完整的xml文本出来,我去试试

---

我做了个测试,模拟25万个记录

create table xml_test (a clob);

DECLARE
  l_line varchar2(4000);
  l_clob clob;
BEGIN
  l_line := '<IN>
        <AUACTIVITY_ID>372683</AUACTIVITY_ID>
    </IN>
    <IN>
        <AUACTIVITY_ID>807436</AUACTIVITY_ID>
    </IN>
    <IN>
        <AUACTIVITY_ID>386725</AUACTIVITY_ID>
    </IN>
    <IN>
        <AUACTIVITY_ID>400718</AUACTIVITY_ID>
    </IN>
    <IN>
        <AUACTIVITY_ID>386915</AUACTIVITY_ID>
    </IN>';
   EXECUTE IMMEDIATE 'TRUNCATE TABLE xml_test DROP STORAGE' ;
    insert into xml_test values ('<XML>');
  SELECT a
    INTO l_clob
    FROM xml_test
  for update;
  dbms_lob.open(l_clob, dbms_lob.lob_readwrite);
  for i in 1..50000 loop
  dbms_lob.writeappend(l_clob, lengthb(l_line), l_line);
  end loop;
  dbms_lob.writeappend(l_clob, lengthb('</XML>'), '</XML>');
  dbms_lob.close(l_clob);
  commit;
END;
/

SELECT COUNT(AUACTIVITY_ID)
  FROM xml_test,
       XMLTABLE('XML/IN' PASSING XMLTYPE(a) COLUMNS AUACTIVITY_ID NUMBER PATH
                '/IN/AUACTIVITY_ID');

可以很快查出结果

根据你这个sql中的 ":auIdParams“来看，应该是使用参数传入，这里容易忽视一个问题，就是varchar2的最大长度只有三万多，简单的字符串传入会导致内容被截断，导致xml校验不通过。
一般建议的用法是像我上面这个测试案例一样，用表中的字段作为参数去进行解析；
或者使用动态sql绑定变量的方式去查询，比如

--数组变量接收结果
EXECUTE IMMEDIATE q'{SELECT AUACTIVITY_ID
FROM XMLTABLE(
'XML/IN' PASSING XMLTYPE(:1) COLUMNS AUACTIVITY_ID NUMBER PATH '/IN/AUACTIVITY_ID'
) }' into rec using auIdParams;

--或者插表
EXECUTE IMMEDIATE q'{
insert into table_a (AUACTIVITY_ID)
SELECT AUACTIVITY_ID
FROM XMLTABLE(
'XML/IN' PASSING XMLTYPE(:1) COLUMNS AUACTIVITY_ID NUMBER PATH '/IN/AUACTIVITY_ID'
) }'  using auIdParams;