如何从 Oracle 中的 xml 数据创建嵌套的 json 对象

Question

我正在尝试解析oracle数据库中的xml以返回单个json对象。我有不同的路径。我想以嵌套的 json 格式从 oracle xml 中提取数据。我的 xml 数据中有多个申请人，我想将数据提取为嵌套的 json 格式，以了解哪些数据属于申请人 结果格式示例

{
"loanApplication": {
"applicantGroup": [
{
"applicant": {
"birthDate": "1-1-1",
"maritalStatusDd": "3",
"languagePreferenceDd": "0",
"assetTypeDd": [1, 6],
"asset": [1500, 60000],
"Liabilities": [500, 600, 400],
"sumOfAsset": 61500,
"sumOfliabilities": 1500
}
},
{
"applicant": {
"birthDate": "2-2-2",
"maritalStatusDd": "0",
"languagePreferenceDd": "0",
"assetTypeDd": [2, 6, 9],
"asset": [5000, 20000, 100],
"Liabilities": [500, 600, 400],
"sumOfAsset": 25100,
"sumOfliabilities": 1500
}
}
]
}
}

来自 XML 的示例数据

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>
<liability>
<broker>
<liabilityAmount>9999999.8</liabilityAmount>
<liabilityDescription>CI</liabilityDescription>
<liabilityTypeDd>2</liabilityTypeDd>
</broker>
</liability>
<employmentHistory>
<income>
<annualIncomeAmount>150000.0</annualIncomeAmount>
<incomeAmount>150000.0</incomeAmount>
<incomeDescription>income description</incomeDescription>
<incomePeriodDd>0</incomePeriodDd>
<incomeTypeDd>6</incomeTypeDd>
</income>
</employmentHistory>
</applicant>
</applicantGroup>
<applicantGroup>
<applicantGroupTypeDd>1</applicantGroupTypeDd>
<applicant>
<asset>
<assetDescription>neweg</assetDescription>
<assetTypeDd>2</assetTypeDd>
<assetValue>5000.0</assetValue>
</asset>
<asset>
<assetDescription>Bay</assetDescription>
<assetTypeDd>6</assetTypeDd>
<assetValue>20000</assetValue>
</asset>
<asset>
<assetDescription>TDC</assetDescription>
<assetTypeDd>9</assetTypeDd>
<assetValue>100</assetValue>
</asset>
<liability>
<broker>
<liabilityAmount>9999999.8</liabilityAmount>
<liabilityDescription>CI</liabilityDescription>
<liabilityTypeDd>2</liabilityTypeDd>
</broker>
</liability>
<liability>
<broker>
<liabilityAmount>9999999.8</liabilityAmount>
<liabilityDescription>CI</liabilityDescription>
<liabilityTypeDd>2</liabilityTypeDd>
</broker>
</liability>
<employmentHistory>
<income>
<annualIncomeAmount>150000.0</annualIncomeAmount>
<incomeAmount>150000.0</incomeAmount>
<incomeDescription>income description</incomeDescription>
<incomePeriodDd>0</incomePeriodDd>
<incomeTypeDd>6</incomeTypeDd>
</income>
</employmentHistory>
</applicant>
</applicantGroup>
</loanApplication>')
FROM dual
)
SELECT JSON_OBJECT (        
KEY 'Assets' value y.Assets
,KEY 'assetType' VALUE (SELECT JSON_ARRAYAGG( val) FROM   
XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.abcdef.com/Schema/FCX/1'),'/loanApplication/applicantGroup/applicant/asset/assetTypeDd'
PASSING (EXTRACT(xml, '/loanApplication', 'xmlns="http://www.abcdef.com/Schema/FCX/1"') ) COLUMNS val INT PATH './text()') )
,KEY 'liability' VALUE (SELECT JSON_ARRAYAGG( val) FROM   
XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.abcdef.com/Schema/FCX/1'),'/loanApplication/applicantGroup/applicant/liability/broker/BorrowerLiabilityType'
PASSING (EXTRACT(xml, '/loanApplication', 'xmlns="http://www.abcdef.com/Schema/FCX/1"') ) COLUMNS val INT PATH './text()') )
) applicant
FROM t,
XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.abcdef.com/Schema/FCX/1'), '/loanApplication/applicantGroup/applicant/asset'
PASSING xml
COLUMNS
Assets INT PATH 'assetValue') y

Answer 1

您可以从按applicantGroupTypeDd分组开始，以便分离最里面的对象(applicant)，然后继续使用合适的功能(JSON_OBJECT或JSON_ARRAYAGG)到最顶层，例如

WITH t(xml) AS
(SELECT XMLType(<yourXMLvalue>)
FROM dual)
SELECT JSON_OBJECT(KEY 'loanApplication' VALUE
JSON_OBJECT(KEY 'applicantGroup' VALUE
JSON_ARRAYAGG(JSON_OBJECT(KEY 'applicant'
VALUE JSON_OBJECT(KEY 'assetTypeDd' VALUE JSON_ARRAYAGG(assetTypeDd),
     KEY 'asset'       VALUE JSON_ARRAYAGG(assetValue),
     KEY 'sumOfAsset'  VALUE SUM(assetValue))))))
FROM t,
XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.abcdef.com/Schema/FCX/1'),
'/loanApplication/applicantGroup' PASSING t.xml COLUMNS
applicantGroupTypeDd INT PATH 'applicantGroupTypeDd',
asset XMLTYPE PATH 'applicant/asset') t1,
XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.abcdef.com/Schema/FCX/1'),
'asset' PASSING t1.asset COLUMNS 
assetTypeDd INT PATH 'assetTypeDd',
assetValue  INT PATH 'assetValue') t2
GROUP BY applicantGroupTypeDd

演示