Take the 2-minute tour ×
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. It's 100% free, no registration required.

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>
share|improve this question
2  
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
add comment

1 Answer

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
share|improve this answer
add comment

Your Answer

 
discard

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.