在没有循环的情况下将 Xml 数据转换为 sql server 中的表？-解网

问：

我有xml格式的数据。我需要插入到员工和工资表中

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

我能够使用以下逻辑获取 Employee 表的输出

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

输出：

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

但是我无法找到任何在不使用循环的情况下将 EUID 插入工资表的逻辑

输出应采用以下格式：

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

我是 xml 解析的新手

SQL SQL Server 数据库 XML 分析

在提出问题时，您需要提供一个最小的可重现示例：（1） DDL 和样本数据填充，即 CREATE 表加上 INSERT T-SQL 语句。（2）你需要做什么，即逻辑和你的代码尝试在T-SQL中实现它。（3）期望的输出，基于上面 #1 中的示例数据。（4）您的 SQL Server 版本（SELECT @@version;）。

答：

1赞 Yitzhak Khabinsky 5/6/2022 #1

请尝试以下解决方案。

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;

在没有循环的情况下将 Xml 数据转换为 sql server 中的表？

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

评论

评论