Oracle XML 多个节点-解网

问：

我在表 TEST_XML 中有以下 XML，其中一列“XMLTEXT”类型为 CLOB。

我希望在最后收到以下类似表格的结果：

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>

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

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 XML 解析

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;

输出：

USER_IDENTITY	PRODUCT_OFFERING_ID	关键词
[电子邮件保护]	11990	34861
[电子邮件保护]	11990	35357
[电子邮件保护]	11990	34841

小提琴

Oracle XML 多个节点

Oracle XML many nodes

评论

评论