By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @xml XML =
N'<ENVELOPE>
<BILLFIXED>
<BILLDATE>29-Jun-2019</BILLDATE>
<BILLREF>123</BILLREF>
<BILLPARTY>ABC</BILLPARTY>
</BILLFIXED>
<BILLOP>200</BILLOP>
<BILLCL>200</BILLCL>
<BILLDUE>29-Jun-2019</BILLDUE>
<BILLOVERDUE>1116</BILLOVERDUE>
<BILLFIXED>
<BILLDATE>30-Jun-2019</BILLDATE>
<BILLREF>April To June -19</BILLREF>
<BILLPARTY>efg</BILLPARTY>
</BILLFIXED>
<BILLOP>100</BILLOP>
<BILLCL>100</BILLCL>
<BILLDUE>30-Jun-2019</BILLDUE>
<BILLOVERDUE>1115</BILLOVERDUE>
</ENVELOPE>';
SELECT
f.value('(BILLDATE /text())[1]', 'VARCHAR(20)') AS BILLDATE
, f.value('(BILLREF /text())[1]', 'VARCHAR(20)') AS BILLREF
, f.value('(BILLPARTY /text())[1]', 'VARCHAR(20)') AS BILLPARTY
, c.value('(BILLOP /text())[1]', 'INT') AS BILLOP
, c.value('(BILLCL /text())[1]', 'INT') AS BILLCL
, c.value('(BILLDUE /text())[1]', 'VARCHAR(20)') AS BILLDUE
, c.value('(BILLOVERDUE/text())[1]', 'INT') AS BILLOVERDUE
FROM (VALUES(
@xml.query('
for $bf in /ENVELOPE/BILLFIXED
let $nxt := (/ENVELOPE/BILLFIXED[. >> $bf])[1]
return
<ENVELOPE>
| BILLDATE | BILLREF | BILLPARTY | BILLOP | BILLCL | BILLDUE | BILLOVERDUE |
|---|---|---|---|---|---|---|
| 29-Jun-2019 | 123 | ABC | 200 | 200 | 29-Jun-2019 | 1116 |
| 30-Jun-2019 | April To June -19 | efg | 100 | 100 | 30-Jun-2019 | 1115 |