views:

402

answers:

3

How do I create a truly empty xml element with the for xml-directive in sql server (2005)?

Example:

select
    ''
for xml path('element'), root('elements')

Outputs:

<elements><element></element></elements>

But what I really want is:

<elements><element /></elements>
A: 

Add the type directive and then run it in a subquery.

Like this:

select
(
    select
        ''
    for xml path('element'), type
)
for xml path('elements')
Andyredbeard
A: 
select
    null
for xml path('element'), root('elements')
guille
That's a good suggestion, to make it work for empty strings instead of null, then one could do nullif(@string, '').
Andyredbeard
A: 

It does not work when you use Aliases and the value is null

arkal