SQL server changes XML structure when inserted
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty{ margin-bottom:0;
}
up vote
15
down vote
favorite
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
add a comment |
up vote
15
down vote
favorite
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
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
add a comment |
up vote
15
down vote
favorite
up vote
15
down vote
favorite
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
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
sql-server xml
edited Nov 27 at 8:19
Tom V
13.7k74575
13.7k74575
asked Nov 27 at 0:53
Mr Zach
2074
2074
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
add a comment |
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
add a comment |
3 Answers
3
active
oldest
votes
up vote
21
down vote
accepted
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>
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
add a comment |
up vote
8
down vote
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 wil 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.
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
add a comment |
up vote
0
down vote
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>
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
add a comment |
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
21
down vote
accepted
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>
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
add a comment |
up vote
21
down vote
accepted
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>
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
add a comment |
up vote
21
down vote
accepted
up vote
21
down vote
accepted
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>
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>
answered Nov 27 at 6:55
Mikael Eriksson
17.5k34684
17.5k34684
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
add a comment |
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
add a comment |
up vote
8
down vote
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 wil 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.
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
add a comment |
up vote
8
down vote
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 wil 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.
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
add a comment |
up vote
8
down vote
up vote
8
down vote
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 wil 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.
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 wil 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.
answered Nov 27 at 2:44
Michael Green
13.9k82957
13.9k82957
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
add a comment |
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
add a comment |
up vote
0
down vote
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>
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
add a comment |
up vote
0
down vote
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>
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
add a comment |
up vote
0
down vote
up vote
0
down vote
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>
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>
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
add a comment |
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
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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