How to Ensure the XML.Nodes Always Returns a Value or Link Variables to XML.Nodes Table

Question

I have some XML that I read from a table that looks like this:

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

I use the following query to parse the XML and return a single record (@RcdSetId and @RcdType are variables that will actually be passed in to this stored procedure but have fixed declarations for this example):

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)

This works fine so long as @Xml is not empty. However, if @Xml is empty then nothing is returned. I am trying to find a way so that I always get the values from @RcdSetId and @RcdType regardless of whether or not there is any XML data.

If there is data it should return something like:

RcdSetID	RcdType	selectby	approvalaction	approvalperiodtype	approvalperiodrange	perioddate	operationkey	levels
1	O	3	1	1	3	Feb 16 2009 12:00AM	131de3f0-da73-426d-bcf6-c9e20f501d3d	b87b1cee-0d86-4d1b-9018-c31238d5d764

If there is no data in the @Xml variable then it should return the following:

RcdSetID	RcdType	selectby	approvalaction	approvalperiodtype	approvalperiodrange	perioddate	operationkey	levels
1	O

We're running SQL Server 2022.

Answer 1

A couple of possibilities to deal with .nodes returning zero rows.

1. Generate one row and then OUTER APPLY the nodes result to that

select v.RcdSetID, 
       v.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(@RcdSetID, @RcdType)) V(RcdSetID, RcdType)
OUTER APPLY @Xml.nodes('//timecard') workdays(d) 

2. Make the query into a scalar aggregate as they always return 1 row even if 0 rows are the input

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

The casting everything to string (and subsequent replacement of nulls with empty strings) looks smelly though.