SQL Server MS SQL Parsing xml files to tables

idv4meu8 于 2023-06-21 发布在其他

关注(0)|答案(2)|浏览(110)

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:

sql-server

来源：https://stackoverflow.com/questions/76454398/ms-sql-parsing-xml-files-to-tables

2条答案

按热度按时间

jdgnovmf1#

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

db<>fiddle

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

db<>fiddle

赞(0）回复(0）举报 2023-06-21

wnvonmuf2#

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

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server MS SQL Parsing xml files to tables

2条答案

相关问题

热门标签

最新问答