XMLTABLE get location to the variables

Viewed 18

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

Fiddle

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 ... ... ...
0 Answers
Related