T-SQL: Convert XML to string without self-closing tags

Question

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

<BOOKING>
  <BOOKINGID>56812</BOOKINGID>
  <PERSONCODE />
</BOOKING>

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:

<BOOKING>
  <BOOKINGID>56812</BOOKINGID>
  <PERSONCODE></PERSONCODE>
</BOOKING>

Answer 1

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 = '
<BOOKING>
  <BOOKINGID>56812</BOOKINGID>
  <PERSONCODE />
</BOOKING>'

-- 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