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>
CodePudding user response:
Here is how to handle it via double CROSS APPLY clause that is simulating one-to-many relationship.
Notable points:
- No need to use
.query()
method. Just.value()
method is enough. - XPath
../text()
expression is used for performance reasons.
SQL
;WITH rs (xmldata) AS
(
SELECT TRY_CAST(BulkColumn AS XML) AS BulkColumn
FROM OPENROWSET(BULK 'e:\Temp\owneyjs.xml', SINGLE_BLOB) AS x
)
SELECT c.value('@id', 'VARCHAR(30)') AS inventorymodel
, x.value('(DemandPercent/text())[1]','FLOAT') AS DemandPercent
, x.value('(StocksPerDay/text())[1]', 'INT') AS StocksPerDay
, x.value('(Supply/text())[1]', 'INT') AS Supply
FROM rs
CROSS APPLY xmldata.nodes('/inventorymodels/inventorymodel') AS t1(c)
CROSS APPLY t1.c.nodes('inventorymodeldays/inventorymodelday') AS t2(x);
Output
---------------- ------------------ -------------- --------
| inventorymodel | DemandPercent | StocksPerDay | Supply |
---------------- ------------------ -------------- --------
| Default | 15.0000009536743 | 0 | 0 |
| Default | 10 | 0 | 1 |
| Default | 11 | 0 | 0 |
| Default | 12 | 0 | 0 |
| Default | 13 | 0 | 0 |
| Default | 19 | 1 | 1 |
| Default | 20 | 0 | 0 |
---------------- ------------------ -------------- --------
CodePudding user response:
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)
CodePudding user response:
Assuming you have your XML in a variable @input
, you could try this:
SELECT
ModelId = xc.value('(../../@id)[1]', 'varchar(25)'),
DemandPercent = xc.value('(DemandPercent)[1]', 'decimal(25,15)'),
StocksPerDay = xc.value('(StocksPerDay)[1]', 'int'),
Supply = xc.value('(Supply)[1]', 'int')
FROM
@input.nodes('/inventorymodels/inventorymodel/inventorymodeldays/inventorymodelday') as XT(XC)
This will return a result like this:
ModelId | DemandPercent | StocksPerDay | Supply |
---|---|---|---|
Default | 15.000000953674300 | 0 | 0 |
Default | 10.000000000000000 | 0 | 1 |
Default | 11.000000000000000 | 0 | 0 |
Default | 12.000000000000000 | 0 | 0 |
Default | 13.000000000000000 | 0 | 0 |
Default | 19.000000000000000 | 1 | 1 |
Default | 20.000000000000000 | 0 | 0 |