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,