How to get data in XML with extractvalue between NS tags

Question

I'm working on the below xml. I need this data --> "12574017"

<ns0:AvlABC xmlns="http://xmlns.zzz.com/xxx/aaa/ZOZSE/POwn" 
xmlns:ns7="http://xmlns.zzz.com/xxx/aaa/ZOZ/POwn" 
xmlns:ns1="http://xmlns.zzz.com/xxx/aaa/ZOZSE/COwn" 
xmlns:ns0="http://xmlns.zzz.com/xxx/aaa/ZOZSE/OOwn" 
xmlns:ns2="http://xmlns.zzz.com/xxx/aaa/ZOZ/COwn">
    <ns2:RequestInfoBP>
        <ns2:TypeId>sales1</ns2:TypeId>
        <ns2:Code>SALE_ORD</ns2:Code>
        <ns2:Date>2016/03/02-18:47:32</ns2:Date>
        <ns2:Id>525810007</ns2:Id>
        <ns2:MDI>
            <ns2:CINFO>
                <ns2:CUSERID/>
                <ns2:CUSERID/>
            </ns2:CINFO>
            <ns2:UINFO>
                <ns2:UpdateDate>2016/03/02-18:47:44</ns2:UpdateDate>
                <ns2:UpdateUser>936455507</ns2:UpdateUser>
            </ns2:UINFO>
        </ns2:MDI>
        <ns2:InqType>VRF_INQ</ns2:InqType>
    </ns2:RequestInfoBP>
    <ns0:Prdtype>
        <ns7:ProductKey>
            <ns7:PrdId>22627705</ns7:PrdId>
        </ns7:ProductKey>
        <ns7:DptPrd>
            <ns7:PrdId>2150905</ns7:PrdId>
            <ns7:FlsValueName>
                <ns7:FlsCharId>7125</ns7:FlsCharId>
                <ns7:FlsCharName>txn_fon_id</ns7:FlsCharName>
                <ns7:FlsValueId>9352727</ns7:FlsValueId>
                <ns7:FlsName>txn_fon_asd</ns7:FlsName>
                <ns7:FlsValueNameBI>11237118</ns7:FlsValueNameBI>
            </ns7:FlsValueName>
            <ns7:FlsValueName>
                <ns7:FlsCharId>30188302</ns7:FlsCharId>
                <ns7:FlsCharName>txn_sd_id</ns7:FlsCharName>
                <ns7:FlsValueId>12574017</ns7:FlsValueId>
                <ns7:FlsName>txn_sd_asd</ns7:FlsName>
                <ns7:FlsValueNameBI>1235858</ns7:FlsValueNameBI>
            </ns7:FlsValueName>
        </ns7:DptPrd>
    </ns0:Prdtype>
</ns0:AvlABC>

I can get this value with substr and instr method

select to_char(substr(A.DATA,instr(A.DATA,'txn_sd_id\</ns7:FlsCharName\>\<ns7:FlsValueId\>')+length('txn_sd_id\</ns7:FlsCharName\>\<ns7:FlsValueId\>'),
instr(A.DATA,'\</ns7:FlsValueId\>\<ns7:FlsName\>txn_sd_asd')-instr(A.DATA,'txn_sd_id\</ns7:FlsCharName\>\<ns7:FlsValueId\>')-length('txn_sd_id\</ns7:FlsCharName\>\<ns7:FlsValueId\>')))
value , a.* from temp_xx a

But "ns7" field is variable state. Because it can be sometimes <ns7:FlsValueId> sometimes like this <ns8:FlsValueId> or <ns2:FlsValueId> or <ns14:FlsValueId> etc..

I think I must use XPath with extractvalue but I couldn't.

How Can I get this data is from variable "ns" tags with extractvalue or another way ?

select to_char(substr(A.DATA,instr(A.DATA,'txn_sd_id\</ns7:FlsCharName\>\<ns7:FlsValueId\>')+length('txn_sd_id\</ns7:FlsCharName\>\<ns7:FlsValueId\>'),
instr(A.DATA,'\</ns7:FlsValueId\>\<ns7:FlsName\>txn_sd_asd')-instr(A.DATA,'txn_sd_id\</ns7:FlsCharName\>\<ns7:FlsValueId\>')-length('txn_sd_id\</ns7:FlsCharName\>\<ns7:FlsValueId\>')))
value , a.* from temp_xx a

Answer 1

The extractvalue() function is deprecated, so you shouldn't really use that.

You can use XMLQuery, with an Xpath that ignores the namespaces, since you don't know which one you want:

select xmlquery(
  '//*:FlsValueName[*:FlsCharName="txn_sd_id"]/*:FlsValueId/text()'
  passing xmltype(a.data)
  returning content).getStringVal() as id
from temp_xx a

ID
12574017

fiddle

If your data column is already an XMLType, not CLOB or varchar2, then you don't need the xmltype() conversion call.

The XPath looks for a FlsValueName node which has a child FlsCharName node with value "txn_sd_id"; then gets the value from the FlsValueId node under that matched FlsValueName. All of the node references are prefixed with the *: wildcard namespace so they'll match any, and you don't even need to declare the namespaces as part of the XPath.

The text content of the node is returned, as a string; you can then convert that to a number by wrapping the XMLQuery in to_number() if you want that data type instead.

---

If you actually plan to extract multiple values then you can use XMLTable instead. For example:

select x.*
from temp_xx a
cross apply xmltable(
  '//*:FlsValueName'
  passing xmltype(a.data)
  columns
    prdid number path './../*:PrdId',
    flscharid number path '*:FlsCharId',
    flscharname varchar2(10) path '*:FlsCharName',
    flsvalueid number path '*:FlsValueId',
    flsname varchar2(10) path '*:FlsName',
    flsvaluenamebi number path '*:FlsValueNameBI'
) x

PRDID	FLSCHARID	FLSCHARNAME	FLSVALUEID	FLSNAME	FLSVALUENAMEBI
2150905	7125	txn_fon_id	9352727	txn_fon_as	11237118
2150905	30188302	txn_sd_id	12574017	txn_sd_asd	1235858

fiddle

You can specify the data types (including sensible sizes for strings) in the columns clause, and I've included an example if walking back up the node tree to get the PrdId from a higher level. Nested XMLTable calls are a more useful approach if you have multiple levels each with multiple values.