SQL server changes XML structur when inserted
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty{ margin-bottom:0;
}
up vote
3
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
3
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
middle line correct in both cases, so if it only 1 problem - it is not a problem
– a_vlad
6 hours ago
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
6 hours ago
hmhm, proper space it is " " but not just a space like in this comment (you can not see it)
– a_vlad
6 hours ago
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♦
5 hours ago
Yes, I did as a quick fix, thank you.
– Mr Zach
5 hours ago
add a comment |
up vote
3
down vote
favorite
up vote
3
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 4 hours ago
Michael Green
13.8k82957
13.8k82957
asked 6 hours ago
Mr Zach
1452
1452
middle line correct in both cases, so if it only 1 problem - it is not a problem
– a_vlad
6 hours ago
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
6 hours ago
hmhm, proper space it is " " but not just a space like in this comment (you can not see it)
– a_vlad
6 hours ago
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♦
5 hours ago
Yes, I did as a quick fix, thank you.
– Mr Zach
5 hours ago
add a comment |
middle line correct in both cases, so if it only 1 problem - it is not a problem
– a_vlad
6 hours ago
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
6 hours ago
hmhm, proper space it is " " but not just a space like in this comment (you can not see it)
– a_vlad
6 hours ago
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♦
5 hours ago
Yes, I did as a quick fix, thank you.
– Mr Zach
5 hours ago
middle line correct in both cases, so if it only 1 problem - it is not a problem
– a_vlad
6 hours ago
middle line correct in both cases, so if it only 1 problem - it is not a problem
– a_vlad
6 hours ago
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
6 hours ago
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
6 hours ago
hmhm, proper space it is " " but not just a space like in this comment (you can not see it)
– a_vlad
6 hours ago
hmhm, proper space it is " " but not just a space like in this comment (you can not see it)
– a_vlad
6 hours ago
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♦
5 hours ago
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♦
5 hours ago
Yes, I did as a quick fix, thank you.
– Mr Zach
5 hours ago
Yes, I did as a quick fix, thank you.
– Mr Zach
5 hours ago
add a comment |
2 Answers
2
active
oldest
votes
up vote
3
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♦
4 hours ago
add a comment |
up vote
1
down vote
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>
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
3
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♦
4 hours ago
add a comment |
up vote
3
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♦
4 hours ago
add a comment |
up vote
3
down vote
up vote
3
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 4 hours ago
Michael Green
13.8k82957
13.8k82957
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♦
4 hours ago
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♦
4 hours ago
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♦
4 hours ago
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♦
4 hours ago
add a comment |
up vote
1
down vote
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>
add a comment |
up vote
1
down vote
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>
add a comment |
up vote
1
down vote
up vote
1
down vote
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 37 mins ago
Mikael Eriksson
17.3k34683
17.3k34683
add a comment |
add a comment |
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-structur-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
middle line correct in both cases, so if it only 1 problem - it is not a problem
– a_vlad
6 hours ago
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
6 hours ago
hmhm, proper space it is " " but not just a space like in this comment (you can not see it)
– a_vlad
6 hours ago
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♦
5 hours ago
Yes, I did as a quick fix, thank you.
– Mr Zach
5 hours ago