Oracle XML多节点

qlvxas9a 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(103)

我在表TEST_XML中有以下XML，其中有一个CLOB类型的列“XMLTEXT”。
我希望在最后收到以下类似表格的结果：

User_Identity                       Product_offer_ID   Keyword
[email protected]   11990              34861
[email protected]   11990              35357
[email protected]   11990              34841

XML内容：

<MakeOfferEDRs>
    <EDRs>
     <MakeOffer status="6" bonusRuleID="119621" rewardActionID="191032" rewardTime="2023-09-   07T14:19:56" brandID="1" accountID="11712960353450032">
      <Parameter name="[LOOPBACK_RECOMMENDED_OFFER]">1</Parameter>
      <Products>
       <Product keyword="34861" id="22146"/>
       <Product keyword="35357" id="23146"/>
       <Product keyword="34841" id="22140"/>
       </Products>
      <RecommendationParameters>
       <ProductsNumber max="5"/>
       <Strategies>
        <Strategy name="5 RetSS 1 chain version_all MF Bands" id="16384"/>
       </Strategies>
      </RecommendationParameters>
      <ProductOfferingID>11990</ProductOfferingID>
      <UserIdentities>
       <UserIdentity triggered="1" type="EMAIL">[email protected]</UserIdentity>
      </UserIdentities>
      <UseCaseRunId>20662</UseCaseRunId>
      <UseCaseTypeId>5</UseCaseTypeId>
      <ScheduledCampaignID>62179</ScheduledCampaignID>
    </MakeOffer>
   </EDRs>

我尝试了几个查询，基于这个，你会看到我可以在一行中获得原始值（列“Keyword”结果<Product id="22146" keyword="34861"/><Product id="23146" keyword="35357"/><Product id="22140" keyword="34841"/>）或将所有三个结果合并为一个值（列“Keyword2”结果“348613535734841”）：

select x.* from VGRAMATOV.TEST_XML t, xmltable (
'MakeOfferEDRs/EDRs'
passing xmltype(t.xmltext) 
  columns   
     User_Identity                 XMLTYPE    path './MakeOffer/UserIdentities/UserIdentity',
     Product_Offering_ID           XMLTYPE    path './MakeOffer/ProductOfferingID',
     Keyword                       XMLTYPE    path './MakeOffer/Products/Product',
     Keyword2                      XMLTYPE    path './MakeOffer/Products/Product/@keyword'
        ) as x

oracle

来源：https://stackoverflow.com/questions/77092162/oracle-xml-many-nodes

1条答案

按热度按时间

o3imoua41#

解压缩到Products/Product中以获取每个关键字，然后返回层次结构以获取其他值：

SELECT x.*
FROM   TEST_XML t
       CROSS APPLY XMLTABLE(
         '/MakeOfferEDRs/EDRs/MakeOffer/Products/Product'
         PASSING XMLTYPE(t.xmltext)
         COLUMNS
           User_Identity       VARCHAR2(200) path './../../UserIdentities/UserIdentity',
           Product_Offering_ID NUMBER path './../../ProductOfferingID',
           Keyword             NUMBER path '@keyword'
       ) x

其中，对于样本数据：

CREATE TABLE test_xml (xmltext) AS
SELECT '<MakeOfferEDRs>
    <EDRs>
     <MakeOffer status="6" bonusRuleID="119621" rewardActionID="191032" rewardTime="2023-09-   07T14:19:56" brandID="1" accountID="11712960353450032">
      <Parameter name="[LOOPBACK_RECOMMENDED_OFFER]">1</Parameter>
      <Products>
       <Product keyword="34861" id="22146"/>
       <Product keyword="35357" id="23146"/>
       <Product keyword="34841" id="22140"/>
       </Products>
      <RecommendationParameters>
       <ProductsNumber max="5"/>
       <Strategies>
        <Strategy name="5 RetSS 1 chain version_all MF Bands" id="16384"/>
       </Strategies>
      </RecommendationParameters>
      <ProductOfferingID>11990</ProductOfferingID>
      <UserIdentities>
       <UserIdentity triggered="1" type="EMAIL">[email protected]</UserIdentity>
      </UserIdentities>
      <UseCaseRunId>20662</UseCaseRunId>
      <UseCaseTypeId>5</UseCaseTypeId>
      <ScheduledCampaignID>62179</ScheduledCampaignID>
    </MakeOffer>
   </EDRs>
</MakeOfferEDRs>' FROM DUAL;

输出：
| 用户标识|产品编号|关键字|
| --|--|--|
| email protected(https://stackoverflow.com/cdn-cgi/l/email-protection)| 11990 | 34861 |
| email protected(https://stackoverflow.com/cdn-cgi/l/email-protection)| 11990 | 35357 |
| email protected(https://stackoverflow.com/cdn-cgi/l/email-protection)| 11990 | 34841 |
fiddle

赞(0）回复(0）举报 2023-10-16

我来回答

Oracle XML多节点

1条答案

相关问题

热门标签

最新问答