SQL Server使用OPENXML反序列化XML

本文主要是介绍SQL Server使用OPENXML反序列化XML，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

DECLARE @data AS xml = N'<row ACOCLI="00330" ARAGS1="Some description" ACOFIS="01266820248" AEMAIL=" " ACOAGE=" " AANNUL=" " ANOMIG="Some Name"/>';
 SELECT @data.value('(row/@ACOCLI)[1]','varchar(6)'), @data.value('(row/@ARAGS1)[1]','varchar(30)'), @data.value('(row/@ACOFIS)[1]','varchar(15)'), 
@data.value('(row/@AEMAIL)[1]','varchar(6)'), @data.value('(row/@ACOAGE)[1]','varchar(6)'), 
@data.value('(row/@AANNUL)[1]','varchar(6)'), @data.value('(row/@ANOMIG)[1]','varchar(15)');

SQL 示例。【此方法支持单条xml 反序列化】

DECLARE @data AS xml = '<List Name="Default" Fields=""><Item Id="" UserId="" EmployeeName="许庆贵" WorkNo="3007" ChangeTypeAdd="成手调入" 
ChangeTypeDel="" InDeptId="PRO0001I.PROP10DD.PROP10DM" InDept="土建室" OutDeptId="" OutDept="" ChangeDate="2007-12-12" Remark="" />
<Item Id="" UserId="" EmployeeName="李晶" WorkNo="3008" ChangeTypeAdd="成手调入" ChangeTypeDel="" 
InDeptId="PRO0001I.PROP10DD.PROP10DY" InDept="暖通室" OutDeptId="" OutDept="" ChangeDate="2007-12-12" Remark="" /></List>';

SELECT @data.value('(List/Item/@EmployeeName)[1]','varchar(6)'),
       @data.value('(List/Item/@WorkNo)[1]','varchar(30)'),
       @data.value('(List/Item/@ChangeTypeAdd)[1]','varchar(15)'),
       @data.value('(List/Item/@ChangeTypeDel)[1]','varchar(6)'),
       @data.value('(List/Item/@InDeptId)[1]','varchar(6)'),
       @data.value('(List/Item/@InDept)[1]','varchar(6)'),
       @data.value('(List/Item/@ChangeDate)[1]','varchar(15)');

这篇关于SQL Server使用OPENXML反序列化XML的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！