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:

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:

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

We're running SQL Server 2022.

• sql
• sql-server
• xml

• Can you show what if @Xml is empty means? Is that NULL, or <timecard />, or <timecard></timecard>, or something else? – Aaron Bertrand Commented Feb 14 at 18:47
• Aside... why deserialize strongly typed data from the XML only to cast it all to varchar? – AlwaysLearning Commented Feb 14 at 23:42
• 1 Have you considered using @Xml.nodes() in an outer apply instead? Maybe in the same statement against dbo.Audit, but even from (values (1)) T(N) outer apply @Xml.nodes(...) would be sufficient. – AlwaysLearning Commented Feb 14 at 23:44
• Do you have a list of all combinations of RcdSetId and RcdType that you want to display?? If not you should consider putting them in a table, then left join to that table, you have a row with all nulls – Hogan Commented Feb 16 at 0:33
• @AlwaysLearning - See my comment in the answer for why I convert everything to varchar. You also came up with the same answer but didn't add it as an answer. – Caynadian Commented Feb 17 at 1:16

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