'How to return values in xml with different xpath
I am trying to parse xml in my oracle DB to return individual rows. I have different xpath the ,downPaymentSourceType INT PATH '/downPaymentSource/downPaymentSourceTypeDd' and ,DownpaymentAmount INT PATH '/downPaymentSource/amount' are returning nulls. How do I handle this to provide accurate results. Thanks
WITH t( xml ) AS
(
SELECT XMLType('<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.abcdef.com/Schema/FCX/1">C000312345</additionalData>
</deal>
<applicantGroup>
<applicantGroupTypeDd>0</applicantGroupTypeDd>
<applicant>
<employmentHistory>
<employerName>employer name</employerName>
<employmentHistoryStatusDd>0</employmentHistoryStatusDd>
<employmentHistoryTypeDd>0</employmentHistoryTypeDd>
<income>
<annualIncomeAmount>200000.0</annualIncomeAmount>
<incomeAmount>200000.0</incomeAmount>
<incomePeriodDd>0</incomePeriodDd>
<incomeTypeDd>6</incomeTypeDd>
</income>
</employmentHistory>
<asset>
<assetDescription>qwerqwer</assetDescription>
<assetTypeDd>0</assetTypeDd>
<assetValue>1111.0</assetValue>
</asset>
<liability>
<broker>
<creditLimit>9283.0</creditLimit>
<liabilityAmount>9999999.8</liabilityAmount>
<liabilityMonthlyPayment>299999.99</liabilityMonthlyPayment>
<liabilityTypeDd>2</liabilityTypeDd>
</broker>
</liability>
</applicant>
<applicant>
<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 'dealClosingDate' value x.DealClosingDate
,KEY 'dealPurpose' value x.dealPurpose
,KEY 'combinedGds' value x.combinedGds
,KEY 'combinedTds' value x.combinedTds
,KEY 'combinedLtv' value x.combinedLtv
,KEY 'downpaymentAmount' value x.DownpaymentAmount
,KEY 'combinedAssets' value y.CombinedAssets
,KEY 'combinedLiabilities' value y.CombinedLiabilities
,KEY 'combinedIncome' value y.CombinedIncome
,KEY 'downPaymentSourceType' value x.downPaymentSourceType
,KEY 'sourceAmount' value x.DownpaymentAmount
) Deal
FROM t,
XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.abcdef.com/Schema/FCX/1'), '/loanApplication/deal'
PASSING xml
COLUMNS
DealClosingDate timestamp with time zone PATH 'estimatedClosingDate'
,dealPurpose INT PATH 'dealPurposeDd'
,combinedGds INT PATH 'combinedGds'
,combinedTds INT PATH 'combinedTds'
,combinedLtv INT PATH 'combinedLtv'
,downPaymentSourceType INT PATH '/downPaymentSource/downPaymentSourceTypeDd'
,DownpaymentAmount INT PATH '/downPaymentSource/amount'
) x,
XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.abcdef.com/Schema/FCX/1'), '/loanApplication/applicantGroup/applicant'
PASSING xml
COLUMNS
CombinedLiabilities INT PATH 'liability/broker/liabilityAmount'
,CombinedIncome INT PATH 'employmentHistory/income/incomeAmount'
,CombinedAssets INT PATH 'asset/assetValue'
) y
Solution 1:[1]
You are cross-joining the base table and the two XMLTable calls. Your second XMLTable looks for applicantGroup nodes, and there aren't any in this XML document, so that gets now rows; and therefore the query overall returns no rows either.
If you change the second (implicit) cross join to outer apply then you'll still see the data found by the first XMLtable. It's unclear if there will always be a deal node, but you can make the first join an outer apply too.
...
FROM t
OUTER APPLY
XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.abcdef.com/Schema/FCX/1'), '/loanApplication/deal'
PASSING xml
COLUMNS
DealClosingDate timestamp with time zone PATH 'estimatedClosingDate'
,dealPurpose INT PATH 'dealPurposeDd'
,combinedGds INT PATH 'combinedGds'
,combinedTds INT PATH 'combinedTds'
,combinedLtv INT PATH 'combinedLtv'
,downPaymentSourceType INT PATH '/downPaymentSource/downPaymentSourceTypeDd'
,DownpaymentAmount INT PATH '/downPaymentSource/amount'
) x
OUTER APPLY
XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.abcdef.com/Schema/FCX/1'), '/loanApplication/applicantGroup/applicant'
PASSING xml
COLUMNS
CombinedLiabilities INT PATH 'liability/broker/liabilityAmount'
,CombinedIncome INT PATH 'employmentHistory/income/incomeAmount'
,CombinedAssets INT PATH 'asset/assetValue'
) y
DEAL
{"dealClosingDate":"2017-12-05T00:00:00.000000-05:00","dealPurpose":2,"combinedGds":0,"combinedTds":10,"combinedLtv":2,"downpaymentAmount":null,"combinedAssets":null,"combinedLiabilities":null,"combinedIncome":null,"downPaymentSourceType":null,"sourceAmount":null}
(I changed the dealClosingDate data type because... well, I could, and it didn't really look right as a string; but as it ends up as a string in the JSON anyway it's a bit moot.)
Depending on whether you can have multiple (or zero) deal nodes and/or multiple (or zero) applicantGroup nodes - presumably groups can have multiple child nodes - you might be able to structure the XMLTables a bit differently, but it depends on the data and what you want to see.
from your result, you can see that downPaymentSourceType and downpaymentAmount are retuning nulls which is not the case in the XML
That's because the path you are using for those starts with /downPaymentSource/... which doesn't exist within the XPath; and if you did ./downPaymentSource/... or just /downPaymentSource/... you would get a "expected singleton sequence - got multi-item sequence" error.
You can extract the collection of those downPaymentSource nodes as an XMLType, and then use another XMLTable to expand those too:
FROM t
OUTER APPLY
XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.abcdef.com/Schema/FCX/1'), '/loanApplication/deal'
PASSING xml
COLUMNS
DealClosingDate timestamp with time zone PATH 'estimatedClosingDate'
,dealPurpose INT PATH 'dealPurposeDd'
,combinedGds INT PATH 'combinedGds'
,combinedTds INT PATH 'combinedTds'
,combinedLtv INT PATH 'combinedLtv'
,downPayments XMLType PATH 'downPaymentSource'
-- ,downPaymentSourceType INT PATH '/downPaymentSource/downPaymentSourceTypeDd'
-- ,DownpaymentAmount INT PATH '/downPaymentSource/amount'
) x
OUTER APPLY
XMLTABLE(
XMLNAMESPACES(DEFAULT 'http://www.abcdef.com/Schema/FCX/1'), '/downPaymentSource'
PASSING x.downPayments
COLUMNS
downPaymentSourceType INT PATH 'downPaymentSourceTypeDd'
,DownpaymentAmount INT PATH 'amount'
) y
OUTER APPLY
XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.abcdef.com/Schema/FCX/1'), '/loanApplication/applicantGroup/applicant'
PASSING xml
COLUMNS
CombinedLiabilities INT PATH 'liability/broker/liabilityAmount'
,CombinedIncome INT PATH 'employmentHistory/income/incomeAmount'
,CombinedAssets INT PATH 'asset/assetValue'
) z
DEAL
{"dealClosingDate":"2017-12-05T00:00:00.000000-05:00","dealPurpose":2,"combinedGds":0,"combinedTds":10,"combinedLtv":2,"downpaymentAmount":25000,"combinedAssets":1111,"combinedLiabilities":10000000,"combinedIncome":200000,"downPaymentSourceType":1,"sourceAmount":25000}
{"dealClosingDate":"2017-12-05T00:00:00.000000-05:00","dealPurpose":2,"combinedGds":0,"combinedTds":10,"combinedLtv":2,"downpaymentAmount":25000,"combinedAssets":null,"combinedLiabilities":null,"combinedIncome":150000,"downPaymentSourceType":1,"sourceAmount":25000}
{"dealClosingDate":"2017-12-05T00:00:00.000000-05:00","dealPurpose":2,"combinedGds":0,"combinedTds":10,"combinedLtv":2,"downpaymentAmount":25000,"combinedAssets":1111,"combinedLiabilities":10000000,"combinedIncome":200000,"downPaymentSourceType":6,"sourceAmount":25000}
{"dealClosingDate":"2017-12-05T00:00:00.000000-05:00","dealPurpose":2,"combinedGds":0,"combinedTds":10,"combinedLtv":2,"downpaymentAmount":25000,"combinedAssets":null,"combinedLiabilities":null,"combinedIncome":150000,"downPaymentSourceType":6,"sourceAmount":25000}
{"dealClosingDate":"2017-12-05T00:00:00.000000-05:00","dealPurpose":2,"combinedGds":0,"combinedTds":10,"combinedLtv":2,"downpaymentAmount":50000,"combinedAssets":1111,"combinedLiabilities":10000000,"combinedIncome":200000,"downPaymentSourceType":10,"sourceAmount":50000}
{"dealClosingDate":"2017-12-05T00:00:00.000000-05:00","dealPurpose":2,"combinedGds":0,"combinedTds":10,"combinedLtv":2,"downpaymentAmount":50000,"combinedAssets":null,"combinedLiabilities":null,"combinedIncome":150000,"downPaymentSourceType":10,"sourceAmount":50000}
That still might not be what you want though; you might be trying to get a JSON array of those values, and maybe of the applicants too - depends if you're expecting 1, 2, or 6 JSON docs from this example XML doc. However, if you do want different JSON and can't figure out how to get there then you would need to ask a new question about that.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |