How to translate xml data to sql query?

Hi Team

I need help with my ER diagram, must first need to translate xml data to sql query then insert this data to ER diagram so could have unique primary key and foreign key.

CREATE TABLE OrderHeader (
    OrderNumber VARCHAR(10),
    OrderType VARCHAR(20),
    OrderStatus VARCHAR(20),
    CustomerName VARCHAR(50),
    CreateDate DATETIME
);

CREATE TABLE OrderLine (
    OrderNumber VARCHAR(10),
    LineNumber INT,
    ProductCode VARCHAR(20),
    ProductType VARCHAR(20),
    CostPrice DECIMAL(10, 2),
    SalesPrice DECIMAL(10, 2),
    Quantity INT
);


// xml data
. 

SO625144
Normal
New
KFC
2018-03-08 13:52:21


1
GSX837420
Parts
13.54
84.49
10


2
AVF697420
Apparel
19.34
105.99
25




SO625145
Normal
New

Nandos
2018-03-09 08:56:21


1

// ER diagram

-- Create the OrderHeader DataTable
DECLARE @OrderHeader TABLE (
    OrderNumber NVARCHAR(50),
    OrderType NVARCHAR(50),
    OrderStatus NVARCHAR(50),
    CustomerName NVARCHAR(50),
    CreateDate DATETIME
);

-- Create the OrderLine DataTable
DECLARE @OrderLine TABLE (
    OrderNumber NVARCHAR(50),
    LineNumber INT,
    ProductCode NVARCHAR(50),
    ProductType NVARCHAR(50),
    CostPrice DECIMAL(18, 2),
    SalesPrice DECIMAL(18, 2),
    Quantity INT
);

-- XML data
DECLARE @xmlData XML = '

    
        
            SO625144
            Normal
            New
            KFC
            2018-03-08 13:52:21
        
        
            1
            GSX837420
            Parts
            13.54
            84.49
            10
        
        
            2
            AVF697420
            Apparel
            19.34
            105.99
            25
        
    
';

-- Prepare the XML document for parsing
DECLARE @hDoc INT;

EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmlData;

-- Retrieve data from the XML and insert into DataTables
INSERT INTO @OrderHeader
SELECT OrderNumber, OrderType, OrderStatus, CustomerName, CreateDate
FROM OPENXML(@hDoc, '/Root/SalesOrder/OrderHeader')
WITH (
    OrderNumber NVARCHAR(50) './OrderNumber',
    OrderType NVARCHAR(50)  './OrderType',
    OrderStatus NVARCHAR(50) './OrderStatus',
    CustomerName NVARCHAR(50) './CustomerName' , 
    CreateDate DATETIME './CreateDate'
 );

INSERT INTO @OrderLine
SELECT oh.OrderNumber, LineNumber, ProductCode, ProductType, CostPrice, SalesPrice, Quantity
FROM OPENXML(@hDoc, '/Root/SalesOrder/OrderLine')
WITH (
    OrderNumber NVARCHAR(50) '../OrderHeader/OrderNumber',
    LineNumber INT './LineNumber',
    ProductCode NVARCHAR(50) './ProductCode',
    ProductType NVARCHAR(50) './ProductType',
    CostPrice DECIMAL(18, 2) './CostPrice',
    SalesPrice DECIMAL(18, 2) './SalesPrice',
    Quantity INT './Quantity'
) AS ol
JOIN @OrderHeader oh ON oh.OrderNumber = ol.OrderNumber;

-- Clean up the XML document
EXEC sp_xml_removedocument @hDoc;

-- Perform any required database operations using the populated DataTables
-- For example, you can select data from the DataTables
SELECT * FROM @OrderHeader;
SELECT * FROM @OrderLine;

You can modify the code according to your specific database system