Force MSSQL return xml format ignore the null or empty value for the correspond element

Create by @https://www.bing.com/images/create

Suppose we have below sql script sippets:

            select [Header].[MESSAGE_ID]
                 , [Header].[ADJ_DATE]
                 , [Header].[ADJ_NO1]
                 , [Header].[ADJ_NO2]
                 , [Header].[PURPOSE_CODE]
                 , [Header].[WARE_HOUSE]
                 , [Header].[SEND_FLAG]
                 , [Header].[CREATE_TIME]
                 , [Header].[SENDER_ID]
                 , [Header].[ISA_ID]
                 , [Header].[MDN_TIME]
                 , [Header].[ISA13]
                 , [Header].[STATUS]
                 , [Header].[Valid]
                 , [Header].[CreateUser]
                 , [Header].[CreateDate]
                 , [Header].[UpdateUser]
                 , [Header].[UpdateDate]
                 , (
                         select [Detail].[MESSAGE_ID]
                              , [Detail].[SEQ]
                              , [Detail].[QTY]
                              , [Detail].[VENDOR_PART_NO]
                              , [Detail].[CONSUMER_PACK_NO]
                              , [Detail].[PART_DESC]
                              , [Detail].[TO_PROJ_CODE]
                              , [Detail].[FROM_PROJ_CODE]
                              , [Detail].[SHIP_FROM_LOC]
                              , [Detail].[SHIP_TO_LOC]
                              , [Detail].[REF_ID]
                              , [Detail].[REF_DESC]
                              , [Detail].[REF_DATE]
                              , [Detail].[REF_TIME]
                              , [Detail].[ApproveID] -- ApproveID' schema declare as nullable
                              , (
                                      select [Line].[MESSAGE_ID]
                                           , [Line].[DETAIL_SEQ]
                                           , [Line].[LX_NO]
                                           , [Line].[SERIAL_NO]
                                           , [Line].[SN_DESC]
                                           , [Line].[LOT_NO]
                                      from   [dbo].[EDI947_InventoryAdjustment_Detail_Line] as [Line]
                                      where  [MESSAGE_ID] = [Header].[MESSAGE_ID] and [DETAIL_SEQ] = [Detail].[SEQ]
                                      for xml path('LINE'), binary base64, type
                                )
                         from   [dbo].[EDI947_InventoryAdjustment_Detail] as [Detail]
                         where  [MESSAGE_ID] = [Header].[MESSAGE_ID]
                         for xml path('DETAIL'), binary base64, type
                   )
            from   [dbo].[EDI947_InventoryAdjustment_Header] as [Header]
            where  [MESSAGE_ID] = @MessageId
            for xml path('HEADER'), binary base64, type;

The sql script could simply generate below outcome:

<ROOT xmlns="http://www.xxxyyyzzz.com/947_FromInterface">
<HEADER xmlns="">
<MESSAGE_ID>0ac6d36f-9a47-4451-9629-d12d3226f548</MESSAGE_ID>
<ADJ_DATE>20230222</ADJ_DATE>
<ADJ_NO1>20230222T00003</ADJ_NO1>
<ADJ_NO2>20230222T00003</ADJ_NO2>
<PURPOSE_CODE>00</PURPOSE_CODE>
<WARE_HOUSE>XXXXXXXXX</WARE_HOUSE>
<SEND_FLAG>P</SEND_FLAG>
<SENDER_ID>ABC123</SENDER_ID>
<ISA_ID>123ABC</ISA_ID>
<SEND_MDN_TIME>2023-02-22T12:29:28.083</SEND_MDN_TIME>
<STATUS>Normal</STATUS>
<Valid>1</Valid>
<CreateUser>APP</CreateUser>
<CreateDate>2023-02-22T11:15:29.213</CreateDate>
<UpdateUser>APP</UpdateUser>
<UpdateDate>2023-02-22T11:15:29.213</UpdateDate>
<DETAIL>
<MESSAGE_ID>0ac6d36f-9a47-4451-9629-d12d3226f548</MESSAGE_ID>
<SEQ>1</SEQ>
<QTY>20</QTY>
<VENDOR_PART_NO>PART_NO1</VENDOR_PART_NO>
<TO_PROJ_CODE>MAIN</TO_PROJ_CODE>
<FROM_PROJ_CODE>MAIN</FROM_PROJ_CODE>
<SHIP_FROM_LOC>CYCLECOUNT</SHIP_FROM_LOC>
<SHIP_TO_LOC>LOC</SHIP_TO_LOC>
<REF_ID>1</REF_ID>
<REF_DESC>MRB</REF_DESC>
<REF_DATE>20230222</REF_DATE>
<REF_TIME>111530</REF_TIME>
<ApproveID/> <!-- We will self closed xml tag if ApproveID is empty string -->
</DETAIL>
</HEADER>
</ROOT>

The question is if we do not want the xml tag to appear (for instance: <ApproveID/>) from the query result?

The simply way we could adopt just modified the sql select query:

-- replace [Detail].[ApproveID] as below:
nullif ([Detail].[ApproveID], '') as [ApproveID]

Then if the [ApproveID] will disappear while the record is null or empty string but appear (<ApproveID>value</ApproveID>) while it remain actual value.

Reference

• https://stackoverflow.com/questions/12705382/tsql-for-xml-prevent-empty-and-self-closing-tags