How to split an XML string into parts with different keys

Nickname_used 0
Viewed 108

from XML column I wanna try get info.

Every client have more that one contract_key in XML, im using split_part and strpos for get needed information :

    select 
application_id,
response_body,
left(Contract_key, strpos(Contract_key, '"') - 1) as Contract_key,
left(AmountR, strpos(AmountR, '<') - 1) as OutstandingAmountR,
left(Avgmonth, strpos(Avgmonth, '<') - 1) as AverageMonthlyInstalmentAmount,
from(

select application_id, 
response_body, 
            split_part(response_body, '<OutstandingAmountR>', 2) AmountR,
            split_part(response_body, '<AverageMonthlyInstalmentAmount>', 2) Avgmonth,
            split_part(response_body, '<Contract key="', 2) as Contract_key,

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') x
;

But problem is this function return the first match found:

ID Key amountR AVGamount
2253545 1 10000 50

this client in same XML have more Contract_key:

ID Key amountR AVGamount
2253545 5 70000 35
2253545 7 8000 70
2253545 ... ... ...

My desired results, by ID get all keys from XML and what info after key until next key :

ID Key amountR AVGamount
2253545 1 10000 50
2253545 5 70000 35
2253545 7 8000 70
2253545 ... ... ...

HOW xml looks like : Contract key = 1 and Contract key = 9

XML link

Desired results from this example:

ID Contract key OutstandingAmountR AverageMonthlyInstalmentAmount
2253545 1 12320.84 224.15
2253545 9 3914.13 74.96
string split sql postgresql
1 Answers

There are a couple of errors in your xml sample data, first one there's no opening tag for </othercontracts> and this tag has a bad formed namespace definition <BatchResponse xmlns="http://katana"> should be like <BatchResponse xmlns:ns3="http://katana"> (namespace=something) and that prevents document navigation.

Solving that https://codebeautify.org/xmlviewer/y22fbb8e2 you should treat xml data as such, not using text functions but xml fuctions, so using xmltable function is very simple to query your data, here an example

Having

create table public.sample (
    id int4 NULL,
    response_body xml NULL
);

insert into sample values (1, '<responseContainer xmlns:ns2="http://ws.nGCR">
    <ns2:Header>
        <ns2:RequestId>11480644</ns2:RequestId> ... );

Query should look like

select xmltable.* from
xmltable(xmlnamespaces('http://ws.nGCR' AS ns2),
'/responseContainer/ns2:Report/ns2:Registers/ns2:PositiveInfo/BatchResponse/Commands/Command/GenericReport/Data/ExistingContracts/Contract'
  passing (select response_body from sample) 
  columns key text path '@key',
  OutstandingAmountR float8 path 'OutstandingAmountR/text()',
  AverageMonthlyInstalmentAmount float8 path 'AverageMonthlyInstalmentAmount/text()' );

Using xpaths to reach desired data.

And result

enter image description here

in documentation https://www.postgresql.org/docs/current/functions-xml.html

Pepe N O0
Related