T-SQL: Convert XML to string without self-closing tags We have a weirdo system we're integrating with which consumes XML, but it does not understand self-closing tags, so

&lt;BOOKING&gt;
  &lt;BOOKINGID&gt;56812&lt;/BOOKINGID&gt;
  &lt;PERSONCODE /&gt;
&lt;/BOOKING&gt;


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:

&lt;BOOKING&gt;
  &lt;BOOKINGID&gt;56812&lt;/BOOKINGID&gt;
  &lt;PERSONCODE&gt;&lt;/PERSONCODE&gt;
&lt;/BOOKING&gt;
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 = '
&lt;BOOKING&gt;
  &lt;BOOKINGID&gt;56812&lt;/BOOKINGID&gt;
  &lt;PERSONCODE /&gt;
&lt;/BOOKING&gt;'

-- 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)), '&gt;_&lt;', '&gt;&lt;')

select @S