How can I make FOR XML PATH return empty elements for an empty result?

Question

How can I make FOR XML PATH return elements when my query returns an empty result?

This is what my query looks like:

 DECLARE @StatementNumber int = 2577

 SELECT
     sl.statement_number [StatementNumber], sl.STATEMENT_LINE [StatementLine], 
     CASE sl.CHARGE_CREDIT_TYPE
         WHEN 'C' THEN
                      CASE sl.LINE_TYPE
                          WHEN 'PP' THEN 'P'
                          ELSE 'C'
                      END
         WHEN 'R' THEN 'D'
         WHEN 'D' THEN 'D'
         WHEN 'F' THEN
                      CASE sl.ANTICIPATED_FLAG
                          WHEN 'Y' THEN 'F'
                          WHEN 'N' THEN 'D'
                      END
     END [GroupType],
     sl.ACADEMIC_YEAR [AcademicYear], sl.ACADEMIC_TERM [AcademicTerm],
     ct.MEDIUM_DESC [TermDesc], sl.ACADEMIC_SESSION [AcademicSession],
     cs.MEDIUM_DESC [SessionDesc],
     sl.CHARGE_CREDIT_TYPE [ChargeCreditType], sl.AMOUNT [Amount], 
     sl.DESCRIPTION [Description], sl.ENTRY_DATE [EntryDate], 
     sl.STATEMENT_MESSAGE [StatementMessage], sl.LINE_TYPE [LineType],  
     sl.ANTICIPATED_FLAG [AnticipatedFlag], 
     sl.PRINT_ORDER [PrintOrder], sl.CHARGECREDITNUMBER [ChargeCreditNumber], 
     sl.PAYMENT_OPTION [PaymentOption], sl.PAYPLAN_ELIGIBLE [PayplanEligible], 
     sl.SCHED_PAYMENT_AMT [SchedulePaymentAmount]
 FROM
     dbo.STATEMENTLINE sl
 LEFT OUTER JOIN
     dbo.CODE_ACATERM ct ON ct.CODE_VALUE_KEY = sl.ACADEMIC_TERM
 LEFT OUTER JOIN
     dbo.CODE_ACASESSION cs ON cs.CODE_VALUE_KEY = sl.ACADEMIC_SESSION
 WHERE
     sl.STATEMENT_NUMBER = @StatementNumber 
     AND (sl.LINE_TYPE = 'CC' OR sl.LINE_TYPE = 'CR' OR sl.LINE_TYPE = 'PP' )
 ORDER BY 
     GroupType, sl.ENTRY_DATE, sl.PRINT_ORDER 
 FOR XML PATH('StatementDetail'), ELEMENTS XSINIL;

What I want is that even if my query return empty values I still want to be able to see this.

Desired output:

 <StatementDetail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
     <StatementNumber/>
     <StatementLine/>
     <GroupType/>
     <AcademicYear/>
     <AcademicTerm/>
     <TermDesc/>
     <AcademicSession/>
     <SessionDesc/>
     <ChargeCreditType/>
     <Amount/>
     <Description/>
     <EntryDate/>
     <StatementMessage >
     <LineType/>
     <AnticipatedFlag/>
     <PrintOrder/>
     <ChargeCreditNumber/>
     <PaymentOption/>
     <PayplanEligible/>
     <SchedulePaymentAmount/>
 </StatementDetail>

This is what it looks like when I change the statementNumber variable and my query returns values:

 <StatementDetail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
     <StatementNumber>2576</StatementNumber>
     <StatementLine>1</StatementLine>
     <GroupType>C</GroupType>
     <AcademicYear>2021</AcademicYear>
     <AcademicTerm>FALL</AcademicTerm>
     <TermDesc>Fall</TermDesc>
     <AcademicSession/>
     <SessionDesc xsi:nil="true"/>
     <ChargeCreditType>C</ChargeCreditType>
     <Amount>75.000000</Amount>
     <Description>SOC 101 - Course Fee</Description>
     <EntryDate>2021-12-15T00:00:00</EntryDate>
     <StatementMessage xsi:nil="true"/>
     <LineType>CC</LineType>
     <AnticipatedFlag>N</AnticipatedFlag>
     <PrintOrder>015</PrintOrder>
     <ChargeCreditNumber>19</ChargeCreditNumber>
     <PaymentOption>Y</PaymentOption>
     <PayplanEligible>Y</PayplanEligible>
     <SchedulePaymentAmount>0.000000</SchedulePaymentAmount>
 </StatementDetail>

Answer 1

Please try the following conceptual solution.

It is using SQL Server's XQuery power.

You can modify @state parameter value, anything outside of TX or FL, and see what it does.

For your real scenario, just replace the following SELECT statement with you real SELECT.

SELECT * 
FROM @City
WHERE State = @state
FOR XML PATH('StatementDetail'), ELEMENTS XSINIL

SQL

-- DDL and sample data population, start
DECLARE @City TABLE (ID INT IDENTITY PRIMARY KEY, State CHAR(2), City VARCHAR(20));
INSERT INTO @City (State, City) VALUES
('FL', 'Miami'),
('TX', 'Dallas');
-- DDL and sample data population, end

DECLARE @state CHAR(2) = 'FL';

SELECT TRY_CAST(COALESCE((
SELECT * 
FROM @City
WHERE State = @state
FOR XML PATH('StatementDetail'), ELEMENTS XSINIL
), '') AS XML)
.query('if (count(/StatementDetail) gt 0) then .
else
<StatementDetail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <StatementNumber/>
    <StatementLine/>
    <GroupType/>
    <AcademicYear/>
    <AcademicTerm/>
    <TermDesc/>
    <AcademicSession/>
    <SessionDesc/>
    <ChargeCreditType/>
    <Amount/>
    <Description/>
    <EntryDate/>
    <StatementMessage/>
    <LineType/>
    <AnticipatedFlag/>
    <PrintOrder/>
    <ChargeCreditNumber/>
    <PaymentOption/>
    <PayplanEligible/>
    <SchedulePaymentAmount/>
</StatementDetail>');