如何在Oracle XML提取过程中为用户创建唯一的ID

发布于 2025-01-21 07:57:50 字数 4270 浏览 3 评论 0原文

我的Oracle DB中有Xmldata，我的Oracle DB中有不同的申请人。请注意，当申请人在XMLDATA中时，Appid是Oracle表中的一个字段（我在XML中有多个申请人），我想为申请人创建一个唯一的ID。在样本数据中，有3个申请人。我如何在选择语句中创建唯一的ID。

WITH t( xml ) AS
    (
    SELECT XMLType('<loanApplication xmlns="http://www.abcdef.com/Schema/FCX/1" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
     <applicantGroup>
         <applicantGroupTypeDd>0</applicantGroupTypeDd>
        <applicant>
          <asset>
            <assetDescription>neweg</assetDescription>
            <assetTypeDd>1</assetTypeDd>
            <assetValue>1500.0</assetValue>
          </asset>
          <asset>
            <assetDescription>RayM</assetDescription>
            <assetTypeDd>6</assetTypeDd>
            <assetValue>60000</assetValue>
          </asset>
          <asset>
            <assetDescription>TDC</assetDescription>
            <assetTypeDd>8</assetTypeDd>
            <assetValue>100</assetValue>
          </asset>
          <asset>
            <assetDescription>2007 Hyundai</assetDescription>
            <assetTypeDd>4</assetTypeDd>
            <assetValue>2500</assetValue>
          </asset>
       </applicant>
      </applicantGroup>
      <applicantGroup>
        <applicantGroupTypeDd>1</applicantGroupTypeDd>
        <applicant>
          <asset>
            <assetDescription>neweg</assetDescription>
            <assetTypeDd>2</assetTypeDd>
            <assetValue>15000.0</assetValue>
          </asset>
          <asset>
            <assetDescription>Bay</assetDescription>
            <assetTypeDd>6</assetTypeDd>
            <assetValue>60000</assetValue>
          </asset>
          <asset>
            <assetDescription>TDC</assetDescription>
            <assetTypeDd>9</assetTypeDd>
            <assetValue>100</assetValue>
          </asset>
          <asset>
            <assetDescription>2007 car</assetDescription>
            <assetTypeDd>3</assetTypeDd>
            <assetValue>2500</assetValue>
          </asset>
       </applicant>
      </applicantGroup>
      <applicantGroup>
        <applicantGroupTypeDd>3</applicantGroupTypeDd>
        <applicant>
          <asset>
            <assetDescription>neweg</assetDescription>
            <assetTypeDd>6</assetTypeDd>
            <assetValue>100.0</assetValue>
          </asset>
          <asset>
            <assetDescription>RayM</assetDescription>
            <assetTypeDd>8</assetTypeDd>
            <assetValue>60000</assetValue>
          </asset>
          <asset>
            <assetDescription>TDC</assetDescription>
            <assetTypeDd>7</assetTypeDd>
            <assetValue>100</assetValue>
          </asset>
          <asset>
            <assetDescription>2007 Hyundai</assetDescription>
            <assetTypeDd>5</assetTypeDd>
            <assetValue>2500</assetValue>
          </asset>
       </applicant>
      </applicantGroup>
        </loanApplication>')
     FROM dual
    )
    SELECT JSON_OBJECT (        
            KEY 'Assets' value y.Assets
            ) assets
    FROM t,
    XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.abcdef.com/Schema/FCX/1'), '/loanApplication/applicantGroup/applicant/asset'
          PASSING xml
          COLUMNS
                Assets INT PATH 'assetValue') y

结果，我需要

appid	pepersantId	assettydd
1	1	[1,6,8,4]
1	2	[1,2,6,9,3]
1	3	[3,6,8,7,5]

谢谢

原文

I have xmldata in my oracle DB, there are different applicants for a particular appID in my oracle DB. Note the appID is a field in the oracle table while the applicants are in the xmldata(I have multiple applicants in this xml) I would like to create a unique id for the applicants.
In the sample data, there are 3 applicants. how do I create unique ids in my select statement.

WITH t( xml ) AS
    (
    SELECT XMLType('<loanApplication xmlns="http://www.abcdef.com/Schema/FCX/1" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
     <applicantGroup>
         <applicantGroupTypeDd>0</applicantGroupTypeDd>
        <applicant>
          <asset>
            <assetDescription>neweg</assetDescription>
            <assetTypeDd>1</assetTypeDd>
            <assetValue>1500.0</assetValue>
          </asset>
          <asset>
            <assetDescription>RayM</assetDescription>
            <assetTypeDd>6</assetTypeDd>
            <assetValue>60000</assetValue>
          </asset>
          <asset>
            <assetDescription>TDC</assetDescription>
            <assetTypeDd>8</assetTypeDd>
            <assetValue>100</assetValue>
          </asset>
          <asset>
            <assetDescription>2007 Hyundai</assetDescription>
            <assetTypeDd>4</assetTypeDd>
            <assetValue>2500</assetValue>
          </asset>
       </applicant>
      </applicantGroup>
      <applicantGroup>
        <applicantGroupTypeDd>1</applicantGroupTypeDd>
        <applicant>
          <asset>
            <assetDescription>neweg</assetDescription>
            <assetTypeDd>2</assetTypeDd>
            <assetValue>15000.0</assetValue>
          </asset>
          <asset>
            <assetDescription>Bay</assetDescription>
            <assetTypeDd>6</assetTypeDd>
            <assetValue>60000</assetValue>
          </asset>
          <asset>
            <assetDescription>TDC</assetDescription>
            <assetTypeDd>9</assetTypeDd>
            <assetValue>100</assetValue>
          </asset>
          <asset>
            <assetDescription>2007 car</assetDescription>
            <assetTypeDd>3</assetTypeDd>
            <assetValue>2500</assetValue>
          </asset>
       </applicant>
      </applicantGroup>
      <applicantGroup>
        <applicantGroupTypeDd>3</applicantGroupTypeDd>
        <applicant>
          <asset>
            <assetDescription>neweg</assetDescription>
            <assetTypeDd>6</assetTypeDd>
            <assetValue>100.0</assetValue>
          </asset>
          <asset>
            <assetDescription>RayM</assetDescription>
            <assetTypeDd>8</assetTypeDd>
            <assetValue>60000</assetValue>
          </asset>
          <asset>
            <assetDescription>TDC</assetDescription>
            <assetTypeDd>7</assetTypeDd>
            <assetValue>100</assetValue>
          </asset>
          <asset>
            <assetDescription>2007 Hyundai</assetDescription>
            <assetTypeDd>5</assetTypeDd>
            <assetValue>2500</assetValue>
          </asset>
       </applicant>
      </applicantGroup>
        </loanApplication>')
     FROM dual
    )
    SELECT JSON_OBJECT (        
            KEY 'Assets' value y.Assets
            ) assets
    FROM t,
    XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.abcdef.com/Schema/FCX/1'), '/loanApplication/applicantGroup/applicant/asset'
          PASSING xml
          COLUMNS
                Assets INT PATH 'assetValue') y

Results, I need

AppId	applicantId	assetTypeDd
1	1	[1,6,8,4]
1	2	[1,2,6,9,3]
1	3	[3,6,8,7,5]

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

少女净妖师 2025-01-28 07:57:50

考虑Xpath的祖先轴，并计数pf 先前的sibling，因为它看起来一个申请人节点属于每个申请人：

WITH t( xml_data ) AS
    (
    SELECT XMLType('<loanApplication xmlns="http://www.abcdef.com/Schema/FCX/1" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
     <applicantGroup>
         <applicantGroupTypeDd>0</applicantGroupTypeDd>
        <applicant>
          <asset>
            <assetDescription>neweg</assetDescription>
            <assetTypeDd>1</assetTypeDd>
            <assetValue>1500.0</assetValue>
          </asset>
          <asset>
            <assetDescription>RayM</assetDescription>
            <assetTypeDd>6</assetTypeDd>
            <assetValue>60000</assetValue>
          </asset>
          <asset>
            <assetDescription>TDC</assetDescription>
            <assetTypeDd>8</assetTypeDd>
            <assetValue>100</assetValue>
          </asset>
          <asset>
            <assetDescription>2007 Hyundai</assetDescription>
            <assetTypeDd>4</assetTypeDd>
            <assetValue>2500</assetValue>
          </asset>
       </applicant>
      </applicantGroup>
      <applicantGroup>
        <applicantGroupTypeDd>1</applicantGroupTypeDd>
        <applicant>
          <asset>
            <assetDescription>neweg</assetDescription>
            <assetTypeDd>2</assetTypeDd>
            <assetValue>15000.0</assetValue>
          </asset>
          <asset>
            <assetDescription>Bay</assetDescription>
            <assetTypeDd>6</assetTypeDd>
            <assetValue>60000</assetValue>
          </asset>
          <asset>
            <assetDescription>TDC</assetDescription>
            <assetTypeDd>9</assetTypeDd>
            <assetValue>100</assetValue>
          </asset>
          <asset>
            <assetDescription>2007 car</assetDescription>
            <assetTypeDd>3</assetTypeDd>
            <assetValue>2500</assetValue>
          </asset>
       </applicant>
      </applicantGroup>
      <applicantGroup>
        <applicantGroupTypeDd>3</applicantGroupTypeDd>
        <applicant>
          <asset>
            <assetDescription>neweg</assetDescription>
            <assetTypeDd>6</assetTypeDd>
            <assetValue>100.0</assetValue>
          </asset>
          <asset>
            <assetDescription>RayM</assetDescription>
            <assetTypeDd>8</assetTypeDd>
            <assetValue>60000</assetValue>
          </asset>
          <asset>
            <assetDescription>TDC</assetDescription>
            <assetTypeDd>7</assetTypeDd>
            <assetValue>100</assetValue>
          </asset>
          <asset>
            <assetDescription>2007 Hyundai</assetDescription>
            <assetTypeDd>5</assetTypeDd>
            <assetValue>2500</assetValue>
          </asset>
       </applicant>
      </applicantGroup>
        </loanApplication>')
     FROM dual
    )

SELECT y.ApplicantId AS "applicantId",   
       LISTAGG(y.AssetTypeDd, ',') AS "assetTypeDd",
       LISTAGG(y.Assets, ',') AS "assets"
FROM t,
     XMLTABLE(
          XMLNAMESPACES('http://www.abcdef.com/Schema/FCX/1' AS "d",
                        DEFAULT 'http://www.abcdef.com/Schema/FCX/1'),
          '//d:asset'
          PASSING xml_data
          COLUMNS
                ApplicantId INT PATH 'count(ancestor::applicantGroup/preceding-sibling::*)+1',
                AssetTypeDd INT PATH 'assetTypeDd',
                Assets INT PATH 'assetValue'
    ) y
GROUP BY y.ApplicantId
ORDER BY y.ApplicantId

在线demo

Consider XPath's ancestor axis and count pf preceding-sibling since it appears one applicant node falls under each applicantGroup:

WITH t( xml_data ) AS
    (
    SELECT XMLType('<loanApplication xmlns="http://www.abcdef.com/Schema/FCX/1" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
     <applicantGroup>
         <applicantGroupTypeDd>0</applicantGroupTypeDd>
        <applicant>
          <asset>
            <assetDescription>neweg</assetDescription>
            <assetTypeDd>1</assetTypeDd>
            <assetValue>1500.0</assetValue>
          </asset>
          <asset>
            <assetDescription>RayM</assetDescription>
            <assetTypeDd>6</assetTypeDd>
            <assetValue>60000</assetValue>
          </asset>
          <asset>
            <assetDescription>TDC</assetDescription>
            <assetTypeDd>8</assetTypeDd>
            <assetValue>100</assetValue>
          </asset>
          <asset>
            <assetDescription>2007 Hyundai</assetDescription>
            <assetTypeDd>4</assetTypeDd>
            <assetValue>2500</assetValue>
          </asset>
       </applicant>
      </applicantGroup>
      <applicantGroup>
        <applicantGroupTypeDd>1</applicantGroupTypeDd>
        <applicant>
          <asset>
            <assetDescription>neweg</assetDescription>
            <assetTypeDd>2</assetTypeDd>
            <assetValue>15000.0</assetValue>
          </asset>
          <asset>
            <assetDescription>Bay</assetDescription>
            <assetTypeDd>6</assetTypeDd>
            <assetValue>60000</assetValue>
          </asset>
          <asset>
            <assetDescription>TDC</assetDescription>
            <assetTypeDd>9</assetTypeDd>
            <assetValue>100</assetValue>
          </asset>
          <asset>
            <assetDescription>2007 car</assetDescription>
            <assetTypeDd>3</assetTypeDd>
            <assetValue>2500</assetValue>
          </asset>
       </applicant>
      </applicantGroup>
      <applicantGroup>
        <applicantGroupTypeDd>3</applicantGroupTypeDd>
        <applicant>
          <asset>
            <assetDescription>neweg</assetDescription>
            <assetTypeDd>6</assetTypeDd>
            <assetValue>100.0</assetValue>
          </asset>
          <asset>
            <assetDescription>RayM</assetDescription>
            <assetTypeDd>8</assetTypeDd>
            <assetValue>60000</assetValue>
          </asset>
          <asset>
            <assetDescription>TDC</assetDescription>
            <assetTypeDd>7</assetTypeDd>
            <assetValue>100</assetValue>
          </asset>
          <asset>
            <assetDescription>2007 Hyundai</assetDescription>
            <assetTypeDd>5</assetTypeDd>
            <assetValue>2500</assetValue>
          </asset>
       </applicant>
      </applicantGroup>
        </loanApplication>')
     FROM dual
    )

SELECT y.ApplicantId AS "applicantId",   
       LISTAGG(y.AssetTypeDd, ',') AS "assetTypeDd",
       LISTAGG(y.Assets, ',') AS "assets"
FROM t,
     XMLTABLE(
          XMLNAMESPACES('http://www.abcdef.com/Schema/FCX/1' AS "d",
                        DEFAULT 'http://www.abcdef.com/Schema/FCX/1'),
          '//d:asset'
          PASSING xml_data
          COLUMNS
                ApplicantId INT PATH 'count(ancestor::applicantGroup/preceding-sibling::*)+1',
                AssetTypeDd INT PATH 'assetTypeDd',
                Assets INT PATH 'assetValue'
    ) y
GROUP BY y.ApplicantId
ORDER BY y.ApplicantId

Online Demo

回复收藏 0 原文

~没有更多了~