CODEWITHSUNDEEP
xmlt-sqltags
Bruno Bieri
Bruno Bieri

Reputation: 10236

TSQL for xml prevent empty- and self-closing tags

A while ago I asked this question: TSQL for xml xml-tag just once

Now I've the issue that empty- and self-closing tags shouldn't appear in the xml.

I found possible workarounds here and here but it doesn't work like I expect.

I also tried to write "$$" in each potential empty tag then at the end I tried to remove all the tags containing "$$" with:

SET @persons.modify('delete (//$$)')

but this approach does not work either.

How can I remove empty- and self-closing tags with XQuery or prevent them to appear within TSQL for xml?

Upvotes: 0

Views: 3464

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

It depends on how you write your query but generally null values is not included in the resulting XML so if you want to omit empty values you can use nullif to get a null value instead of empty.

declare @Person table
(
  Name varchar(20)
)

insert into @Person values ('Person1')
insert into @Person values ('Person2')
insert into @Person values ('')
insert into @Person values (null)

select '2012' as period,
       (
        select nullif(Person.Name, '') as 'users/person'
        from @Person as Person
        for xml path('') , type
       )
for xml path(''), root('company')

Result:

<company>
  <period>2012</period>
  <users>
    <person>Person1</person>
  </users>
  <users>
    <person>Person2</person>
  </users>
</company>

Upvotes: 2

Related Questions