SQL Server: Row_Number() order by sort by xml data order

Question

I am pulling an XML data. i create temp table with it. but I want the sorting to be done according to the order of the xml data. how can I do it?

procedure:

ALTER PROCEDURE [dbo].[diziDonustur]
@date xml = null,
@islem xml = null
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @degisken int

    EXEC sp_xml_preparedocument @degisken OUTPUT, @date

    select 
    akt = ROW_NUMBER() OVER(ORDER BY cektar),
    cektar 
    into #tmp
    from cek 
    where cektar in (
      SELECT Convert(datetime,Convert(nvarchar,text)) FROM OPENXML (@degisken, '/ArrayOfDateTime/dateTime') where text is not null 
    )
    group by cektar

    
    
    exec sp_xml_removedocument @degisken;  



    EXEC sp_xml_preparedocument @degisken OUTPUT, @islem

    SELECT akt = ROW_NUMBER() OVER(ORDER BY Islem),Islem INTO #tmp4 from(
        SELECT Convert(decimal,Convert(nvarchar,text)) as Islem FROM OPENXML (@degisken, '/ArrayOfDecimal/decimal') where text is not null 
    )a--here I want to sort by xml data itself, not by operation


    
    select cektar,Islem into #tmpXmlBirlesim from #tmp inner join #tmp4 on #tmp.akt = #tmp4.akt
    select * from #tmpXmlBirlesim
END

run a procedure:

exec diziDonustur @islem = '<?xml version="1.0"?>
<ArrayOfDecimal xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <decimal>4000000</decimal>
  <decimal>343</decimal>
  <decimal>4000000</decimal>
</ArrayOfDecimal>', @date = '<?xml version="1.0"?>
<ArrayOfDateTime xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <dateTime>2021-12-18T00:00:00</dateTime>
  <dateTime>2021-12-19T00:00:00</dateTime>
  <dateTime>2021-12-20T00:00:00</dateTime>
</ArrayOfDateTime>'

When assigning row_number() in #tmp4, I am sorting by the Islem column. but then it sorts from the smallest number. I want to sort by the order from the XML, not by the Transaction column.

that is, in whatever order the XML data comes to me, it should be placed in the #tmp4 table in that order.

for example:

'<?xml version="1.0"?>
<ArrayOfDecimal xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <decimal>4000000</decimal> --should be number 1 in this table
  <decimal>343</decimal> --should be number 2 in this table
  <decimal>4000000</decimal> --should be number 3 in this table
</ArrayOfDecimal>'

Answer 1

Following the previous answer

declare @islem xml = '<?xml version="1.0"?>
<ArrayOfDecimal xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <decimal>4000000</decimal>
  <decimal>343</decimal>
  <decimal>4000000</decimal>
</ArrayOfDecimal>';

with Numbers(Number) as(
-- quick inline tally
 select 1 union all 
 select 2 union all 
 select 3 
)
 
select T.X.value('text()[1]', 'nvarchar(100)') as RowLabel,
       N.Number as RowNumber
from Numbers as N
cross apply @islem.nodes('ArrayOfDecimal/decimal[sql:column("N.Number")]') as T(X)
where N.Number between 1 and @islem.value('count(ArrayOfDecimal/decimal)', 'int');

You may wish to create a persistent Numbers tally table instead of quick sample in the query.