SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

declare @xml
xml= '<ENVELOPE>'+
replicate( cast('
    <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>' as nvarchar(max)), 100) +'
</ENVELOPE>';

select 
  max(case when col = 'BILLDATE' then val end) as BILLDATE,
  max(case when col = 'BILLREF' then val end) as BILLREF,
  max(case when col = 'BILLPARTY' then val end) as BILLPARTY,
  max(case when col = 'BILLOP' then val end) as BILLOP,
  max(case when col = 'BILLCL' then val end) as BILLCL,
  max(case when col = 'BILLDUE' then val end) as BILLPARTY,
  max(case when col = 'BILLOVERDUE' then val end) as BILLOVERDUE
from
(
select 
  b.b.value('local-name(.)', 'nvarchar(200)') as col, b.b.value('text()[1]', 'nvarchar(200)') as val,
  sum(case when b.b.value('local-name(.)', 'nvarchar(200)') = 'BILLFIXED' then 1 else 0 end) 
  over(order by b.b rows unbounded preceding) as grp
from @xml.nodes('/ENVELOPE//*') as b(b)
) as x
group by grp;

BILLDATE	BILLREF	BILLPARTY	BILLOP	BILLCL	BILLPARTY	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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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

Warning: Null value is eliminated by an aggregate or other SET operation.