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

LAI TOCA
2 min readJul 12, 2024

--

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

--

--

LAI TOCA

Coding for fun. (Either you are running for food or running for being food.)