如何确保XML.Nodes始终返回值或关联变量至XML.Nodes表

场景与问题 #

从表中读取的XML结构如下：

<timecard>
  <selectby>3</selectby>
  <approvalaction>2</approvalaction>
  <approvalperiodtype>1</approvalperiodtype>
  <approvalperiodrange>3</approvalperiodrange>
  <perioddate>2024-12-16T10:41:23</perioddate>
  <operationkey>5b1f7115-09b6-4f29-8613-edbf3d1d5265</operationkey>
  <levels>e54b5a4b-c1bd-4f4d-807e-0f3810f20446</levels>
</timecard>

原查询用于解析XML并返回单条记录（@RcdSetId和@RcdType为存储过程传入变量，示例中固定赋值）：

declare @Xml xml, @RcdSetID int = 1, @RcdType char(1) = 'O'

select @xml = oldxml from dbo.Audit where AuditKey = 'F033126A-01BA-4A38-AED6-73DA7ABA7F6C';

select @RcdSetID RcdSetID, @RcdType RcdType,
       isnull(cast(d.value('selectby[1]','int') as varchar(1000)),'') selectby,
       isnull(cast(d.value('approvalaction[1]','int') as varchar(1000)),'') approvalaction,
       isnull(cast(d.value('approvalperiodtype[1]','int') as varchar(1000)),'') approvalperiodtype,
       isnull(cast(d.value('approvalperiodrange[1]','int') as varchar(1000)),'') approvalperiodrange,
       isnull(cast(d.value('perioddate[1]','datetime') as varchar(1000)),'') perioddate,
       isnull(cast(d.value('operationkey[1]','varchar(36)') as varchar(1000)),'') operationkey,
       isnull(cast(d.value('levels[1]','varchar(36)') as varchar(1000)),'') levels
from @Xml.nodes('//timecard') workdays(d)

原查询在XML非空时运行正常，但当XML为空时，@Xml.nodes('//timecard')返回空集，导致整个查询无结果返回。需求是：无论XML是否包含数据，始终返回@RcdSetId和@RcdType的值；若XML有数据则返回对应字段值，若无数据则其他字段为空。

解决方案 #

方案一：使用LEFT JOIN确保返回单条记录 #

通过将查询主表设为包含单条记录的数据集，再与XML节点结果做LEFT JOIN，确保即使XML为空，主数据集仍会返回一条记录：

declare @Xml xml, @RcdSetID int = 1, @RcdType char(1) = 'O'

select @xml = oldxml from dbo.Audit where AuditKey = 'F033126A-01BA-4A38-AED6-73DA7ABA7F6C';

select 
    @RcdSetID RcdSetID, 
    @RcdType RcdType,
    isnull(cast(d.value('selectby[1]','int') as varchar(1000)),'') selectby,
    isnull(cast(d.value('approvalaction[1]','int') as varchar(1000)),'') approvalaction,
    isnull(cast(d.value('approvalperiodtype[1]','int') as varchar(1000)),'') approvalperiodtype,
    isnull(cast(d.value('approvalperiodrange[1]','int') as varchar(1000)),'') approvalperiodrange,
    isnull(cast(d.value('perioddate[1]','datetime') as varchar(1000)),'') perioddate,
    isnull(cast(d.value('operationkey[1]','varchar(36)') as varchar(1000)),'') operationkey,
    isnull(cast(d.value('levels[1]','varchar(36)') as varchar(1000)),'') levels
from (VALUES(1)) AS t(Id)
LEFT JOIN @Xml.nodes('//timecard') workdays(d) ON 1=1

方案二：直接使用XML变量的value方法 #

这种写法更简洁，直接通过XML变量的value方法取值，当XML为空或节点不存在时，value返回NULL，再通过ISNULL转为空字符串：

declare @Xml xml, @RcdSetID int = 1, @RcdType char(1) = 'O'

select @xml = oldxml from dbo.Audit where AuditKey = 'F033126A-01BA-4A38-AED6-73DA7ABA7F6C';

select 
    @RcdSetID RcdSetID, 
    @RcdType RcdType,
    isnull(cast(@Xml.value('(//timecard/selectby)[1]','int') as varchar(1000)),'') selectby,
    isnull(cast(@Xml.value('(//timecard/approvalaction)[1]','int') as varchar(1000)),'') approvalaction,
    isnull(cast(@Xml.value('(//timecard/approvalperiodtype)[1]','int') as varchar(1000)),'') approvalperiodtype,
    isnull(cast(@Xml.value('(//timecard/approvalperiodrange)[1]','int') as varchar(1000)),'') approvalperiodrange,
    isnull(cast(@Xml.value('(//timecard/perioddate)[1]','datetime') as varchar(1000)),'') perioddate,
    isnull(cast(@Xml.value('(//timecard/operationkey)[1]','varchar(36)') as varchar(1000)),'') operationkey,
    isnull(cast(@Xml.value('(//timecard/levels)[1]','varchar(36)') as varchar(1000)),'') levels

两种方案都能满足需求：XML有数据时返回对应字段值，XML为空时返回@RcdSetId、@RcdType及其他空字段。

内容的提问来源于stack exchange，提问作者Caynadian