更新时间:2023-02-03 12:54:49
假设 XML 示例如上:
Assuming XML sample as above:
<Record xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Emp>
<ID>3</ID>
<EmpName>Dibyendu</EmpName>
<Sal>3500</Sal>
</Emp>
</Record>
假设下表:
CREATE TABLE Employee
(
[ID] [int] NOT NULL,
[EmpName] varchar(max) NOT NULL,
[Sal] [int] NULL
)
以下使用 xpaths 的存储过程应该可以解决问题
The following stored procedure, that uses xpaths, should do the trick
CREATE PROCEDURE AddEmployee
@empXml xml
AS
INSERT INTO Employee
(
ID,
EmpName,
Sal
)
VALUES
(
@empXml.value('(/Record/Emp/ID)[1]', 'int'),
@empXml.value('(/Record/Emp/EmpName)[1]', 'varchar(max)'),
@empXml.value('(/Record/Emp/Sal)[1]', 'int')
)
然后你可以执行:
exec AddEmployee '<Record xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Emp><ID>3</ID><EmpName>Dibyendu</EmpName><Sal>3500</Sal></Emp></Record>'
如果 Record XML 可能包含多个Emp"元素,您将需要进行一些重构.
You will need to do a little refactoring if the Record XML could potentially include multiple 'Emp' elements.