CODEWITHSUNDEEP
sqlsql-serverxmlt-sql
emery.noel
emery.noel

Reputation: 1185

Sql2012 FOR XML Inconsistent "empty element" Behavior

I have an empty string in a table that I am selecting into XML. Ideally, this will produce an empty element, like <name /> instead of <name></name>.

It seems that if my empty string is the only thing I select, I get the undesired (verbose) empty tag ... but if I select other things, including another empty string (or the same empty string), I get the desirable empty tag ().

Can anyone tell me why this happens? And more importantly, how to control it?

declare @table table( TextValue nvarchar(100) )
insert into @table( TextValue ) values ( '' )

select
    ( select t.TextValue name for xml path( '' ), type )
from @table t
for xml path( 'row' ), root( 'root' ), type
/* result:
<root>
  <row>
    <name></name> <!-- select just once, verbose -->
  </row>
</root> 
*/

select
    ( select t.TextValue name for xml path( '' ), type ),
    ( select t.TextValue name for xml path( '' ), type )
from @table t
for xml path( 'row' ), root( 'root' ), type
/* result:
<root>
  <row>
    <name /> <!-- select twice, nice and neat -->
    <name />
  </row>
</root>
*/

Thanks!

Upvotes: 0

Views: 63

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

It made me curious too:

That's what I came up with:

If you tell the XML engine to create XML it is done the way

  • open the element
  • fill in the content
  • close the element

All of these example lead to <element></element>

select '' name
for xml path( 'row' )

select '' name
      ,'' name2       
for xml path( 'row' )

select '' name
      ,''
      ,'' name 
for xml path( 'row' )

But if the XML-elment was created before the XML Engine is dealing with it, the (better) short form is used.

All of them lead to <element/>

select CAST('<name></name>' AS XML)
for xml path( 'row' )

select CAST('<name/>' AS XML)
for xml path( 'row' )

select (SELECT '' AS name FOR XML PATH(''),TYPE)
for xml path( 'row' )

UPDATE

You even can combine this

select (SELECT '' AS name FOR XML PATH(''),TYPE)
       ,'' AS name
for xml path( 'row' )

leads to

<row>
  <name />
  <name></name>
</row>

UPDATE 2

I think this has nothing to do with the element name's length. Neither is it bound to the count of columns you call. This is merely bound to: Is the XML created this moment or was it create before?

DECLARE @n1 VARCHAR(100)='';
DECLARE @n2 XML='<name></name>';
DECLARE @n3 XML='<name/>';

SELECT @n1 AS name FOR XML PATH('root');
SELECT @n2 FOR XML PATH('root');
SELECT @n3 FOR XML PATH('root');

Upvotes: 1

Related Questions