Oracle从xmltype提取值 [英] Oracle extract values from xmltype

问题描述

这是我当前正在使用的代码:

This is the code I am currently using:

SET serveroutput ON
CREATE OR REPLACE
PROCEDURE test_proc(i_xml varchar2)
IS

l_name VARCHAR2(20);
l_age NUMBER;
l_xml xmltype;
BEGIN
l_xml := xmltype(i_xml);


FOR x IN
(SELECT VALUE(p) col_val
FROM TABLE(XMLSEQUENCE(EXTRACT(l_xml, '/ROWSET/ROW'))) p
)
LOOP

     IF x.col_val.existSNode('/ROW/name/text()') > 0 THEN
          l_name:= x.col_val.EXTRACT('/ROW/name/text()').getstringVal();
     END IF;
     IF x.col_val.existSNode('/ROW/age/text()') > 0 THEN
          l_age := x.col_val.EXTRACT('/ROW/age/text()').getstringVal();
     END IF;
end loop;

end;
/
BEGIN
test_proc('<ROWSET>
<ROW>
<name>aa</name>
<age>20</age>
</ROW>
<ROW>
<name>bbb</name>
<age>25</age>
</ROW>
</ROWSET>');
END;
/

上面的代码使用xml来提取现有节点值并将其保存到特定的局部变量.它用于多组数据，并且运行良好.我只是想知道是否可以在没有"for x loop"的情况下使用相同的控件，因为从现在开始，我将仅在i_xml中拥有一个数据，而我只能拥有其中一个 name或age标签.

The above code uses xml to extract and save the existing node values to particular local variables. It is been used in the case for multiple sets of data and is working fine. I just wanted to know whether can I able to use the same without "for x loop", because I will only have one data in the i_xml from now onwards and I will only have either name or age tags .

下面的代码应该用于保存到l_name或l_age中，而无需像我上面使用的那样使用循环"方法:

The following code should be used to save into l_name or l_age without the "loop" method like I used above:

<ROWSET>
<ROW>
    <name>aa</name>
</ROW>
</ROWSET>

或

<ROWSET>
<ROW>
    <age>18</age>
</ROW>
</ROWSET>

/ 而且我尝试使用以下内容:

/ And I've tried using the following:

SELECT
     CASE
          WHEN VALUE(p).existsNode('/ROW/name/text()') = 1
          THEN p.EXTRACT('/ROW/name/text()').getstringVal()
          WHEN VALUE(P).existsNode('/ROW/age/text()') = 1
          THEN p.EXTRACT('/ROW/age/text()').getstringVal()
     END
INTO l_new
FROM TABLE(xmlsequence(EXTRACT(l_xml, '/ROWSET/ROW'))) p;

/ 任何更好的方法都表示赞赏..谢谢

/ Any better way is appreciated.. Thanks

推荐答案

如果您确实确定只有一个ROW，则可以执行以下操作:

If you're really sure you'll only have one ROW then you can do:

begin
  l_xml := xmltype(i_xml);
  if l_xml.existsnode('/ROWSET/ROW/name') > 0 then
    l_name := l_xml.extract('/ROWSET/ROW/name/text()').getstringval();
  end if;
  if l_xml.existsnode('/ROWSET/ROW/age') > 0 then
    l_age := l_xml.extract('/ROWSET/ROW/age/text()').getnumberval();
  end if;
end;

如果您同时具有name或age或两者兼有(或两者都不存在)(至少在其中工作"表示没有错误)，则该方法将起作用.如果确实有多行，它将连接结果，因此对于原始数据，l_name将是aabbb，而l_age将是2025.可能不是您所期望的.

That will work if you have name or age, or both, or neither (where 'work' means doesn't error, at least). If you did have more than one row it would concatenate the results, so with your original data, l_name would be aabbb, and l_age would be 2025. Which might not be what you expect.

这篇关于Oracle从xmltype提取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！