Skip to Main Content

How to return a json array from xml data in oracle

User_D16LFApr 12 2022

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,

Comments
Post Details
Added on Apr 12 2022
0 comments
14 views