Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We have a weirdo system we're integrating with which consumes XML, but it does not understand self-closing tags, so


is beyond its capabilities.

If what I have is declare @x xml, how do I convert that @x to string without having self-closing tags:

share|improve this question
Is it acceptible to not include the empty elements at all, or are they required? Also, are you creating the XML output by concatenating strings, or by using FOR XML on your queries? – Jon Seigel Aug 27 '12 at 15:42

A workaround would be to add an unlikely value to your empty nodes using insert (XML DML) and then replace the value on your string representation.

declare @S nvarchar(max)

declare @XML xml = '

-- Add _ as value to all empty nodes
while @XML.exist('//*[.="" and count(*)=0]') = 1
  set @XML.modify('insert text{"_"} into (//*[.="" and count(*)= 0])[1]')

-- Cast to string and remove _ as value
set @S = replace(cast(@XML as nvarchar(max)), '>_<', '><')

select @S
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.