Forum Stats

  • 3,826,853 Users
  • 2,260,713 Discussions
  • 7,897,102 Comments

Discussions

How to return a json array from xml data in oracle

I have a table in oracle where the xml data is tied to unique subId and AppId

| SubID | AppID | msg   |

| ------ | ----- |--------|

| 1     | 11   | xmldata|

| 2     | 12   | xmldata|

| 3     | 13   | xmldata|


In xmldata, I have applicantGroup node which contains multiple applicants, the first applicant in this node could have multiple assets,liabilities and incomes etc, same for the other applicants in this xmldata. The data in the xml ties bank to the unique subId and appId. How can I return a json output with arrays of the applicant information.

Sample xmldata for sub1

```

<loanApplication xmlns="http://www.abcdef.com/Schema/FCX/1"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 <deal>

   <applicationDate>2017-01-10T16:15:00-05:00</applicationDate>

   <applicationId>QA-8899</applicationId>

   <combinedGds>0.13055</combinedGds>

   <combinedLtv>1.8</combinedLtv>

   <combinedTds>10.2714</combinedTds>

   <dealPurposeDd>2</dealPurposeDd>

   <dealTypeDd>0</dealTypeDd>

   <downPaymentSource>

     <amount>25000.0</amount>

     <downPaymentSourceTypeDd>1</downPaymentSourceTypeDd>

     <description>aasdadsf</description>

   </downPaymentSource>

   <downPaymentSource>

     <amount>25000.0</amount>

     <downPaymentSourceTypeDd>6</downPaymentSourceTypeDd>

     <description>wrewertwerewrt</description>

   </downPaymentSource>

   <downPaymentSource>

     <amount>50000.0</amount>

     <downPaymentSourceTypeDd>10</downPaymentSourceTypeDd>

     <description>second and thirs</description>

   </downPaymentSource>

   <estimatedClosingDate>2017-12-05T00:00:00-05:00</estimatedClosingDate>

   <financingWaiverDate>2017-01-04T00:00:00-05:00</financingWaiverDate>

   <refiImprovementAmount>10000.0</refiImprovementAmount>

   <refiImprovementsDesc>roof</refiImprovementsDesc>

   <refiImprovementsFlag>Y</refiImprovementsFlag>

   <refiPurpose>refi purpose</refiPurpose>

   <taxPayorDd>1</taxPayorDd>

   <additionalData dataType="String" name="firmLicenseRegistrationNumber" xmlns:t="http://www.filogix.com/Schema/FCX/1">C000312345</additionalData>

 </deal>

 <applicantGroup>

   <applicantGroupTypeDd>0</applicantGroupTypeDd>

   <applicant>

     <employmentHistory>

       <employerName>employer name</employerName>

       <employmentHistoryStatusDd>1</employmentHistoryStatusDd>

       <employmentHistoryTypeDd>2</employmentHistoryTypeDd>

       <income>

         <annualIncomeAmount>300000.0</annualIncomeAmount>

         <incomeAmount>300000.0</incomeAmount>

         <incomePeriodDd>0</incomePeriodDd>

         <incomeTypeDd>3</incomeTypeDd>

       </income>

     </employmentHistory>

     <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>

     <liability>

       <broker>

         <creditLimit>9283.0</creditLimit>

         <liabilityAmount>9999999.8</liabilityAmount>

         <liabilityMonthlyPayment>299999.99</liabilityMonthlyPayment>

         <liabilityTypeDd>2</liabilityTypeDd>

       </broker>

     </liability>

    <liability>

       <broker>

         <creditLimit>10000</creditLimit>

         <liabilityAmount>1000</liabilityAmount>

         <liabilityMonthlyPayment>20</liabilityMonthlyPayment>

         <liabilityTypeDd>2</liabilityTypeDd>

       </broker>

     </liability>

</applicant>

<applicant>

     <employmentHistory>

       <employerName>employer1</employerName>

       <employmentHistoryStatusDd>1</employmentHistoryStatusDd>

       <employmentHistoryTypeDd>2</employmentHistoryTypeDd>

       <income>

         <annualIncomeAmount>500000.0</annualIncomeAmount>

         <incomeAmount>500000.0</incomeAmount>

         <incomePeriodDd>0</incomePeriodDd>

         <incomeTypeDd>3</incomeTypeDd>

       </income>

     </employmentHistory>

     <asset>

       <assetDescription>oldeg</assetDescription>

       <assetTypeDd>1</assetTypeDd>

       <assetValue>1500.0</assetValue>

     </asset>

    <asset>

       <assetDescription>RM</assetDescription>

       <assetTypeDd>6</assetTypeDd>

       <assetValue>60000</assetValue>

     </asset>

     <asset>

       <assetDescription>DCT</assetDescription>

       <assetTypeDd>8</assetTypeDd>

       <assetValue>100</assetValue>

     </asset>

     <asset>

       <assetDescription>2007 Hyundai</assetDescription>

       <assetTypeDd>4</assetTypeDd>

       <assetValue>2500</assetValue>

     </asset>

     <liability>

       <broker>

         <creditLimit>90283.0</creditLimit>

         <liabilityAmount>20000</liabilityAmount>

         <liabilityMonthlyPayment>100</liabilityMonthlyPayment>

         <liabilityTypeDd>2</liabilityTypeDd>

       </broker>

     </liability>

    <liability>

       <broker>

         <creditLimit>10000</creditLimit>

         <liabilityAmount>1000</liabilityAmount>

         <liabilityMonthlyPayment>20</liabilityMonthlyPayment>

         <liabilityTypeDd>2</liabilityTypeDd>

       </broker>

     </liability>

</applicant>

 </applicantGroup>

</loanApplication>

```


Expected output is something like this


```{

   "subId" : 1,

   "appId" : 21,


   "applicantArray" :

   {

     "applicant1" : [ { "asset" : 1500, "liabilities" : 9999999.8, "income" : 300000},

                  {"asset" : 60000, "liabilities" : 1000,"income" : null},

                       { "asset" : 100, "liabilities" : 100, "income" : null}

               ],


    "applicant2" : [ { "asset" : 11500, "liabilities" : 20000, "income" : 500000},

                  {"asset" : 60000, "liabilities" : 1000,"income" : null},

                       { "asset" : 100, "liabilities" : 100, "income" : null}

               ]

   }

}

```

code I have tried

```

SELECT

       JSON_OBJECT (

           KEY 'subId' value t.subId

           ,KEY 'appId' value t.appId    

           ,KEY 'Assets' value y.Assets

           ,KEY 'Liabilities' value y.Liabilities

           ,KEY 'Income' value y.Income

           ) sample

   FROM tabledata t,

        XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.abcdef.com/Schema/FCX/1'), '/loanApplication/applicantGroup/applicant'

         PASSING xml

         COLUMNS

           Liabilities INT PATH 'liability/broker/liabilityAmount'

           ,Income INT PATH 'employmentHistory/income/incomeAmount'

           ,Assets INT PATH 'asset/assetValue'

           ) y

```

I will really appreciate your help,