I've started shredding my XML document in SQL Server but I'm stuck on how to get tback out. I can currently only get the 3 elements under inventorymodelday
to each row. How do you go "back up" in levels so I can apply the inventorymodel id
to each row (in this case "Default")?
SELECT
InvModels.inventorymodelday.query('DemandPercent').value('.', 'float'),
InvModels.inventorymodelday.query('StocksPerDay').value('.', 'int'),
InvModels.inventorymodelday.query('Supply').value('.', 'int')
FROM(
SELECT CAST(InvModels AS xml)
FROM OPENROWSET(
BULK '***.xml', SINGLE_BLOB) AS T(InvModels)
) AS T(InvModels)
CROSS APPLY InvModels.nodes('inventorymodels/inventorymodel/inventorymodeldays/inventorymodelday') AS InvModels(inventorymodelday)
<inventorymodels count="1">
<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>
</inventorymodel>
</inventorymodels>
Something like that might work
SELECT
InvModel.inventorymodel.value('@id', 'varchar(100)'),
InvModels.inventorymodelday.query('DemandPercent').value('.', 'float'),
InvModels.inventorymodelday.query('StocksPerDay').value('.', 'int'),
InvModels.inventorymodelday.query('Supply').value('.', 'int')
FROM(
SELECT CAST(InvModels AS xml)
FROM OPENROWSET(
BULK '***.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)