Oracle 从 xmltype 中提取值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17544610/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle extract values from xmltype
提问by ajmalmhd04
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;
/
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
nameor agetags .
上面的代码使用 xml 来提取现有节点值并将其保存到特定的局部变量中。它被用于多组数据的案例中，并且工作正常。我只是想知道我是否可以在没有“for x 循环”的情况下使用相同的数据，因为从现在开始我将在 i_xml 中只有一个数据，我将只有
nameorage标签。
The following code should be used to save into l_name or l_age without the "loop" method like I used above:
应该使用以下代码保存到 l_name 或 l_age 中，而不像我上面使用的那样使用“循环”方法：
<ROWSET>
<ROW>
<name>aa</name>
</ROW>
</ROWSET>
or
或者
<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
/ 任何更好的方式表示赞赏..谢谢
回答by Alex Poole
If you're really sure you'll only have one ROWthen you can do:
如果你真的确定你只有一个，ROW那么你可以这样做：
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;
That will work if you have nameor 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_namewould be aabbb, and l_agewould be 2025. Which might not be what you expect.
如果您有nameor age，或两者都有，或两者都没有（其中“工作”意味着至少不会出错），那将起作用。如果您确实有多于一行，它将连接结果，因此对于您的原始数据，l_name将是aabbb，并且l_age将是2025。这可能不是您所期望的。
