How to create unique id for users during extract from oracle xml-CodePudding

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

CodePudding user response：

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