在SQL Server中切碎XML文档

Question

我已经开始在SQL Server中切碎我的XML文档，但我一直在思考如何将其打包。我目前只能将inventorymodelday下的3个元素分配给每一行。你怎么样；"备份"；以便我可以将inventorymodel id应用于每一行(在这种情况下为"默认"(？

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>

Answer 1

假设在变量@input中有您的XML，您可以尝试以下操作：

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)

这将返回如下结果：

>

ModelId	DemandPercent	StocksPerDay	Supply
默认	1500000953674300
默认值	1000000000000000	0	1
默认值	1000000000000000
默认值	120000000000000		默认值	1300000000000	0		默认值	1000000000000000	1
默认值	2000000000000000

类似的东西可能会在中工作

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)

以下是如何通过模拟一对多关系的doubleCROSS APPLY子句来处理它。

注意事项：

• 无需使用.query()方法。只要.value()方法就足够了
• 出于性能原因，使用XPath../text()表达式

SQL

;WITH rs (xmldata) AS
(
SELECT TRY_CAST(BulkColumn AS XML) AS BulkColumn 
FROM OPENROWSET(BULK 'e:Tempowneyjs.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);

输出

+----------------+------------------+--------------+--------+
| 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 |
+----------------+------------------+--------------+--------+