extracting json tag value available within xml-CodePudding

From the below XML log, I have requirement to extract phoneNumber only <json:string name="phoneNumber">9480562628</json:string>. Can someone help in this??

<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>
   </Body>
   <standardRule>Y</standardRule>
   <TRANSACTION_ID>SFASJNF3U6375H7D1Y4XWJDZ</TRANSACTION_ID>
   <TRANSACTION_NAME>Login</TRANSACTION_NAME>
   <JSON_Body>{ "phoneNumber":"9480562628" }</JSON_Body>
</Input>

Expected Result:

9480562628

CodePudding user response：

This statement got the result as expected. :)

extractvalue(xmltype(x.xml_request), '(/Input//phoneNumber)[1]/text()')
    || ' '|| extractvalue(xmltype(x.xml_request), '(/Input/Body//*[@name="phoneNumber"])[1]/text()') AS "phoneNumber_"

CodePudding user response：

Use XMLTABLE and specify the XMLNAMESPACES:

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

Which, for your sample data:

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>
   </Body>
   <standardRule>Y</standardRule>
   <TRANSACTION_ID>SFASJNF3U6375H7D1Y4XWJDZ</TRANSACTION_ID>
   <TRANSACTION_NAME>Login</TRANSACTION_NAME>
   <JSON_Body>{ "phoneNumber":"9480562628" }</JSON_Body>
</Input>'
);

Outputs:

PHONENUMBER
9480562628

fiddle