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!

Solution 1

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>

Solution 2

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.

Comments

[moogs]

the "simple solution" is not compatible with the legacy stuff that reads the output. if barfs when the element is not present. this ISNULL thing is nice, but a bit cumbersome.

[moogs]

that comment above sounded a bit cold. i'm grateful for the answer and I'm using this now! unless others come up.