SQL server changes XML structure when inserted












15














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



              <xsl:value-of select="name/n/given" />
<xsl:text> </xsl:text>
<xsl:value-of select="name/n/family" />


When I read it back, it looks like this



              <xsl:value-of select="name/n/given" />
<xsl:text />
<xsl:value-of select="name/n/family" />


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?










share|improve this question
























  • It is a problem, because this does change how the XSLT transformation output will be. First line will create a space between given and family name, while the second will not create any space between, so it will be like JohnJohnsen, while the first one will be like John Johnsen
    – Mr Zach
    Nov 27 at 1:26










  • hmhm, proper space it is " " but not just a space like in this comment (you can not see it)
    – a_vlad
    Nov 27 at 1:31






  • 1




    Perhaps you could use a control character that doesn't exist in the data (like _ or ~) and then replace that with a space at presentation time.
    – Aaron Bertrand
    Nov 27 at 1:42










  • Yes, I did as a quick fix, thank you.
    – Mr Zach
    Nov 27 at 1:51
















15














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



              <xsl:value-of select="name/n/given" />
<xsl:text> </xsl:text>
<xsl:value-of select="name/n/family" />


When I read it back, it looks like this



              <xsl:value-of select="name/n/given" />
<xsl:text />
<xsl:value-of select="name/n/family" />


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?










share|improve this question
























  • It is a problem, because this does change how the XSLT transformation output will be. First line will create a space between given and family name, while the second will not create any space between, so it will be like JohnJohnsen, while the first one will be like John Johnsen
    – Mr Zach
    Nov 27 at 1:26










  • hmhm, proper space it is " " but not just a space like in this comment (you can not see it)
    – a_vlad
    Nov 27 at 1:31






  • 1




    Perhaps you could use a control character that doesn't exist in the data (like _ or ~) and then replace that with a space at presentation time.
    – Aaron Bertrand
    Nov 27 at 1:42










  • Yes, I did as a quick fix, thank you.
    – Mr Zach
    Nov 27 at 1:51














15












15








15


2





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



              <xsl:value-of select="name/n/given" />
<xsl:text> </xsl:text>
<xsl:value-of select="name/n/family" />


When I read it back, it looks like this



              <xsl:value-of select="name/n/given" />
<xsl:text />
<xsl:value-of select="name/n/family" />


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?










share|improve this question















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



              <xsl:value-of select="name/n/given" />
<xsl:text> </xsl:text>
<xsl:value-of select="name/n/family" />


When I read it back, it looks like this



              <xsl:value-of select="name/n/given" />
<xsl:text />
<xsl:value-of select="name/n/family" />


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?







sql-server xml






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 27 at 8:19









Tom V

13.8k74676




13.8k74676










asked Nov 27 at 0:53









Mr Zach

2075




2075












  • It is a problem, because this does change how the XSLT transformation output will be. First line will create a space between given and family name, while the second will not create any space between, so it will be like JohnJohnsen, while the first one will be like John Johnsen
    – Mr Zach
    Nov 27 at 1:26










  • hmhm, proper space it is " " but not just a space like in this comment (you can not see it)
    – a_vlad
    Nov 27 at 1:31






  • 1




    Perhaps you could use a control character that doesn't exist in the data (like _ or ~) and then replace that with a space at presentation time.
    – Aaron Bertrand
    Nov 27 at 1:42










  • Yes, I did as a quick fix, thank you.
    – Mr Zach
    Nov 27 at 1:51


















  • It is a problem, because this does change how the XSLT transformation output will be. First line will create a space between given and family name, while the second will not create any space between, so it will be like JohnJohnsen, while the first one will be like John Johnsen
    – Mr Zach
    Nov 27 at 1:26










  • hmhm, proper space it is " " but not just a space like in this comment (you can not see it)
    – a_vlad
    Nov 27 at 1:31






  • 1




    Perhaps you could use a control character that doesn't exist in the data (like _ or ~) and then replace that with a space at presentation time.
    – Aaron Bertrand
    Nov 27 at 1:42










  • Yes, I did as a quick fix, thank you.
    – Mr Zach
    Nov 27 at 1:51
















It is a problem, because this does change how the XSLT transformation output will be. First line will create a space between given and family name, while the second will not create any space between, so it will be like JohnJohnsen, while the first one will be like John Johnsen
– Mr Zach
Nov 27 at 1:26




It is a problem, because this does change how the XSLT transformation output will be. First line will create a space between given and family name, while the second will not create any space between, so it will be like JohnJohnsen, while the first one will be like John Johnsen
– Mr Zach
Nov 27 at 1:26












hmhm, proper space it is " " but not just a space like in this comment (you can not see it)
– a_vlad
Nov 27 at 1:31




hmhm, proper space it is " " but not just a space like in this comment (you can not see it)
– a_vlad
Nov 27 at 1:31




1




1




Perhaps you could use a control character that doesn't exist in the data (like _ or ~) and then replace that with a space at presentation time.
– Aaron Bertrand
Nov 27 at 1:42




Perhaps you could use a control character that doesn't exist in the data (like _ or ~) and then replace that with a space at presentation time.
– Aaron Bertrand
Nov 27 at 1:42












Yes, I did as a quick fix, thank you.
– Mr Zach
Nov 27 at 1:51




Yes, I did as a quick fix, thank you.
– Mr Zach
Nov 27 at 1:51










3 Answers
3






active

oldest

votes


















24














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 =
'<root>
<element xml:space = "preserve"> </element>
<element> </element>
</root>'

select @X;


Result:



<root>
<element xml:space="preserve"> </element>
<element />
</root>


Entire document:



declare @X xml =
'<root xml:space = "preserve">
<element> </element>
<element> </element>
</root>'

select @X;


Result:



<root xml:space="preserve">
<element> </element>
<element> </element>
</root>





share|improve this answer





















  • Interesting that this is required. It shouldn't be SQL Server's remit to decide what whitespace is "insignificant" and silently strip it without document modifications!
    – Lightness Races in Orbit
    Nov 27 at 10:39






  • 2




    @LightnessRacesinOrbit I'm quite happy with the implementation by SQL Server. Formatting (whitespace) in XML is not considered important until you say it is. Have a look at this example to see the number of nodes that are actually in the document and what it does to storage size..
    – Mikael Eriksson
    Nov 27 at 12:33








  • 3




    I consider it to be a spec violation, because here the data is accepted as XML and stored as XML, with no manipulation or transformation or any other form of XML-layer shenanigans other than simply storing the document (ostensibly), so the behaviour should fall into that of a "processor" rather than an "application", and therefore must not strip whitespace.
    – Lightness Races in Orbit
    Nov 27 at 12:58





















8














This page of the SQL Server documentation says




The data is stored in an internal representation that ... may not be an identical copy of the text XML, because the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration.




For your example I suppose it considers the middle tag's white space to be not significant and is therefore free to refactor the representation. I don't think there is a fix for this; it is just how SQL Server implements the XML data type.



Work-arounds would include using a place-holder instead of white space as @Aaron says. The consumer must remember to insert and strip out these tokens. Alternatively define the column as nvarchar instead of XML. This will definitely preserve all white space and any other formatting. A quick example:



create table x(i nvarchar(99), j xml);
insert x values ('<a> </a>', '<a> </a>'); -- note the space
select * from x

i j
---------- -------
<a> </a> <a />


The nvarchar column preserves the input format, the XML column does not.



You will lose the ability to use XPATH in SQL queries. If the XML is only shredded in the application this is immaterial. Further the character string could be compressed saving space in the DB, if this is significant for you.






share|improve this answer























  • You could probably still use XPATH in queries against the XML version, even if you just let it reformat, as long as you aren't relying on a hit (or miss) for the insignificant space there.
    – Aaron Bertrand
    Nov 27 at 2:49



















0














You could wrap your space within CDATA when storing the data:



<xsl:text><![CDATA[ ]]></xsl:text>


It appears that SQL server then keeps the space internally, but removes the unnecessary CDATA markup itself when getting the result back using SELECT. Fortunately, the space is kept when re-using the result of such a SELECT:



DECLARE @X XML = '<text><![CDATA[ ]]></text>'
DECLARE @Y XML

SET @Y = (SELECT @X)

SELECT @Y


The result will be:



<text> </text>





share|improve this answer





















  • Also tried CDATA but it was also removed.
    – Mr Zach
    Nov 27 at 16:45










  • @MrZach CDATA itself is removed, but the space remains. (Tried on SQL Express 2016.)
    – Bruno
    Nov 27 at 16:46












  • Strange, here the space was removed. Think also express 2016 or 2017
    – Mr Zach
    Nov 27 at 17:09











Your Answer








StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f223496%2fsql-server-changes-xml-structure-when-inserted%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























3 Answers
3






active

oldest

votes








3 Answers
3






active

oldest

votes









active

oldest

votes






active

oldest

votes









24














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 =
'<root>
<element xml:space = "preserve"> </element>
<element> </element>
</root>'

select @X;


Result:



<root>
<element xml:space="preserve"> </element>
<element />
</root>


Entire document:



declare @X xml =
'<root xml:space = "preserve">
<element> </element>
<element> </element>
</root>'

select @X;


Result:



<root xml:space="preserve">
<element> </element>
<element> </element>
</root>





share|improve this answer





















  • Interesting that this is required. It shouldn't be SQL Server's remit to decide what whitespace is "insignificant" and silently strip it without document modifications!
    – Lightness Races in Orbit
    Nov 27 at 10:39






  • 2




    @LightnessRacesinOrbit I'm quite happy with the implementation by SQL Server. Formatting (whitespace) in XML is not considered important until you say it is. Have a look at this example to see the number of nodes that are actually in the document and what it does to storage size..
    – Mikael Eriksson
    Nov 27 at 12:33








  • 3




    I consider it to be a spec violation, because here the data is accepted as XML and stored as XML, with no manipulation or transformation or any other form of XML-layer shenanigans other than simply storing the document (ostensibly), so the behaviour should fall into that of a "processor" rather than an "application", and therefore must not strip whitespace.
    – Lightness Races in Orbit
    Nov 27 at 12:58


















24














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 =
'<root>
<element xml:space = "preserve"> </element>
<element> </element>
</root>'

select @X;


Result:



<root>
<element xml:space="preserve"> </element>
<element />
</root>


Entire document:



declare @X xml =
'<root xml:space = "preserve">
<element> </element>
<element> </element>
</root>'

select @X;


Result:



<root xml:space="preserve">
<element> </element>
<element> </element>
</root>





share|improve this answer





















  • Interesting that this is required. It shouldn't be SQL Server's remit to decide what whitespace is "insignificant" and silently strip it without document modifications!
    – Lightness Races in Orbit
    Nov 27 at 10:39






  • 2




    @LightnessRacesinOrbit I'm quite happy with the implementation by SQL Server. Formatting (whitespace) in XML is not considered important until you say it is. Have a look at this example to see the number of nodes that are actually in the document and what it does to storage size..
    – Mikael Eriksson
    Nov 27 at 12:33








  • 3




    I consider it to be a spec violation, because here the data is accepted as XML and stored as XML, with no manipulation or transformation or any other form of XML-layer shenanigans other than simply storing the document (ostensibly), so the behaviour should fall into that of a "processor" rather than an "application", and therefore must not strip whitespace.
    – Lightness Races in Orbit
    Nov 27 at 12:58
















24












24








24






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 =
'<root>
<element xml:space = "preserve"> </element>
<element> </element>
</root>'

select @X;


Result:



<root>
<element xml:space="preserve"> </element>
<element />
</root>


Entire document:



declare @X xml =
'<root xml:space = "preserve">
<element> </element>
<element> </element>
</root>'

select @X;


Result:



<root xml:space="preserve">
<element> </element>
<element> </element>
</root>





share|improve this answer












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 =
'<root>
<element xml:space = "preserve"> </element>
<element> </element>
</root>'

select @X;


Result:



<root>
<element xml:space="preserve"> </element>
<element />
</root>


Entire document:



declare @X xml =
'<root xml:space = "preserve">
<element> </element>
<element> </element>
</root>'

select @X;


Result:



<root xml:space="preserve">
<element> </element>
<element> </element>
</root>






share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 27 at 6:55









Mikael Eriksson

17.6k34684




17.6k34684












  • Interesting that this is required. It shouldn't be SQL Server's remit to decide what whitespace is "insignificant" and silently strip it without document modifications!
    – Lightness Races in Orbit
    Nov 27 at 10:39






  • 2




    @LightnessRacesinOrbit I'm quite happy with the implementation by SQL Server. Formatting (whitespace) in XML is not considered important until you say it is. Have a look at this example to see the number of nodes that are actually in the document and what it does to storage size..
    – Mikael Eriksson
    Nov 27 at 12:33








  • 3




    I consider it to be a spec violation, because here the data is accepted as XML and stored as XML, with no manipulation or transformation or any other form of XML-layer shenanigans other than simply storing the document (ostensibly), so the behaviour should fall into that of a "processor" rather than an "application", and therefore must not strip whitespace.
    – Lightness Races in Orbit
    Nov 27 at 12:58




















  • Interesting that this is required. It shouldn't be SQL Server's remit to decide what whitespace is "insignificant" and silently strip it without document modifications!
    – Lightness Races in Orbit
    Nov 27 at 10:39






  • 2




    @LightnessRacesinOrbit I'm quite happy with the implementation by SQL Server. Formatting (whitespace) in XML is not considered important until you say it is. Have a look at this example to see the number of nodes that are actually in the document and what it does to storage size..
    – Mikael Eriksson
    Nov 27 at 12:33








  • 3




    I consider it to be a spec violation, because here the data is accepted as XML and stored as XML, with no manipulation or transformation or any other form of XML-layer shenanigans other than simply storing the document (ostensibly), so the behaviour should fall into that of a "processor" rather than an "application", and therefore must not strip whitespace.
    – Lightness Races in Orbit
    Nov 27 at 12:58


















Interesting that this is required. It shouldn't be SQL Server's remit to decide what whitespace is "insignificant" and silently strip it without document modifications!
– Lightness Races in Orbit
Nov 27 at 10:39




Interesting that this is required. It shouldn't be SQL Server's remit to decide what whitespace is "insignificant" and silently strip it without document modifications!
– Lightness Races in Orbit
Nov 27 at 10:39




2




2




@LightnessRacesinOrbit I'm quite happy with the implementation by SQL Server. Formatting (whitespace) in XML is not considered important until you say it is. Have a look at this example to see the number of nodes that are actually in the document and what it does to storage size..
– Mikael Eriksson
Nov 27 at 12:33






@LightnessRacesinOrbit I'm quite happy with the implementation by SQL Server. Formatting (whitespace) in XML is not considered important until you say it is. Have a look at this example to see the number of nodes that are actually in the document and what it does to storage size..
– Mikael Eriksson
Nov 27 at 12:33






3




3




I consider it to be a spec violation, because here the data is accepted as XML and stored as XML, with no manipulation or transformation or any other form of XML-layer shenanigans other than simply storing the document (ostensibly), so the behaviour should fall into that of a "processor" rather than an "application", and therefore must not strip whitespace.
– Lightness Races in Orbit
Nov 27 at 12:58






I consider it to be a spec violation, because here the data is accepted as XML and stored as XML, with no manipulation or transformation or any other form of XML-layer shenanigans other than simply storing the document (ostensibly), so the behaviour should fall into that of a "processor" rather than an "application", and therefore must not strip whitespace.
– Lightness Races in Orbit
Nov 27 at 12:58















8














This page of the SQL Server documentation says




The data is stored in an internal representation that ... may not be an identical copy of the text XML, because the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration.




For your example I suppose it considers the middle tag's white space to be not significant and is therefore free to refactor the representation. I don't think there is a fix for this; it is just how SQL Server implements the XML data type.



Work-arounds would include using a place-holder instead of white space as @Aaron says. The consumer must remember to insert and strip out these tokens. Alternatively define the column as nvarchar instead of XML. This will definitely preserve all white space and any other formatting. A quick example:



create table x(i nvarchar(99), j xml);
insert x values ('<a> </a>', '<a> </a>'); -- note the space
select * from x

i j
---------- -------
<a> </a> <a />


The nvarchar column preserves the input format, the XML column does not.



You will lose the ability to use XPATH in SQL queries. If the XML is only shredded in the application this is immaterial. Further the character string could be compressed saving space in the DB, if this is significant for you.






share|improve this answer























  • You could probably still use XPATH in queries against the XML version, even if you just let it reformat, as long as you aren't relying on a hit (or miss) for the insignificant space there.
    – Aaron Bertrand
    Nov 27 at 2:49
















8














This page of the SQL Server documentation says




The data is stored in an internal representation that ... may not be an identical copy of the text XML, because the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration.




For your example I suppose it considers the middle tag's white space to be not significant and is therefore free to refactor the representation. I don't think there is a fix for this; it is just how SQL Server implements the XML data type.



Work-arounds would include using a place-holder instead of white space as @Aaron says. The consumer must remember to insert and strip out these tokens. Alternatively define the column as nvarchar instead of XML. This will definitely preserve all white space and any other formatting. A quick example:



create table x(i nvarchar(99), j xml);
insert x values ('<a> </a>', '<a> </a>'); -- note the space
select * from x

i j
---------- -------
<a> </a> <a />


The nvarchar column preserves the input format, the XML column does not.



You will lose the ability to use XPATH in SQL queries. If the XML is only shredded in the application this is immaterial. Further the character string could be compressed saving space in the DB, if this is significant for you.






share|improve this answer























  • You could probably still use XPATH in queries against the XML version, even if you just let it reformat, as long as you aren't relying on a hit (or miss) for the insignificant space there.
    – Aaron Bertrand
    Nov 27 at 2:49














8












8








8






This page of the SQL Server documentation says




The data is stored in an internal representation that ... may not be an identical copy of the text XML, because the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration.




For your example I suppose it considers the middle tag's white space to be not significant and is therefore free to refactor the representation. I don't think there is a fix for this; it is just how SQL Server implements the XML data type.



Work-arounds would include using a place-holder instead of white space as @Aaron says. The consumer must remember to insert and strip out these tokens. Alternatively define the column as nvarchar instead of XML. This will definitely preserve all white space and any other formatting. A quick example:



create table x(i nvarchar(99), j xml);
insert x values ('<a> </a>', '<a> </a>'); -- note the space
select * from x

i j
---------- -------
<a> </a> <a />


The nvarchar column preserves the input format, the XML column does not.



You will lose the ability to use XPATH in SQL queries. If the XML is only shredded in the application this is immaterial. Further the character string could be compressed saving space in the DB, if this is significant for you.






share|improve this answer














This page of the SQL Server documentation says




The data is stored in an internal representation that ... may not be an identical copy of the text XML, because the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration.




For your example I suppose it considers the middle tag's white space to be not significant and is therefore free to refactor the representation. I don't think there is a fix for this; it is just how SQL Server implements the XML data type.



Work-arounds would include using a place-holder instead of white space as @Aaron says. The consumer must remember to insert and strip out these tokens. Alternatively define the column as nvarchar instead of XML. This will definitely preserve all white space and any other formatting. A quick example:



create table x(i nvarchar(99), j xml);
insert x values ('<a> </a>', '<a> </a>'); -- note the space
select * from x

i j
---------- -------
<a> </a> <a />


The nvarchar column preserves the input format, the XML column does not.



You will lose the ability to use XPATH in SQL queries. If the XML is only shredded in the application this is immaterial. Further the character string could be compressed saving space in the DB, if this is significant for you.







share|improve this answer














share|improve this answer



share|improve this answer








edited Dec 18 at 9:18

























answered Nov 27 at 2:44









Michael Green

14.1k82957




14.1k82957












  • You could probably still use XPATH in queries against the XML version, even if you just let it reformat, as long as you aren't relying on a hit (or miss) for the insignificant space there.
    – Aaron Bertrand
    Nov 27 at 2:49


















  • You could probably still use XPATH in queries against the XML version, even if you just let it reformat, as long as you aren't relying on a hit (or miss) for the insignificant space there.
    – Aaron Bertrand
    Nov 27 at 2:49
















You could probably still use XPATH in queries against the XML version, even if you just let it reformat, as long as you aren't relying on a hit (or miss) for the insignificant space there.
– Aaron Bertrand
Nov 27 at 2:49




You could probably still use XPATH in queries against the XML version, even if you just let it reformat, as long as you aren't relying on a hit (or miss) for the insignificant space there.
– Aaron Bertrand
Nov 27 at 2:49











0














You could wrap your space within CDATA when storing the data:



<xsl:text><![CDATA[ ]]></xsl:text>


It appears that SQL server then keeps the space internally, but removes the unnecessary CDATA markup itself when getting the result back using SELECT. Fortunately, the space is kept when re-using the result of such a SELECT:



DECLARE @X XML = '<text><![CDATA[ ]]></text>'
DECLARE @Y XML

SET @Y = (SELECT @X)

SELECT @Y


The result will be:



<text> </text>





share|improve this answer





















  • Also tried CDATA but it was also removed.
    – Mr Zach
    Nov 27 at 16:45










  • @MrZach CDATA itself is removed, but the space remains. (Tried on SQL Express 2016.)
    – Bruno
    Nov 27 at 16:46












  • Strange, here the space was removed. Think also express 2016 or 2017
    – Mr Zach
    Nov 27 at 17:09
















0














You could wrap your space within CDATA when storing the data:



<xsl:text><![CDATA[ ]]></xsl:text>


It appears that SQL server then keeps the space internally, but removes the unnecessary CDATA markup itself when getting the result back using SELECT. Fortunately, the space is kept when re-using the result of such a SELECT:



DECLARE @X XML = '<text><![CDATA[ ]]></text>'
DECLARE @Y XML

SET @Y = (SELECT @X)

SELECT @Y


The result will be:



<text> </text>





share|improve this answer





















  • Also tried CDATA but it was also removed.
    – Mr Zach
    Nov 27 at 16:45










  • @MrZach CDATA itself is removed, but the space remains. (Tried on SQL Express 2016.)
    – Bruno
    Nov 27 at 16:46












  • Strange, here the space was removed. Think also express 2016 or 2017
    – Mr Zach
    Nov 27 at 17:09














0












0








0






You could wrap your space within CDATA when storing the data:



<xsl:text><![CDATA[ ]]></xsl:text>


It appears that SQL server then keeps the space internally, but removes the unnecessary CDATA markup itself when getting the result back using SELECT. Fortunately, the space is kept when re-using the result of such a SELECT:



DECLARE @X XML = '<text><![CDATA[ ]]></text>'
DECLARE @Y XML

SET @Y = (SELECT @X)

SELECT @Y


The result will be:



<text> </text>





share|improve this answer












You could wrap your space within CDATA when storing the data:



<xsl:text><![CDATA[ ]]></xsl:text>


It appears that SQL server then keeps the space internally, but removes the unnecessary CDATA markup itself when getting the result back using SELECT. Fortunately, the space is kept when re-using the result of such a SELECT:



DECLARE @X XML = '<text><![CDATA[ ]]></text>'
DECLARE @Y XML

SET @Y = (SELECT @X)

SELECT @Y


The result will be:



<text> </text>






share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 27 at 16:43









Bruno

90721224




90721224












  • Also tried CDATA but it was also removed.
    – Mr Zach
    Nov 27 at 16:45










  • @MrZach CDATA itself is removed, but the space remains. (Tried on SQL Express 2016.)
    – Bruno
    Nov 27 at 16:46












  • Strange, here the space was removed. Think also express 2016 or 2017
    – Mr Zach
    Nov 27 at 17:09


















  • Also tried CDATA but it was also removed.
    – Mr Zach
    Nov 27 at 16:45










  • @MrZach CDATA itself is removed, but the space remains. (Tried on SQL Express 2016.)
    – Bruno
    Nov 27 at 16:46












  • Strange, here the space was removed. Think also express 2016 or 2017
    – Mr Zach
    Nov 27 at 17:09
















Also tried CDATA but it was also removed.
– Mr Zach
Nov 27 at 16:45




Also tried CDATA but it was also removed.
– Mr Zach
Nov 27 at 16:45












@MrZach CDATA itself is removed, but the space remains. (Tried on SQL Express 2016.)
– Bruno
Nov 27 at 16:46






@MrZach CDATA itself is removed, but the space remains. (Tried on SQL Express 2016.)
– Bruno
Nov 27 at 16:46














Strange, here the space was removed. Think also express 2016 or 2017
– Mr Zach
Nov 27 at 17:09




Strange, here the space was removed. Think also express 2016 or 2017
– Mr Zach
Nov 27 at 17:09


















draft saved

draft discarded




















































Thanks for contributing an answer to Database Administrators Stack Exchange!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.





Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


Please pay close attention to the following guidance:


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f223496%2fsql-server-changes-xml-structure-when-inserted%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Probability when a professor distributes a quiz and homework assignment to a class of n students.

Aardman Animations

Are they similar matrix