By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (xml CLOB);
INSERT INTO table_name (xml) VALUES ('<Input>
<Header>
<User-Agent>android;11;4.27.0;samsung_SM-A205F</User-Agent>
<Date>Sun, 09 Oct 2022 21:59:08 GMT</Date>
<Username />
<UserInfo />
<Location>1wIRSNscfkI0qragmfshMiG189qgAf/PumlP3DTbgN4=</Location>
<AAAUN>SFASJNF3U6375H7D1Y4XWJDZ</AAAUN>
<Authorization>WS androidDove:ri6/G20ZNX+bsNyX8GUEB4vSMS4=</Authorization>
<h>test</h>
<Accept-Language>en</Accept-Language>
<Test>false</Test>
<Content-Type>application/json; charset=UTF-8</Content-Type>
<Content-Length>75</Content-Length>
<Host>com.in</Host>
<Accept-Encoding>gzip</Accept-Encoding>
<X-Forwarded-For>0.0.0.0, 0.0.0.0</X-Forwarded-For>
<X-APIRP-ID>0.0.0.0</X-APIRP-ID>
<Via>1.1 69WC0-</Via>
<X-Client-IP>0.0.0.0.</X-Client-IP>
<X-Global-Transaction-ID>RU44D1I3S40ZBVLMQHHUZSB1QOH1HEWO700LZQLB5WR8IGZYU4</X-Global-Transaction-ID>
</Header>
<X />
<URI>/esb/crs2/public/Login</URI>
<ServiceName>PUBLICPOSTOTPLOGIN</ServiceName>
<PrimaryKey />
<Parameters>
<Parameter1 />
<Parameter2 />
<Parameter3 />
<Parameter4 />
</Parameters>
<Body>
<json:object xmlns:json="http://www.ibm.com/" xmlns:xsi="http://www.w3.org/" xsi:schemaLocation="http://www.datapower.com">
<json:string name="phoneNumber">9480562628</json:string>
</json:object>
1 rows affected
SELECT x.*
FROM table_name t
CROSS APPLY XMLTABLE(
XMLNAMESPACES('http://www.ibm.com/' AS "json", 'http://www.w3.org/' AS "xsi"),
'/Input'
PASSING XMLTYPE(t.xml)
COLUMNS
phonenumber VARCHAR2(20) PATH './Body/json:object/json:string[@name="phoneNumber"]'
) x
| PHONENUMBER |
|---|
| 9480562628 |