Row numbers when shredding XML in SQL Server

Question

I'm trying to add row numbers in the DAY_VALUE column that reset after each inventorymodel. I have a start, but I can't them to reset or count up instead of having ties. It would look something like this:

DEMANDPERCENT	INVENTORYMODEL_ID	DAY_VALUE	STOCKPERDAY	SUPPLY
15.0000000953	Default	1	0	0
10	Default	2	0	1
11	Default	3	0	0
14.2857151031	Test	1	0	0
14.2857151031	Test	2	0	0

INSERT INTO #SYS_MYS_INVENTORYMODELDAYS(
    DEMANDPERCENT,
    INVENTORYMODEL_ID,
    DAY_VALUE,
    STOCKPERDAY,
    SUPPLY
)
SELECT
    InvModels.inventorymodelday.query('DemandPercent').value('.', 'float'),
    InvModel.inventorymodel.value('@id', 'nvarchar(255)'),
    InvModel.inventorymodel.value('let $i := . return count(/inventorymodels/inventorymodel/inventorymodeldays/inventorymodelday[. << $i]) + 1', 'int'),
    InvModels.inventorymodelday.query('StocksPerDay').value('.', 'int'),
    InvModels.inventorymodelday.query('Supply').value('.', 'int')
FROM(
    SELECT CAST(InvModels AS xml)
    FROM OPENROWSET(
        BULK 'C:\Users\owjo1001\AppData\Local\Nielsen\Spaceman\Config\InvModels.xml', SINGLE_BLOB) AS T(InvModels)
) AS T(InvModels)
CROSS APPLY InvModels.nodes('inventorymodels/inventorymodel') AS InvModel(inventorymodel)
CROSS APPLY InvModel.inventorymodel.nodes('inventorymodeldays/inventorymodelday') AS InvModels(inventorymodelday)

  <inventorymodel id="Default">
    <inventorymodeldays count="7">
      <inventorymodelday>
        <DemandPercent>15.0000009536743</DemandPercent>
        <StocksPerDay>0</StocksPerDay>
        <Supply>0</Supply>
      </inventorymodelday>
      <inventorymodelday>
        <DemandPercent>10</DemandPercent>
        <StocksPerDay>0</StocksPerDay>
        <Supply>1</Supply>
      </inventorymodelday>
      <inventorymodelday>
        <DemandPercent>11</DemandPercent>
        <StocksPerDay>0</StocksPerDay>
        <Supply>0</Supply>
      </inventorymodelday>
      <inventorymodelday>
        <DemandPercent>12</DemandPercent>
        <StocksPerDay>0</StocksPerDay>
        <Supply>0</Supply>
      </inventorymodelday>
      <inventorymodelday>
        <DemandPercent>13</DemandPercent>
        <StocksPerDay>0</StocksPerDay>
        <Supply>0</Supply>
      </inventorymodelday>
      <inventorymodelday>
        <DemandPercent>19</DemandPercent>
        <StocksPerDay>1</StocksPerDay>
        <Supply>1</Supply>
      </inventorymodelday>
      <inventorymodelday>
        <DemandPercent>20</DemandPercent>
        <StocksPerDay>0</StocksPerDay>
        <Supply>0</Supply>
      </inventorymodelday>
    </inventorymodeldays>
    <MovementStores>1</MovementStores>
    <MovementDays>7</MovementDays>
    <SafetyStockType>0</SafetyStockType>
    <SafetyStockDays>0</SafetyStockDays>
    <SafetyStockPercent>0</SafetyStockPercent>
    <UseDemandVariance>0</UseDemandVariance>
    <DemandVariance>0</DemandVariance>
    <UseCaseMultiple>1</UseCaseMultiple>
    <CaseMultiple>1.25</CaseMultiple>
    <UseManualInventory>0</UseManualInventory>
    <SetAsDefault>1</SetAsDefault>
    <HistoricalServiceLevel>0.899999976158142</HistoricalServiceLevel>
    <TargetServiceLevel>0.949999988079071</TargetServiceLevel>
  </inventorymodel>
  <inventorymodel id="Test">
    <inventorymodeldays count="7">
      <inventorymodelday>
        <DemandPercent>14.2857151031494</DemandPercent>
        <StocksPerDay>0</StocksPerDay>
        <Supply>0</Supply>
      </inventorymodelday><inventorymodels count="2">
      <inventorymodelday>
        <DemandPercent>14.2857151031494</DemandPercent>
        <StocksPerDay>0</StocksPerDay>
        <Supply>0</Supply>
      </inventorymodelday>
      <inventorymodelday>
        <DemandPercent>14.2857151031494</DemandPercent>
        <StocksPerDay>0</StocksPerDay>
        <Supply>0</Supply>
      </inventorymodelday>
      <inventorymodelday>
        <DemandPercent>14.2857151031494</DemandPercent>
        <StocksPerDay>0</StocksPerDay>
        <Supply>0</Supply>
      </inventorymodelday>
      <inventorymodelday>
        <DemandPercent>14.2857151031494</DemandPercent>
        <StocksPerDay>1</StocksPerDay>
        <Supply>1</Supply>
      </inventorymodelday>
      <inventorymodelday>
        <DemandPercent>14.2857151031494</DemandPercent>
        <StocksPerDay>0</StocksPerDay>
        <Supply>0</Supply>
      </inventorymodelday>
      <inventorymodelday>
        <DemandPercent>14.2857151031494</DemandPercent>
        <StocksPerDay>1</StocksPerDay>
        <Supply>1</Supply>
      </inventorymodelday>
    </inventorymodeldays>
    <MovementStores>1</MovementStores>
    <MovementDays>7</MovementDays>
    <SafetyStockType>0</SafetyStockType>
    <SafetyStockDays>0</SafetyStockDays>
    <SafetyStockPercent>0</SafetyStockPercent>
    <UseDemandVariance>1</UseDemandVariance>
    <DemandVariance>0.100000001490116</DemandVariance>
    <UseCaseMultiple>0</UseCaseMultiple>
    <CaseMultiple>0</CaseMultiple>
    <UseManualInventory>0</UseManualInventory>
    <SetAsDefault>0</SetAsDefault>
    <HistoricalServiceLevel>0.899999976158142</HistoricalServiceLevel>
    <TargetServiceLevel>0.949999988079071</TargetServiceLevel>
  </inventorymodel>
</inventorymodels>

Answer 1

Here is an idea based on Get the position of xml element in SQL Server 2012 (which is similar to lptr's comment above)

Note that row_number() seemingly works here, but the order is not guaranteed there.

SELECT
    InvModel.inventorymodel.value('@id', 'varchar(100)'),
    InvModels.*

FROM(
    SELECT CAST(InvModels AS xml)
    FROM OPENROWSET(
        BULK 'C:\Users\owjo1001\AppData\Local\Nielsen\Spaceman\Config\InvModels.xml', SINGLE_BLOB) AS T(InvModels)

) AS T(InvModels)
CROSS APPLY InvModels.nodes('inventorymodels/inventorymodel') AS InvModel(inventorymodel)
cross apply (
select row_number() over(order by (select null)),
    InvModels.inventorymodelday.value('DemandPercent[1]', 'float'),
    InvModels.inventorymodelday.value('StocksPerDay[1]', 'int'),
    InvModels.inventorymodelday.value('Supply[1]', 'int'),
    InvModels.inventorymodelday.value('let $i := . return count(../inventorymodelday[. << $i])+1','int') DAY_VALUE
from 
invModel.inventoryModel.nodes('inventorymodeldays/inventorymodelday')  as InvModels(inventorymodelday)
) as InvModels(DAY_VALUE_ROW_NUMBER,DemandPercent,StocksPerDay,Supply,DAY_VALUE)