如何使用ORACLE SQL从XML分别提取可重复的json节点值？

小编典典

如何使用ORACLE SQL从XML分别提取可重复的json节点值？

sql

我有以下的XML和我想提取JSON参数“serviceNumber”的价值观分开
我尝试使用EXTRACT功能，但我得到的结果连接起来，但我希望他们分开。

<Input>
   <Header>
      <User-Agent></User-Agent>
      <Accepted-Language></Accepted-Language>
      <Authorization></Authorization>
      <Date>Mon, 27 Feb 2017 21:39:55 GMT</Date>
      <Username></Username>
      <Test />
      <UserInfo />
      <msisdn />
      <imsi />
      <X-Forwarded-For></X-Forwarded-For>
      <x-Client-IP></x-Client-IP>
      <Location />
   </Header>
   <URI></URI>
   <ServiceName></ServiceName>
   <PrimaryKey></PrimaryKey>
   <Parameters>
      <Parameter1></Parameter1>
      <Parameter2 />
      <Parameter3 />
   </Parameters>
   <Body>
      <json:object xmlns:json="http://www.ibm.com/xmlns/prod/2009/jsonx" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.datapower.com/schemas/json jsonx.xsd">
         <json:string name="transactionType"></json:string>
         <json:array name="payments">
            <json:object>
               <json:string name="amount"></json:string>
               <json:string name="serviceNumber">536888669</json:string>
               <json:string name="serviceType"></json:string>
               <json:string name="accountNumber"></json:string>
            </json:object>
            <json:object>
               <json:string name="amount"></json:string>
               <json:string name="serviceNumber">535394441</json:string>
               <json:string name="serviceType"></json:string>
               <json:string name="accountNumber"></json:string>
            </json:object>
            <json:object>
               <json:string name="amount"></json:string>
               <json:string name="serviceNumber">138422262</json:string>
               <json:string name="serviceType"></json:string>
               <json:string name="accountNumber"></json:string>
            </json:object>
            <json:object>
               <json:string name="amount"></json:string>
               <json:string name="serviceNumber">505809543</json:string>
               <json:string name="serviceType"></json:string>
               <json:string name="accountNumber"></json:string>
            </json:object>
            <json:object>
               <json:string name="amount"></json:string>
               <json:string name="serviceNumber">138422338</json:string>
               <json:string name="serviceType"></json:string>
               <json:string name="accountNumber"></json:string>
            </json:object>
         </json:array>
      </json:object>
   </Body>
</Input>

我想要像这样的输出

serviceNumber
-------------
536888669
535394441
138422262
505809543
138422338

阅读 179

2021-04-14

共1个答案

小编典典

用途XMLTABLE：

SELECT x.serviceNumber
FROM   your_table t
       CROSS JOIN
       XMLTABLE(
         XMLNAMESPACE( 'http://www.yourserver.url/json/' AS "json" ),
         '//root/Input/Body/json:object/json:array/json:object/'
         PASSING XMLTYPE(
           '<?xml version="1.0" encoding="utf-8"?>' ||
           '<root xmlns:json="http://www.yourserver.url/json/">' ||
           t.your_xml_column ||
           '</root>'
         )
         COLUMNS serviceNumber NUMBER PATH './json:string[@name="serviceNumber"]'
       ) x

2021-04-14