snippetsqlMinor

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

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

withoutsqlconvertxmltagsstringclosingself

Problem

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:

Solution

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

  56812
  
'

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

Code Snippets

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

Context

StackExchange Database Administrators Q#23200, answer score: 4

Revisions (0)

No revisions yet.