I have XML column in Oracle table where i have this string:
<field name="Text1" type="String"><value/>
I need to extract the value of this field if exists, in the above example it doesn't exists. In the below example the value exists:
<field name="Text1" type="String"><value>12345</value>
What will be the best way to do this?
Thank you.
CodePudding user response:
As you said: extract value.
SQL> with test (col) as
2 (select '<field name="Text1" type="String"><value>12345</value></field>'
3 from dual
4 )
5 SELECT extractvalue(xmltype(col), '/field/value') result
6 from test;
RESULT
----------------------------------------------------------------------------------------------------
12345
SQL>
CodePudding user response:
XMLTABLE
would be a good choice to use in such a way that
SELECT x.*
FROM xml_tab t,
XMLTABLE('//Root'
PASSING xml_data
COLUMNS
field1 INT PATH 'field[@name="Text1"]/value',
field2 INT PATH 'field[@name="Text2"]/value'
) x
returning
FIELD1 | FIELD2 |
---|---|
12345 |
where xml_data
is
<Root>
<field name="Text1" type="String">
<value>12345</value>
</field>
<field name="Text2" type="String">
<value/>
</field>
</Root>
PS. The EXTRACTVALUE
function is deprecated. It is still supported for backward compatibility. However, Oracle recommends that you use the XMLTABLE
function, or the XMLCAST
and XMLQUERY
functions instead