Select query with SQL XML empty element-CodePudding

I wrote a query to get the data from table in XML format, but if column doesn't have any data then its not returning the data in the XML output. Please let me know how to fix this.

I need to get the output even though their is no data in the table column with empty tag -like this "</BatchEntryId>". Here BatchEntryId is NULL in the table

My query :

SELECT 
    Data.value('(/Data/Reference)[1]', 'nvarchar(10)') AS PolicyNumber,
    [RequestId],
    [BatchEntryId],
    [StatusCode],
    [PaymentMethodCode],
    Data.value('(/Data/Amount)[1]', 'nvarchar(10)') AS Amount
FROM 
    [dbo].[TransmissionData]
WHERE 
    RequestId = 2031070233
FOR XML RAW ('RequestRecord'), ELEMENTS, TYPE

My output:

<RequestRecord>
    <PolicyNumber>Policy034</PolicyNumber>
    <RequestId>2031070233</RequestId>
    <StatusCode>A</StatusCode>
    <PaymentMethodCode>1XCC</PaymentMethodCode>
    <Amount>200.00</Amount>
</RequestRecord>

The problem is 'BatchEntryId' which I did not get in the output XML, because that column has NULL value. But I need that also in the output XML as an empty tag, like this </BatchEntryId>.

Please let me know, how to fix this.

I am looking for output like this:

<RequestRecord>
    <PolicyNumber>Policy034</PolicyNumber>
    <RequestId>2031070233</RequestId>
    <BatchEntryId/>
    <StatusCode>A</StatusCode>
    <PaymentMethodCode>1XCC</PaymentMethodCode>
    <Amount>200.00</Amount>
</RequestRecord>

CodePudding user response：

You can ISNULL it to an empty string

SELECT Data.value('(/Data/Reference)[1]', 'nvarchar(10)') as  PolicyNumber
       ,[RequestId]
       ,ISNULL([BatchEntryId], '') AS BatchEntryId
       ,[StatusCode]
       ,[PaymentMethodCode]
       ,Data.value('(/Data/Amount)[1]', 'nvarchar(10)') as  Amount
    FROM [dbo].[TransmissionData]
    WHERE RequestId = 2031070233
    FOR XML RAW ('RequestRecord'), ELEMENTS, TYPE

If BatchEntryId is not varchar or nvarchar you should cast it first

ISNULL(CAST(BatchEntryId AS varchar(30)), '') AS BatchEntryId

Note that SQL Server generates it as

<BatchEntryId></BatchEntryId>

however this is semantically equivalent in XML to

<BatchEntryId/>