I've built a query that will output results in XML format. However, I am running into issue where any columns associated with NULL value are part of the output. I'd like to know how to write T-SQL code where it will exclude columns from being outputted if the value is NULL. Please let me know how to address this issue.
Are you using PATH mode? Books Online - Using PATH Mode, section "Columns that Contain a Null Value By Default": By default, a null value in a column maps to the absence of the attribute, node, or element. This default behavior can be overwritten by requesting element-centric XML using the ELEMENTS directive and specifying XSINIL to request adding elements for NULL valuesStandard syntax is: FOR XML PATH or FOR XML PATH, ELEMENTS To include null attributes in the XML, you would need to add XSINIL: FOR XML PATH, ELEMENTS XSINIL
Yes. I am using "For XML Path". Below is an output. The Element <RefNo> is part of the output. However, it doesn't contain any value. I'd like to exclude RefNo column from output if it's null. It seems all three options recomended doesn't seem to provide desired result. <Customer> <CustID>531</CustID> < RefNo></RefNo><Loc>S</Loc>
I think I may have been able to answer my own question. However, everyones response was greatly appreciated. Value associated with each column didn't have NULL but I think it was white space? Therefore, when I tried to exclude using where condition it failed. Even when I tried to use ' ' on where clause it failed. I winded up re-importing the dataset from Excel into SQL and on second try SQL assigned NULL value and voila! I didn't even have to use where clause.
Yes. I am using "For XML Path". Below is an output. The Element <RefNo> is part of the output. However, it doesn't contain any value. I'd like to exclude RefNo column from output if it's null. It seems all three options recomended doesn't seem to provide desired result.< Customer> <CustID>531</CustID> < RefNo></RefNo><Loc>S</Loc>