英文:
MS SQL Parsing xml files to tables
问题
您能帮助我将XML文件解析成表格吗？我在我的SQL数据库中有成千上万个像这样的XML文件：
这些XML文件具有以下结构：
<eolresult>
<eolpresetting>
<datetime>08.12.2020 5:24:48</datetime>
<order id="2049457704XSK316_FRX101xxxxxxxxxxxxxx000SK35X3PH9P40006C24A4NORL0L7P7XYA0000006T24D0XXK0006A00000000" />
</eolpresetting>
<testresult>NIO</testresult>
<results>
<!--PRN="2049457704XSK316_FRX101xxxxxxxxxxxxxx000SK35X3PH9P40006C24A4NORL0L7P7XYA0000006T24D0XXK0006A00000000"-->
<test name="Time">
<value name="Time of start ">08.12.2020 5:23:03</value>
<value name="Time of save ">08.12.2020 5:24:48</value>
<value name="Time ">105 s</value>
</test>
<test name="Manual test">
<value name="Manual test was used: ">False</value>
</test>
<!-- 其他测试的XML数据 -->
</results>
</eolresult>
我需要以某种方式解析所有这些XML文件并转换成表格，例如按照“测试名称”，因为我想通过Power BI从这些数据创建报告。或者如果有其他处理这些文件的方法，我将感激任何想法。
我想象的表格可能如下所示。每个表格包含ID、eolpresetting中的日期以及测试中的值。
示例：
英文:
can you please help me with parsing xml files into tables?
I have in my SQL database thousands of xml files like this:
These xml files got this structure:
`<eolresult>
<eolpresetting>
<datetime>08.12.2020 5:24:48</datetime>
<order id="2049457704XSK316_FRX101xxxxxxxxxxxxxx000SK35X3PH9P40006C24A4NORL0L7P7XYA0000006T24D0XXK0006A00000000" />
</eolpresetting>
<testresult>NIO</testresult>
<results>
<!--PRN="2049457704XSK316_FRX101xxxxxxxxxxxxxx000SK35X3PH9P40006C24A4NORL0L7P7XYA0000006T24D0XXK0006A00000000"-->
<test name="Time">
<value name="Time of start ">08.12.2020 5:23:03</value>
<value name="Time of save ">08.12.2020 5:24:48</value>
<value name="Time ">105 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,111209</value>
<value name="Low limit - Generator">1,74</value>
<value name="High limit - Generator">2,26</value>
<value name="Measured value - Break contact 1">5,069321</value>
<value name="Minumum value - Break contact 1">1,73</value>
<value name="Measured value - Break contact 2">5,092026</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,294227</value>
<value name="Low limit">1,22</value>
<value name="High limit">1,49</value>
<value name="Test OK">True</value>
</test>
<test name="Thermistor">
<value name="Measured value">8671,408</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,730747E-02</value>
<value name="Measured value 2 - buckled">25022,52</value>
<value name="Measured value 3 - unbuckled">8,596408E-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">99999,9</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,242241</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">99999,9</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,80444</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,77971</value>
<value name="Test OK">False</value>
</test>
<test name="Light">
<value name="Measured value">21,33521</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,04999</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]">-8,725739E-02</value>
<value name="Test OK">True</value>
</test>
<test name="Delivery">
<value name="Measured value - Backrest">0</value>
<value name="Low limit - Backrest">0</value>
<value name="High limit - Backrest">0</value>
<value name="Measured value - Forwards">0</value>
<value name="Low limit - Forwards">0</value>
<value name="High limit - Forwards">0</value>
<value name="Measured value - Height">0</value>
<value name="Low limit - Height">0</value>
<value name="High limit - Height">0</value>
<value name="Measured value - Thigh">0</value>
<value name="Low limit - Thigh">0</value>
<value name="High limit - Thigh">0</value>
<value name="Measured value - Headrest">384,2014</value>
<value name="Low limit - Headrest">320</value>
<value name="High limit - Headrest">450</value>
<value name="Delivery OK">False</value>
</test>
</results>
</eolresult>`
And I need to somehowe parse all of these xml files to tables for. example by the "Test name" because I would like to create a report from these data via Power BI. Or if there is some another way how to process these files, I will be glad for every idea.
What I imagine is that the tables would look something like this. Each table contain the ID, the date from the eolpresenting and the values from the tests.
Example:
答案1
得分: -1
要解析XML文件并将数据存储在MS SQL中的表中，您可以使用SQL Server内置的XML功能。
编写一个脚本或存储过程来解析XML文件并将数据插入表中。
-
EolResultData表位于开头
-
XML数据被正确声明为XML变量，@XMLData。
-
使用游标来迭代XML数据并提取所需信息。
嵌套的游标被设计成处理XML数据中的测试和值节点。
-
解析的数据被插入到EolResultData表中。
在此模板中，您可以根据实际存储XML文件的表名和列名替换游标声明中的*YourTableName*。
此代码应解析XML文件，提取所需数据，并将其存储在EolResultData表中，该表具有ID、DateTime、TestName、ValueName和Value列。
更新：
假设多个XML记录位于<eolresults>元素下方，它可以正确处理它们，而无需使用游标，正如某人建议的那样。
修改后的模板在此处使用nodes()方法提取单独的<eolpresetting>节点，然后执行INSERT INTO SELECT操作将数据插入到EolResultData表中。它还具有额外的orderId列。
英文:
To parse XML files and store the data in tables in MS SQL, you can use the SQL Server's built-in XML functionality
Write a script or stored procedure to parse the XML files and insert the data into the tables.
-
EolResultData table is included at the beginning
-
The XML data is properly declared as an XML variable, @XMLData.
-
Cursors are used to iterate through the XML data and extract the required information.
The nested cursors are structured to process the test and value nodes
within the XML data. -
The parsed data is inserted into the EolResultData table.
Here is the template you can adapt
In the above replace YourTableName in the cursor declaration with the actual table name and column name where your XML files are stored.
This code should parse the XML files, extract the required data, and store it in the EolResultData table, which has columns for ID, DateTime, TestName, ValueName, and Value.
Update:
Assuming multiple XML records are present under the <eolresults> element, it can handle them correctly without using cursors as someone suggested.
The modified template here uses the nodes() method to extract individual <eolpresetting> nodes and then performs an INSERT INTO SELECT operation to insert the data into the EolResultData table. It has the extra orderId col
答案2
得分: -1
这是您要翻译的内容：
不需要在这里使用游标，您可以直接在INSERT语句中使用.nodes。
甚至可以将一个.nodes的集合与另一个集合进行交叉连接，还可以将单个节点传递给.nodes以解析第二级。
如果您正在使用包含数据的实际表格，可以将FROM更改为类似于以下内容：
这是您需要的全部代码，无需使用游标。请注意使用text()，它比从.进行隐式转换更高效。
您可能希望进一步将其转换为单独的表格，可以使用XQuery谓词，例如：
英文:
You don't need a cursor here, you can just use .nodes directly in an INSERT.
You can even cross-join one set of .nodes with another, and you can also pass a single node to .nodes to shred a second level.
INSERT INTO EolResultData
(DateTime, OrderId, TestName, ValueName, Value)
SELECT
DateTime = x1.presetting.value('(datetime/text())[1]', 'datetime'),
OrderId = x1.presetting.value('(order/@id)[1]', 'nvarchar(100)'),
Testname = x2.test.value('@name', 'nvarchar(100)'),
ValueName = x3.value.value('@name', 'nvarchar(100)'),
Value = x3.value.value('text()[1]', 'nvarchar(100)')
FROM @XMLData.nodes('eolresult/eolpresetting') x1(presetting)
CROSS APPLY @XMLData.nodes('eolresult/results/test') x2(test)
CROSS APPLY x2.test.nodes('value') x3(value);
If you are using an actual table with the data, you can change the FROM to something like this:
FROM YourTable t
CROSS APPLY (VALUES( CAST(t.SomeColumn AS xml) )) v(XMLData) -- only necessary if conversion is needed
CROSS APPLY v.XMLData.nodes('eolresult/eolpresetting') x1(presetting)
CROSS APPLY v.XMLData.nodes('eolresult/results/test') x2(test)
CROSS APPLY x2.test.nodes('value') x3(value);
This is all the code you need, no cursors necessary. Note the use of text() which is more performant than an implicit conversion from .
You may want to further transform it into separate tables, for that you can use XQuery predicates, such as this
SELECT
DateTime = x1.presetting.value('(datetime/text())[1]', 'datetime'),
OrderId = x1.presetting.value('(order/@id)[1]', 'nvarchar(100)'),
Testname = x2.test.value('@name', 'nvarchar(100)'),
[Measured value] = x2.test.value('(value[@name="Measured value"]/text())[1]', 'nvarchar(100)'),
[Low limit] = x2.test.value('(value[@name="Low limit" ]/text())[1]', 'nvarchar(100)'),
[High limit] = x2.test.value('(value[@name="High limit" ]/text())[1]', 'nvarchar(100)'),
[Test OK] = x2.test.value('(value[@name="Test OK" ]/text())[1]', 'nvarchar(100)')
FROM @XMLData.nodes('eolresult/eolpresetting') x1(presetting)
CROSS APPLY @XMLData.nodes('eolresult/results/test[@name = "Light"]') x2(test);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论