The For XML clause not returning Columns with NULL Values

• Syed Rizwan Muhammad Rizvi

  SSC Rookie

  Points: 30

  More actions

  October 28, 2005 at 6:14 am

  #91599

  Hi,

  I found out a few days ago that using FOR XML clause is much faster than getting a recordset and parsing it into XML. But I have been facing some issues the one at top which is distrubing me is that today when I tried a query like : -

  Select * from Orders For XML Auto, Elements

  the above wasnt returning a few columns at the end, so I tested it further by trying: -

  1. Select Top 5 InvoiceID from Orders For XML Auto, Elements

  and

  2. Select Distinct InvoiceID from Orders For XML Auto, Elements

  The first query only returned empty Orders tags with no tag representing the column InvoiceID

  Whereas, the second one returned InvoiceID Elements, the reason being that the first query was returning the InvoiceIDs which were all null and the other one returned IDs which were not NULL.

  I need it to return all columns even if they contain null values in rows in the returning recordset. Is there a way to get by this.

   

  Regards,

  Rizwan.

• Syed Rizwan Muhammad Rizvi

  SSC Rookie

  Points: 30

  More actions

  October 31, 2005 at 5:32 am

  #601258

  Comon ppl. dont just see and ignore.