SQL Server Performance

T-SQL Null or No Value Select Statement

Discussion in 'SQL Server 2005 General DBA Questions' started by jstevenson, Mar 4, 2010.

  1. jstevenson New Member

    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.
  2. Adriaan New Member

    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 values
    Standard 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
  3. jstevenson New Member

    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>
  4. PurpleLady New Member

    Try adding WHERE RefNo IS NOT NULL to your query
  5. jstevenson New Member

    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.
  6. jstevenson New Member

    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>

Share This Page