sql-serverxmlt-sqlxqueryshred

Shred XML document in SQL Server


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>

Solution

  • 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)