T-SQL-OPENXML

使用T-SQL的XML数据类型和相关函数来替代OPENXML

示例代码：

-- 原始代码使用OPENXML来读取XML文件 DECLARE @XMLFilePath VARCHAR(MAX) = 'C:\Sample.xml' DECLARE @XML INT EXEC sp_xml_preparedocument @XML OUTPUT, @XMLFilePath

SELECT * FROM OPENXML (@XML, '/root/employee', 2) WITH ( ID INT, Name VARCHAR(50), Department VARCHAR(50) )

EXEC sp_xml_removedocument @XML

-- 使用XML数据类型和相关函数来读取XML文件 DECLARE @XML XML = ' <root> <employee> <ID>1</ID> <Name>John</Name> <Department>IT</Department> </employee> <employee> <ID>2</ID> <Name>Jane</Name> <Department>HR</Department> </employee> </root>'

SELECT ID = T.c.value('ID[1]', 'INT'), Name = T.c.value('Name[1]', 'VARCHAR(50)'), Department = T.c.value('Department[1]', 'VARCHAR(50)') FROM @XML.nodes('/root/employee') T(c)