NULL elements in FOR XML Clause / Alternative to XSINIL

I have some legacy code similar to:

 ...
 '<field1>' +   
 case when field1 is null then '' else cast( field1 as varchar ) end +   
 '</field1>开发者_Go百科;' +  
 ...

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!

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.

You can double up on the columns that can have a null value with an empty string. The values will be concatenated and the empty string makes sure you will always have something that builds the node.

You need to use for xml path instead of for xml raw.

declare @T table
(
  Col1 int,
  Col2 int
)

insert into @T values(1, 2)
insert into @T values(1, null)
insert into @T values(null, 2)
insert into @T values(null, null)

select Col1,
       '' as Col1,
       Col2,
       '' as Col2
from @T
for xml path('row')

Result:

<row>
  <Col1>1</Col1>
  <Col2>2</Col2>
</row>
<row>
  <Col1>1</Col1>
  <Col2></Col2>
</row>
<row>
  <Col1></Col1>
  <Col2>2</Col2>
</row>
<row>
  <Col1></Col1>
  <Col2></Col2>
</row>

you can avoid labor work of isnull by this

    declare @Var varchar(max)
set @Var=(select 
FirstName,
LastName,
Middldle_Name
From Customer 
FOR XML AUTO, ELEMENTS xsinil , ROOT('Customer')
)
select CONVERT(xml,REPLACE(@Var,' xsi:nil="true"',''))