sql - NULL elements in FOR XML Clause / Alternative to XSINIL -

I have some legacy code:

  ... '& lt; Field1 & gt; '+ When the field 1 is empty then the rest of the cast (feature 1 varchar) end +' & lt; / Field1 & gt; ' + ...  

Generates the following elements for empty elements:

  ....  

And I am replacing the query for XML:

SELECT field1, ... for the XML RAW, element

Now, it does not produce an element for columns with zero values. I know about XSIINL: XML RAW, for Element XSINIL

, but it creates a Namescase empty XML element, which does not correspond to the legacy code reading.

  ... & lt; Field 1 xsi: nil = "true" /> ...  

Still using XML clause, any suggestions on preparing the format below?

  ....  

Thanks!

You can double-click the column which can have zero values ​​with the empty string. Values ​​will be collected and the empty string ensures that you will always have something that creates a node.

You need to use instead of for xml path . Insert @ T values ​​(1, 2) into the @ T values ​​(1, empty)

  @T table (Col1 int, Col2 int) Insert) in the @t values ​​(zero, 2) into the @ t values, select the call (empty, null) call 1, for "call 1, call2," as 2 @ t to xml path ( 'Line')  

Result:

  & lt; Line & gt; & Lt; Col1 & gt; 1 & lt; / Col1 & gt; & Lt; Col2 & gt; 2 & lt; / Col2 & gt; & Lt; / Row & gt; & Lt; Line & gt; & Lt; Col1 & gt; 1 & lt; / Col1 & gt; & Lt; Col2 & gt; & Lt; / Col2 & gt; & Lt; / Row & gt; & Lt; Line & gt; & Lt; Col1 & gt; & Lt; / Col1 & gt; & Lt; Col2 & gt; 2 & lt; / Col2 & gt; & Lt; / Row & gt; & Lt; Line & gt; & Lt; Col1 & gt; & Lt; / Col1 & gt; & Lt; Col2 & gt; & Lt; / Col2 & gt; & Lt; / Row & gt;