SQL server changes XML structure when inserted

I'm inserting some XML data to an XML column in SQL server but after the data has been inserted it has been changed by sql server.
Here is the data I insert

When I read it back, it looks like this

Pay attention to the second line. This is a problem because it changes how the XSLT transformation output will be. The first example will create a space between given and family name, while the second will not create any space, so it will be like JohnJohnsen, while the first one will be like John Johnsen.

Is there some way to solve this?

You can use xml:space = "preserve" on the nodes where you want to keep the space. Using xml:space is "only a signal of intent" but SQL server is kind to us here.

For one node

declare @X xml =
'


'

select @X;


Result:







Entire document:

declare @X xml =
'


'

select @X;


Result:







Another options for the entire document is to use convert with style 1.


Preserve insignificant white space. This style setting sets the
default xml:space handling to match the behavior of
xml:space="preserve".

declare @X xml = convert(xml, 
'
   
   
', 1)

select @X;

StackExchange Database Administrators Q#223496, answer score: 26