You have a FOR XML query using ELEMENTS directive. You query  ignores columns having NULL value and you want to include them. What should you do ?

Place the mouse cursor HERE for the answer

---

Cristian Andrei Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET,
MCTS, MCITP - Database Administrator SQL Server 2005
http://sqlserver.ro

...is it al right to specify an ISNULL function in the select list, for example:
       SELECT Col1, ISNULL(Col2, 0) FROM Tab1
       FOR XML AUTO, ELEMENTS
    or
    SELECT Col1, ISNULL(Col2, '') FROM Tab1
       FOR XML AUTO, ELEMENTS
   
    ...the result for Col2 will look like <Col2>0</Col2> or <Col2></Col2>

It depends on the case, it can be a method, however 0 <> NULL, and <firstname xsi:nil="true"></firstname> is different from <fname></fname>.

 

---

Cristian Andrei Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET,
MCTS, MCITP - Database Administrator SQL Server 2005
http://sqlserver.ro