Convert Xml data into a table in sql server without loop?

Question

I have the data in xml format. I need to insert into Employee and Salary table

 DECLARE @XmlInput XML = '<Office>
    <Employee>
        <EUID>xyz</EUID>
        <FirstName>Ram</FirstName>
        <MiddleName>J</MiddleName>
        <LastName>D</LastName>
            <Salary>
                <Monthly>
                    <Year>2022</Year>
                    <Month>01</Month>
                    <Day>31</Day>
                    <Sal>
                        <Amt>100000</Amt>
                    </Sal>
                </Monthly>
                <Monthly>
                    <Year>2022</Year>
                    <Month>02</Month>
                    <Day>28</Day>
                    <Sal>
                        <Amt>90000</Amt>
                    </Sal>
                </Monthly>
            </Salary>
        <Dept>CSE</Dept>
    </Employee>
    <Employee>
        <EUID>abc</EUID>
        <FirstName>Krishna</FirstName>
        <MiddleName>R</MiddleName>
        <LastName>G</LastName>
            <Salary>
                <Monthly>
                    <Year>2022</Year>
                    <Month>01</Month>
                    <Day>31</Day>
                    <Sal>
                        <Amt>50000</Amt>
                    </Sal>
                </Monthly>
                <Monthly>
                    <Year>2022</Year>
                    <Month>02</Month>
                    <Day>28</Day>
                    <Sal>
                        <Amt>60000</Amt>
                    </Sal>
                </Monthly>
            </Salary>
        <Dept>ECE</Dept>
    </Employee>
</Office>' ; 

I am able to get output for Employee table by using below logic

SELECT
            [Table].[Column].value('EUID[1]', 'varchar(MAX)') as 'EID',
            [Table].[Column].value('FirstName [1]', 'varchar(50)') as ' FirstName ',
            [Table].[Column].value(' MiddleName[1]', 'varchar(50)') as ' MidName',
            [Table].[Column].value(' LastName [1]', 'varchar(50)') as ' LastName',
            [Table].[Column].value('(./Dept/text())[1]','Varchar(50)') as [DName] 
FROM 
            @XmlInput.nodes('/Office/Employee') as [Table]([Column])

Output:

EID  EUID   FirstName   MiddleName  LastName      Dept
1    xyz      Ram           J          D          CSE
2    abc    Krishna         R          G          ECE

But I'm not able to find any logic for inserting EUID into salary table without using loop

Output should be in this format:

ID  SalaryDate  SalAmt  EUID
1   2022-01-31  100000  xyz
2   2022-02-28  90000   xyz
3   2022-01-31  50000   abc
4   2022-02-28  60000   abc

I'm new to xml parsing

Answer 1

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @Employee TABLE (
    EID INT IDENTITY PRIMARY KEY, 
    EUID VARCHAR(50),
    FirstName VARCHAR(50),
    MidName VARCHAR(50),
    LastName VARCHAR(50),
    Dept VARCHAR(50)
);
DECLARE @Salary TABLE (
    ID INT IDENTITY PRIMARY KEY,
    EUID VARCHAR(50),
    SalaryDate DATE,
    SalAmt DECIMAL(10,2)
);

DECLARE @XmlInput XML = 
N'<Office>
    <Employee>
        <EUID>xyz</EUID>
        <FirstName>Ram</FirstName>
        <MiddleName>J</MiddleName>
        <LastName>D</LastName>
        <Salary>
            <Monthly>
                <Year>2022</Year>
                <Month>01</Month>
                <Day>31</Day>
                <Sal>
                    <Amt>100000</Amt>
                </Sal>
            </Monthly>
            <Monthly>
                <Year>2022</Year>
                <Month>02</Month>
                <Day>28</Day>
                <Sal>
                    <Amt>90000</Amt>
                </Sal>
            </Monthly>
        </Salary>
        <Dept>CSE</Dept>
    </Employee>
    <Employee>
        <EUID>abc</EUID>
        <FirstName>Krishna</FirstName>
        <MiddleName>R</MiddleName>
        <LastName>G</LastName>
        <Salary>
            <Monthly>
                <Year>2022</Year>
                <Month>01</Month>
                <Day>31</Day>
                <Sal>
                    <Amt>50000</Amt>
                </Sal>
            </Monthly>
            <Monthly>
                <Year>2022</Year>
                <Month>02</Month>
                <Day>28</Day>
                <Sal>
                    <Amt>60000</Amt>
                </Sal>
            </Monthly>
        </Salary>
        <Dept>ECE</Dept>
    </Employee>
</Office>';
-- DDL and sample data population, end

INSERT INTO @Employee (EUID, FirstName, MidName, LastName, Dept)
SELECT EUID = c.value('(EUID/text())[1]', 'varchar(50)'),
    FirstName = c.value('(FirstName/text())[1]', 'varchar(50)'),
    MidName = c.value('(MiddleName/text())[1]', 'varchar(50)'),
    LastName = c.value('(LastName/text())[1]', 'varchar(50)'),
    Dept = c.value('(Dept/text())[1]','Varchar(50)')  
FROM @XmlInput.nodes('/Office/Employee') as t(c);

INSERT INTO @Salary (EUID, SalaryDate, SalAmt)
SELECT EUID = c.value('(../../EUID/text())[1]', 'varchar(50)'),
    SalaryDate = c.value('(Year/text())[1]', 'CHAR(4)') + '-' +
        c.value('(Month/text())[1]', 'CHAR(2)') + '-' +
        c.value('(Day/text())[1]', 'CHAR(2)'),
    SalAmt = c.value('(Sal/Amt/text())[1]', 'DECIMAL(10,2)')
FROM @XmlInput.nodes('/Office/Employee/Salary/Monthly') as t(c);

-- test
SELECT * FROM @Employee;
SELECT * FROM @Salary;