maybe someone can see the problem have this query :
select application_id, response_body, xx.*
from(
select application_id,
response_body
from stage_lt.cb_data_execution_entry_details deed, stage_lt.cb_data_execution_entry dee , stage_lt.cb_application_to_execution_entry aee
where deed.execution_entry_id=dee.id and aee.execution_entry_id=deed.execution_entry_id and dee.provider_name='cb'
and application_id = '2253545'
limit 1) x
cross join xmltable (
'/BatchResponse/Commands/Command/GenericReport/Data/ExistingContracts/Contract'
passing cast(x.response_body as xml)
columns
contract_key int path '@key',
amountr numeric path 'OutstandingAmountR',
average_amount numeric path 'AverageMonthlyInstalmentAmount') as xx
But this query get blanks for me
here is full direction to my results from xml tree
/responseContainer/ns2:Report/ns2:Registers/ns2:PositiveInfo/BatchResponse/Commands/Command/GenericReport/Data/ExistingContracts/Contract
Here is my full XML XMLTREE
my desired results to grooup by Contract key all application_id :
| ID | Contract key | OutstandingAmountR | AverageMonthlyInstalmentAmount |
|---|---|---|---|
| 2253545 | 1 | 12320.84 | 224.15 |
| 2253545 | 9 | 3914.13 | 74.96 |
| 2253545 | ... | ... | ... |