add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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,
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.