SQL Server 2019

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

DECLARE @XML XML = '
<ENVELOPE>
  
    <DSPVCHDATE>16-4-2021</DSPVCHDATE>
    <DSPVCHITEMACCOUNT>PRASHANT MEHTA 359244</DSPVCHITEMACCOUNT>
    <DSPVCHTYPE>Sale</DSPVCHTYPE>
    <DSPINBLOCK>
      <DSPVCHINQTY></DSPVCHINQTY>
      <DSPVCHINAMT></DSPVCHINAMT>
    </DSPINBLOCK>
    <DSPOUTBLOCK>
      <DSPVCHOUTQTY>1 Pcs</DSPVCHOUTQTY>
      <DSPVCHNETTOUTAMT>23046.88</DSPVCHNETTOUTAMT>
    </DSPOUTBLOCK>
    <DSPCLBLOCK>
      <DSPVCHCLQTY></DSPVCHCLQTY>
      <DSPVCHCLAMT></DSPVCHCLAMT>
    </DSPCLBLOCK>
    <DSPEXPLVCHNUMBER>(No. :IV2612)</DSPEXPLVCHNUMBER>

<DSPVCHDATE>19-4-2021</DSPVCHDATE>
    <DSPVCHITEMACCOUNT>XYZ Company</DSPVCHITEMACCOUNT>
    <DSPVCHTYPE>Purchase</DSPVCHTYPE>
    <DSPINBLOCK>
      <DSPVCHINQTY>1 Pcs</DSPVCHINQTY>
      <DSPVCHINAMT>23437.50</DSPVCHINAMT>
    </DSPINBLOCK>
    <DSPOUTBLOCK>
      <DSPVCHOUTQTY></DSPVCHOUTQTY>
      <DSPVCHNETTOUTAMT></DSPVCHNETTOUTAMT>
    </DSPOUTBLOCK>
    <DSPCLBLOCK>
      <DSPVCHCLQTY>0 Pcs</DSPVCHCLQTY>
      <DSPVCHCLAMT></DSPVCHCLAMT>
    </DSPCLBLOCK>
    <DSPEXPLVCHNUMBER>(No. :IV2613)</DSPEXPLVCHNUMBER>
 
</ENVELOPE>';

WITH 
cte AS (Select REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), @XML, 1), N'<DSPVCHDATE>', '
  </ThisNode>
  <ThisNode>
    <DSPVCHDATE>'), N'</ENVELOPE>', N'
  </ThisNode>
</ENVELOPE>')  AS str)
SELECT @XML = CAST(STUFF(str, CHARINDEX(N'</ThisNode>', str), LEN(N'</ThisNode>'), N'') AS XML)
FROM cte
;
    
SELECT
      A.evnt.value('(DSPVCHDATE/text())[1]','nvarchar(100)') as DSPVCHDATE
       ,A.evnt.value('(DSPVCHITEMACCOUNT/text())[1]','nvarchar(100)') as DSPVCHITEMACCOUNT
       ,A.evnt.value('(DSPVCHTYPE/text())[1]','nvarchar(100)') as DSPVCHTYPE
       ,A.evnt.value('(DSPVCHITEMACCOUNT/text())[1]','nvarchar(100)') as DSPVCHITEMACCOUNT
       ,A.evnt.value('(DSPEXPLVCHNUMBER/text())[1]','nvarchar(100)') as DSPEXPLVCHNUMBER

,B.rec.value('(DSPVCHINQTY/text())[1]','nvarchar(100)') AS DSPVCHINQTY
     ,B.rec.value('(DSPVCHINAMT/text())[1]','nvarchar(100)') AS DSPVCHINAMT 
     ,C.rec.value('(DSPVCHOUTQTY/text())[1]','nvarchar(100)') AS DSPVCHOUTQTY 
     ,C.rec.value('(DSPVCHNETTOUTAMT/text())[1]','float') AS DSPVCHNETTOUTAMT 
       ,D.rec.value('(DSPVCHCLQTY/text())[1]','nvarchar(100)') AS DSPVCHCLQTY 
       ,D.rec.value('(DSPVCHCLAMT/text())[1]','int') AS DSPVCHCLAMT 
FROM @XML.nodes('/ENVELOPE/ThisNode') A(evnt)
OUTER APPLY A.evnt.nodes('DSPINBLOCK') B(rec)
OUTER APPLY A.evnt.nodes('DSPOUTBLOCK') C(rec)
OUTER APPLY A.evnt.nodes('DSPCLBLOCK') D(rec)

DSPVCHDATE	DSPVCHITEMACCOUNT	DSPVCHTYPE	DSPVCHITEMACCOUNT	DSPEXPLVCHNUMBER	DSPVCHINQTY	DSPVCHINAMT	DSPVCHOUTQTY	DSPVCHNETTOUTAMT	DSPVCHCLQTY	DSPVCHCLAMT
16-4-2021	PRASHANT MEHTA 359244	Sale	PRASHANT MEHTA 359244	(No. :IV2612)	null	null	1 Pcs	23046.88	null	null
19-4-2021	XYZ Company	Purchase	XYZ Company	(No. :IV2613)	1 Pcs	23437.50	null	null	0 Pcs	null