Почему запрос выдает отчет с перекрестными значениями как с cross join?
нужно чтоб было 4 записи а выдает аж 16
SELECT
b.PAYMENT_ID,
b.DESTINATION_ID,
b.PAY_SUM,
b.COMMISSION,
b.PAY_DATE,
s.EXTERNAL,
s.ADR,
s.FIO,
s.PASSPORT,
s.COUNTER1,
s.COUNTER2,
s.COUNTER3
FROM
(SELECT
PAYMENT_ID,
DESTINATION_ID,
PAY_SUM,
COMMISSION,
PAY_DATE,
EXTERNAL,
ADR,
FIO,
PASSPORT,
COUNTER1,
COUNTER2,
COUNTER3
FROM XMLTABLE('/XML/request/payments/payment/user_data'
PASSING XMLTYPE('<?xml version="1.0" standalone="yes"?>
<XML>
<request type="PAYMENT">
<pack>
<pack_Id>7065528</pack_Id>
<cash>2300.00</cash>
<summ>2290.00</summ>
<balance>10.00</balance>
</pack>
<payments>
<payment>
<payment_id>12345</payment_id>
<destination_Id>21523</destination_Id>
<pay_sum>118.12</pay_sum>
<commission>30.00</commission>
<pay_date>2023-01-16 10:21:06.290</pay_date>
<user_data>
<external>30001625-093558</external>
<adr>МАГНИТОГОРСК, Лермонтова, 104, 005</adr>
<fio>ДОБРОЛЮБОВА ТАМАРА АЛЕКСАНДРОВНА</fio>
<pasp/>
<counter1/>
<counter2/>
</user_data>
</payment>
<payment>
<payment_id>12346</payment_id>
<destination_Id>9582</destination_Id>
<pay_sum>103.00</pay_sum>
<commission>0.00</commission>
<pay_date>2023-01-16 10:21:06.283</pay_date>
<user_data>
<external>60306747</external>
<adr>МАГНИТОГОРСК, Лермонтова, 104, 5</adr>
<fio>ДОБРОЛЮБОВА ТАМАРА АЛЕКСАНДРОВНА</fio>
<pasp/>
<counter1>0</counter1>
<counter2/>
</user_data>
</payment>
<payment>
<payment_id>12347</payment_id>
<reverse_payment_id/>
<org_id>131</org_id>
<destination_Id>15781</destination_Id>
<pay_sum>558.88</pay_sum>
<commission>0.00</commission>
<pay_date>2023-01-16 10:21:06.280</pay_date>
<user_data>
<external>170202147</external>
<adr>МАГНИТОГОРСК, Лермонтова, 104, 5</adr>
<fio>ДОБРОЛЮБОВА ТАМАРА АЛЕКСАНДРОВНА</fio>
<pasp/>
<counter1/>
<counter2/>
</user_data>
</payment>
<payment>
<payment_id>12348</payment_id>
<destination_Id>9582</destination_Id>
<pay_sum>1480.00</pay_sum>
<commission>0.00</commission>
<pay_date>2023-01-16 10:21:06.273</pay_date>
<user_data>
<external>60005470</external>
<adr>МАГНИТОГОРСК, УЛ 25 ЛЕТ ОКТЯБРЯ, 20, </adr>
<fio>ДОБРОЛЮБОВА ТАМАРА АЛЕКСАНДРОВНА</fio>
<pasp/>
<counter1>0</counter1>
<counter2/>
<counter3/>
</user_data>
</payment>
</payments>
</request>
</XML>
')
COLUMNS
PAYMENT_ID number PATH 'payment_id',
DESTINATION_ID number PATH 'destination_Id',
PAY_SUM number PATH 'pay_sum',
COMMISSION number PATH 'commission',
PAY_DATE VARCHAR2(100) PATH 'pay_date',
EXTERNAL VARCHAR2(100) PATH 'external',
ADR VARCHAR2(100) PATH 'adr',
FIO VARCHAR2(100) PATH 'fio',
PASSPORT VARCHAR2(20) PATH 'pasp',
COUNTER1 NUMBER PATH 'counter1',
COUNTER2 NUMBER PATH 'counter2',
COUNTER3 NUMBER PATH 'counter3') a
) s,
(SELECT
PAYMENT_ID,
DESTINATION_ID,
PAY_SUM,
COMMISSION,
PAY_DATE,
EXTERNAL,
ADR,
FIO,
PASSPORT,
COUNTER1,
COUNTER2,
COUNTER3
FROM XMLTABLE('/XML/request/payments/payment'
PASSING XMLTYPE('<?xml version="1.0" standalone="yes"?>
<XML>
<request type="PAYMENT">
<pack>
<pack_Id>7065528</pack_Id>
<cash>2300.00</cash>
<summ>2290.00</summ>
<balance>10.00</balance>
</pack>
<payments>
<payment>
<payment_id>12345</payment_id>
<destination_Id>21523</destination_Id>
<pay_sum>118.12</pay_sum>
<commission>30.00</commission>
<pay_date>2023-01-16 10:21:06.290</pay_date>
<user_data>
<external>30001625-093558</external>
<adr>МАГНИТОГОРСК, Лермонтова, 104, 005</adr>
<fio>ДОБРОЛЮБОВА ТАМАРА АЛЕКСАНДРОВНА</fio>
<pasp/>
<counter1/>
<counter2/>
</user_data>
</payment>
<payment>
<payment_id>12346</payment_id>
<destination_Id>9582</destination_Id>
<pay_sum>103.00</pay_sum>
<commission>0.00</commission>
<pay_date>2023-01-16 10:21:06.283</pay_date>
<user_data>
<external>60306747</external>
<adr>МАГНИТОГОРСК, Лермонтова, 104, 5</adr>
<fio>ДОБРОЛЮБОВА ТАМАРА АЛЕКСАНДРОВНА</fio>
<pasp/>
<counter1>0</counter1>
<counter2/>
</user_data>
</payment>
<payment>
<payment_id>12347</payment_id>
<reverse_payment_id/>
<org_id>131</org_id>
<destination_Id>15781</destination_Id>
<pay_sum>558.88</pay_sum>
<commission>0.00</commission>
<pay_date>2023-01-16 10:21:06.280</pay_date>
<user_data>
<external>170202147</external>
<adr>МАГНИТОГОРСК, Лермонтова, 104, 5</adr>
<fio>ДОБРОЛЮБОВА ТАМАРА АЛЕКСАНДРОВНА</fio>
<pasp/>
<counter1/>
<counter2/>
</user_data>
</payment>
<payment>
<payment_id>12348</payment_id>
<destination_Id>9582</destination_Id>
<pay_sum>1480.00</pay_sum>
<commission>0.00</commission>
<pay_date>2023-01-16 10:21:06.273</pay_date>
<user_data>
<external>60005470</external>
<adr>МАГНИТОГОРСК, УЛ 25 ЛЕТ ОКТЯБРЯ, 20, </adr>
<fio>ДОБРОЛЮБОВА ТАМАРА АЛЕКСАНДРОВНА</fio>
<pasp/>
<counter1>0</counter1>
<counter2/>
<counter3/>
</user_data>
</payment>
</payments>
</request>
</XML>
')
COLUMNS
PAYMENT_ID number PATH 'payment_id',
DESTINATION_ID number PATH 'destination_Id',
PAY_SUM number PATH 'pay_sum',
COMMISSION number PATH 'commission',
PAY_DATE VARCHAR2(100) PATH 'pay_date',
EXTERNAL VARCHAR2(100) PATH 'external',
ADR VARCHAR2(100) PATH 'adr',
FIO VARCHAR2(100) PATH 'fio',
PASSPORT VARCHAR2(20) PATH 'pasp',
COUNTER1 NUMBER PATH 'counter1',
COUNTER2 NUMBER PATH 'counter2',
COUNTER3 NUMBER PATH 'counter3') );
Источник: Stack Overflow на русском