[SOLVED] How to reach second prefix in xml using posgresSQL

How to reach second prefix in xml using posgresSQL

the problem is that I need to extract data from xml, I know how to extract them, but I can't pass prefixes, maybe you can help

   <ns2:PositiveInfo xmlns:ns2="http://ws.nGCR">
                <BatchResponse xmlns="http://katana">
                    <Header>
                        <BatchId>11480644</BatchId>
                        <State>Finished</State>
                        <BeginTimeStamp>2022-09-10T10:21:48Z</BeginTimeStamp>
                        <TimeStamp>2022-09-10T10:21:50Z</TimeStamp>
                        <FinishTimeStamp>2022-09-10T10:21:50Z</FinishTimeStamp>
                        <Duration>2.3571</Duration>
                        <Identifier>600e19f5cc5b4707944b126cc8f6103a</Identifier>
                        <Subscriber>2810192</Subscriber>
    .....

Now im in PositiveInfo prefix, how to reach BatchResponse prefix?

until now I have this query :

    select *
from(
select unnest(xpath('/responseContainer/ns2:Report/ns2:Registers/ns2:PositiveInfo',  
       response_body::XML,  
       array[array['ns2','http://ws.nGCR']]))::XML as test
from stage_lt.cb_data_execution_entry_details deed
where  id = 178752351)xx

Solution

To select the BatchResponse element, whose name is in the http://katana namespace, you'll need to bind another prefix (e.g. katana) to the namespace URI http://katana, just as you have bound the ns2 prefix to the namespace URI http://ws.nGCR, then you can use katana: as a namespace prefix in your XPath expression, e.g. /responseContainer/ns2:Report/ns2:Registers/ns2:PositiveInfo/katana:BatchResponse