在MSSQL中将多个XML解析为单独的表

Question

我能够成功地将文件上传到数据库。(https://i.stack.imgur.com/FHy6K.png)

我有多个XML文件，我需要根据"Test name"将每个XML文件解析为单独的表。
第一列包含"日期时间"，第二列包含"订单id"。其他列将包含给定的"测试名称"的值。

我已经试着用谷歌搜索了一些说明，但仍然不能得到一个好的结果。

或者是否有其他更简单的选择，例如，当从windows文件夹将它们导入数据库时直接解析它们?

<eolresult>
<eolpresetting>
<datetime>17.02.2022 16:10:37</datetime>
<order id="16HBOD0222XSK316_FLX101xxxxxxxxxxxxxx000SK35X3PN9P40006C24A4NORL0L7P1XVV0008I06T24D0XVU0006A0LTS000A" />
</eolpresetting>
<testresult>IO</testresult>
<results>
<!--PRN="16HBOD0222XSK316_FLX101xxxxxxxxxxxxxx000SK35X3PN9P40006C24A4NORL0L7P1XVV0008I06T24D0XVU0006A0LTS000A"-->
<test name="Time">
<value name="Time of start ">17.02.2022 16:08:08</value>
<value name="Time of save ">17.02.2022 16:10:37</value>
<value name="Time ">149 s</value>
</test>
<test name="Manual test">
<value name="Manual test was used: ">False</value>
</test>
<test name="Airbag SAB">
<value name="Measured value - Generator">2,124602</value>
<value name="Low limit - Generator">1,74</value>
<value name="High limit - Generator">2,46</value>
<value name="Measured value - Break contact 1">5,101295</value>
<value name="Minumum value - Break contact 1">1,73</value>
<value name="Measured value - Break contact 2">5,162498</value>
<value name="Minumum value - Break contact 2">1,73</value>
<value name="Test OK">True</value>
</test>
<test name="Airbag CAB">
<value name="Measured value - Generator">2,235402</value>
<value name="Low limit - Generator">1,74</value>
<value name="High limit - Generator">2,46</value>
<value name="Measured value - Break contact 1">5,109858</value>
<value name="Minumum value - Break contact 1">1,73</value>
<value name="Measured value - Break contact 2">5,12398</value>
<value name="Minumum value - Break contact 2">1,73</value>
<value name="Test OK">True</value>
</test>
<test name="Heating">
<value name="Measured value">1,287712</value>
<value name="Low limit">1,22</value>
<value name="High limit">1,72</value>
<value name="Test OK">True</value>
</test>
<test name="Thermistor">
<value name="Measured value">9013,247</value>
<value name="Low limit">4000</value>
<value name="High limit">14000</value>
<value name="Test OK">True</value>
</test>
<test name="Buckle">
<value name="Measured value 1 - unbuckled">9,706554E-02</value>
<value name="Measured value 2 - buckled">45445,75</value>
<value name="Measured value 3 - unbuckled">9,584951E-02</value>
<value name="Bucked limit">3,92</value>
<value name="Unbuckled limit">999</value>
<value name="Test OK">True</value>
</test>
<test name="SBR">
<value name="Measured value - Low force">399,4937</value>
<value name="Low limit - Low force">332</value>
<value name="High limit - Low force">468</value>
<value name="Measured value - Low force - Force [kg]">5,334925</value>
<value name="Low limit - Low force - Force [kg]">5,1</value>
<value name="High limit - Low force - Force [kg]">6,4</value>
<value name="Regulator Active - Low force">False</value>
<value name="Measured value - High force">99,87806</value>
<value name="Low limit - High force">83</value>
<value name="High limit - High force">117</value>
<value name="Measured value - High force - Force [kg]">33,82478</value>
<value name="Low limit - High force - Force [kg]">33,6</value>
<value name="High limit - High force - Force [kg]">34,3</value>
<value name="Regulator Active - High force">True</value>
<value name="Strain Gauge - Tare [kg]">-10,7119</value>
<value name="Test OK">True</value>
</test>
<test name="Light">
<value name="Measured value">21,23078</value>
<value name="Low limit">10</value>
<value name="High limit">40</value>
<value name="Test OK">True</value>
</test>
<test name="Headrests">
<value name="Force - Measured">16,30317</value>
<value name="Force - Low Limit">15,81</value>
<value name="Force - High Limit">17,64</value>
<value name="Regulator Active">True</value>
<value name="Strain Gauge - Tare [kg]">-0,1437721</value>
<value name="Test OK">True</value>
</test>
<test name="ECU test">
<value name="ECU communication status">Driver seat - Address 36</value>
<value name="SW version">0560</value>
<value name="SW version - Verification Active">True</value>
<value name="SW version - Verification Status">True</value>
<value name="HW version">010</value>
<value name="HW version - Verification Active">True</value>
<value name="HW version - Verification Status">True</value>
<value name="Part Number">1EA959760H</value>
<value name="Part Number - Verification Active">True</value>
<value name="Part Number - Verification Status">True</value>
<value name="Fazit ID">CNK-SCU27.01.2200010244</value>
<value name="Coding">01189A00000020500803010101010000000000212100000000000000211000</value>
<value name="Dataset">DB_036_7100_4M0_2531_SCXXMEBSK31X_1742</value>
<value name="Dataset position in DAP (ASCII @ #10)">50</value>
<value name="Initialization Routine 0483041312 IO ECU reply">True</value>
<value name="Initialization Routine 0533040200 IO ECU reply">True</value>
<value name="Initialization Routine 0533040300 IO ECU reply">True</value>
<value name="Initialization Routine 0533041802 IO ECU reply">True</value>
<value name="Button Forward - Start Position">32876</value>
<value name="Button Forward - End Position">32854</value>
<value name="Button Forward - Result">True</value>
<value name="Button Backward - Start Position">32825</value>
<value name="Button Backward - End Position">32836</value>
<value name="Button Backward - Result">True</value>
<value name="Button Upward - Start Position">32982</value>
<value name="Button Upward - End Position">32972</value>
<value name="Button Upward - Result">True</value>
<value name="Button Downward - Start Position">32942</value>
<value name="Button Downward - End Position">32973</value>
<value name="Button Downward - Result">True</value>
<value name="Button Backrest Forward - Start Position">32499</value>
<value name="Button Backrest Forward - End Position">32472</value>
<value name="Button Backrest Forward - Result">True</value>
<value name="Button Backrest Backward - Start Position">32464</value>
<value name="Button Backrest Backward - End Position">32490</value>
<value name="Button Backrest Backward - Result">True</value>
<value name="Button Thigh Upward - Start Position">32922</value>
<value name="Button Thigh Upward - End Position">32898</value>
<value name="Button Thigh Upward - Result">True</value>
<value name="Button Thigh Downward - Start Position">32872</value>
<value name="Button Thigh Downward - End Position">32894</value>
<value name="Button Thigh Downward - Result">True</value>
<value name="Button Lumbar Forward - Start Position">65216</value>
<value name="Button Lumbar Forward - End Position">65248</value>
<value name="Button Lumbar Forward - Result">True</value>
<value name="Button Lumbar Backward - Start Position">176</value>
<value name="Button Lumbar Backward - End Position">96</value>
<value name="Button Lumbar Backward - Result">True</value>
<value name="Button Lumbar Upward - Start Position">54576</value>
<value name="Button Lumbar Upward - End Position">54672</value>
<value name="Button Lumbar Upward - Result">True</value>
<value name="Button Lumbar Downward - Start Position">55072</value>
<value name="Button Lumbar Downward - End Position">54896</value>
<value name="Button Lumbar Downward - Result">True</value>
<value name="Button SET - Result">True</value>
<value name="Button 1 - Result">True</value>
<value name="Button 2 - Result">True</value>
<value name="Button 3 / Massage - Result">True</value>
<value name="DTC Deleted">True</value>
<value name="DTC Record 1" />
<value name="DTC Record 2" />
<value name="DTC Record 3" />
<value name="DTC Record 4" />
<value name="DTC Record 5" />
<value name="DTC Record 6" />
<value name="DTC Record 7" />
<value name="DTC Record 8" />
<value name="DTC Record 9" />
<value name="DTC Record 10" />
<value name="DTC Flawless">True</value>
<value name="Test OK">True</value>
</test>
<test name="Delivery">
<value name="Measured value - Backrest">415,7932</value>
<value name="Low limit - Backrest">410</value>
<value name="High limit - Backrest">420</value>
<value name="Measured value - Forwards">468,5211</value>
<value name="Low limit - Forwards">455</value>
<value name="High limit - Forwards">475</value>
<value name="Measured value - Height">1202,52</value>
<value name="Low limit - Height">1190</value>
<value name="High limit - Height">1210</value>
<value name="Measured value - Thigh">1159,799</value>
<value name="Low limit - Thigh">1130</value>
<value name="High limit - Thigh">1170</value>
<value name="Measured value - Headrest">396,1371</value>
<value name="Low limit - Headrest">320</value>
<value name="High limit - Headrest">450</value>
<value name="Delivery OK">True</value>
</test>
</results>
</eolresult>

我已经找到了一些说明，但是我仍然无法根据我的说明正确地指定和修改查询。

https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/

USE EOL_Testers
GO
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLFilesTable
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT datetime, order id, test
FROM OPENXML(@hDoc, 'eolresult/eolpresetting/results/test')
WITH 
(
datetime [varchar](150) '@datetime',
order id [varchar](150) '@orderid',
datetime [varchar](150) '@datetime'
)
EXEC sp_xml_removedocument @hDoc
GO

Answer 1

目前还不清楚您想要的结果是什么，但是您可以这样做

SELECT
Date        = x1.presetting.value('(datetime/text())[1]', 'varchar(30)'),
OrderId     = x1.presetting.value('(order/@id)[1]', 'varchar(100)'),
TestName    = x2.test.value('@name', 'varchar(100)'),
ValueName   = x3.val.value('@name', 'varchar(100)'),
ValueResult = x3.val.value('text()[1]', 'varchar(100)')
FROM YourTable t
CROSS APPLY t.XMLData.nodes('(eolresult/eolpresetting)[1]') x1(presetting)
CROSS APPLY t.XMLData.nodes('eolresult/results/test') x2(test)
CROSS APPLY x2.test.nodes('value') x3(val);

,db&lt的在小提琴

您可能希望通过测试名称进行过滤，例如

CROSS APPLY t.XMLData.nodes('eolresult/results/test[@name = "Airbag SAB"]') x2(test)