-- Create a table to store the parsed data
CREATE TABLE EolResultData (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    DateTime DATETIME,
    TestName NVARCHAR(100),
    ValueName NVARCHAR(100),
    Value NVARCHAR(100)
);

-- Declare variables
DECLARE @XMLData XML;
DECLARE @ID INT, @DateTime DATETIME;
DECLARE @TestName NVARCHAR(100), @ValueName NVARCHAR(100), @Value NVARCHAR(100);

-- Iterate through each XML file
DECLARE xml_cursor CURSOR FOR
SELECT ID, CAST(XmlData AS XML)
FROM YourTableName -- Replace with the actual table name and column name where XML files are stored

OPEN xml_cursor;
FETCH NEXT FROM xml_cursor INTO @ID, @XMLData;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Extract data from XML
    SET @DateTime = @XMLData.value('(eolresult/eolpresetting/datetime)[1]', 'DATETIME');

    -- Process each test node
    DECLARE test_cursor CURSOR FOR
    SELECT T.c.value('@name', 'NVARCHAR(100)') AS TestName
    FROM @XMLData.nodes('eolresult/results/test') AS T(c);

    OPEN test_cursor;
    FETCH NEXT FROM test_cursor INTO @TestName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Process each value node within the test node
        DECLARE value_cursor CURSOR FOR
        SELECT V.c.value('@name', 'NVARCHAR(100)') AS ValueName,
               V.c.value('.', 'NVARCHAR(100)') AS Value
        FROM @XMLData.nodes('eolresult/results/test[@name=sql:variable("@TestName")]/value') AS V(c);

        OPEN value_cursor;
        FETCH NEXT FROM value_cursor INTO @ValueName, @Value;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- Insert parsed data into the table
            INSERT INTO EolResultData (DateTime, TestName, ValueName, Value)
            VALUES (@DateTime, @TestName, @ValueName, @Value);

            FETCH NEXT FROM value_cursor INTO @ValueName, @Value;
        END;

        CLOSE value_cursor;
        DEALLOCATE value_cursor;

        FETCH NEXT FROM test_cursor INTO @TestName;
    END;

    CLOSE test_cursor;
    DEALLOCATE test_cursor;

    FETCH NEXT FROM xml_cursor INTO @ID, @XMLData;
END;

CLOSE xml_cursor;
DEALLOCATE xml_cursor;