oracle 提取XML中可用的JSON标记值

wyyhbhjk 于 2022-11-03 发布在 Oracle

关注(0)|答案(2)|浏览(148)

从下面的XML日志中，我要求只提取电话号码 <json:string name="phoneNumber">9480562628</json:string>。有人能帮助我吗？

<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>

预期结果：

9480562628

oracle

来源：https://stackoverflow.com/questions/74008710/extracting-json-tag-value-available-within-xml

2条答案

按热度按时间

7d7tgy0s1#

此语句获得了预期的结果。：）

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

赞(0）回复(0）举报 2022-11-03

rdrgkggo2#

使用XMLTABLE并指定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

对于您的示例数据：

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>'
);

输出：
| 电话号码|
| - -|
| 小行星9480562628|
fiddle

赞(0）回复(0）举报 2022-11-03

我来回答

oracle 提取XML中可用的JSON标记值

预期结果：

2条答案

相关问题

热门标签

最新问答