NULL elements in FOR XML Clause / Alternative to XSINIL

Question

I have some legacy code similar to:

 ...
 '<field1>' +   
 case when field1 is null then '' else cast( field1 as varchar ) end +   
 '</field1>' +  
 ...

Which generates the following XML for empty elements:

 ....
 <field1></field1>
 ...

And I'm replacing the query with FOR XML:

 SELECT field1, 
 ...
 FOR XML RAW, ELEMENTS

Now, this does not output an element for columns with NULL values. I know about XSINIL:

 FOR XML RAW, ELEMENTS XSINIL

But this generates a namespaced empty XML element, which is not compatible with the legacy code reading this output.

 ...
 <field1 xsi:nil="true" />
 ...

Any suggestions on generating the format below while still using the FOR XML Clause?

 ....
 <field1></field1>
 ...

Thanks!

Answer 1

One very simple solution would be: just don't specify the "XSINIL" after ELEMENTS!

 FOR XML RAW, ELEMENTS

In that case, you'll just get no XML entry for any values that are NULL.

If you really want an empty XML tag, you need to use something like this:

SELECT
   ......
   ISNULL(CAST(field1 AS VARCHAR(100)), '') AS 'field1',
   ......
FROM dbo.YourTable
FOR XML RAW, ELEMENTS

thus turning the empty field1 into an empty string and thus serializing it into the XML.