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></BILLCL>
<BILLDUE></BILLDUE>
<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
c.value('(BILLDATE /text())[1]', 'VARCHAR(20)') AS BILLDATE
, c.value('(BILLREF /text())[1]', 'VARCHAR(20)') AS BILLREF
, c.value('(BILLPARTY/text())[1]', 'VARCHAR(20)') AS BILLPARTY
, c.value('let $i := . let $nxt := (/ENVELOPE/BILLFIXED[. >> $i])[1] return (/ENVELOPE/BILLOP [. >> $i][. << $nxt]/text())[1]', 'INT') AS BILLOP
, c.value('let $i := . let $nxt := (/ENVELOPE/BILLFIXED[. >> $i])[1] return (/ENVELOPE/BILLCL [. >> $i][. << $nxt]/text())[1]', 'INT') AS BILLCL
, c.value('let $i := . let $nxt := (/ENVELOPE/BILLFIXED[. >> $i])[1] return (/ENVELOPE/BILLDUE [. >> $i][. << $nxt]/text())[1]', 'VARCHAR(20)') AS BILLDUE
, c.value('let $i := . let $nxt := (/ENVELOPE/BILLFIXED[. >> $i])[1] return (/ENVELOPE/BILLOVERDUE[. >> $i][. << $nxt]/text())[1]', 'INT') AS BILLOVERDUE
FROM @xml.nodes('/ENVELOPE/BILLFIXED') AS t(c);
| BILLDATE | BILLREF | BILLPARTY | BILLOP | BILLCL | BILLDUE | BILLOVERDUE |
|---|---|---|---|---|---|---|
| 29-Jun-2019 | 123 | ABC | 200 | null | null | null |
| 30-Jun-2019 | April To June -19 | efg | null | null | null | null |