T-SQL LIKE Predicate failed to match with whitespace in XML converted varcharCan XML columns in Oracle/MS SQL Server replace document oriented databases to some extent?Help with SQL Query to XML outputLike predicate to match a whole word onlyHow to cast a list of values within xquery in SQL Server?SQL to XML with multiple valuesSQL server Like pattern match with CONCAT() always failsWhen converting dynamic SQL (pivot query) to xml output, why is the first digit of the date converted to unicode?How To Read HTML code as XML and get the output like the sample in sql?Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL queryXML/SQL Server 2008 Error: XQuery…Cannot implicitly atomize or apply 'fn:data()' to complex content elements
Probabilities in non-stationary states
Why is "la Gestapo" feminine?
Extraneous elements in "Europe countries" list
Difficulty understanding group delay concept
Have any astronauts/cosmonauts died in space?
What are rules for concealing thieves tools (or items in general)?
How to read string as hex number in bash?
Why doesn't the fusion process of the sun speed up?
Align centered, ragged right and ragged left in align environment
label a part of commutative diagram
Should I be concerned about student access to a test bank?
How to balance a monster modification (zombie)?
10 year ban after applying for a UK student visa
Do native speakers use "ultima" and "proxima" frequently in spoken English?
TDE Master Key Rotation
Does fire aspect on a sword, destroy mob drops?
How much propellant is used up until liftoff?
What (if any) is the reason to buy in small local stores?
Knife as defense against stray dogs
Why didn't Héctor fade away after this character died in the movie Coco?
What kind of footwear is suitable for walking in micro gravity environment?
Why is participating in the European Parliamentary elections used as a threat?
Why do I have a large white artefact on the rendered image?
How to find the largest number(s) in a list of elements?
T-SQL LIKE Predicate failed to match with whitespace in XML converted varchar
Can XML columns in Oracle/MS SQL Server replace document oriented databases to some extent?Help with SQL Query to XML outputLike predicate to match a whole word onlyHow to cast a list of values within xquery in SQL Server?SQL to XML with multiple valuesSQL server Like pattern match with CONCAT() always failsWhen converting dynamic SQL (pivot query) to xml output, why is the first digit of the date converted to unicode?How To Read HTML code as XML and get the output like the sample in sql?Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL queryXML/SQL Server 2008 Error: XQuery…Cannot implicitly atomize or apply 'fn:data()' to complex content elements
Recently I attempt to search for a particular pattern by converting XML
data into varchar(max)
although I'm aware it's not the best practice and found out it's not working as expected:-
Setup
declare @container table(
[Response] xml not null
);
declare @xml xml =
'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://abc.com/xsd" xmlns:ns="http://abc.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soapenv:Header>
<ns:MessageHeader>
<xsd:ID>ABC</xsd:ID>
<xsd:Date>2018-12-31T23:59:59</xsd:Date>
</ns:MessageHeader>
</soapenv:Header>
<soapenv:Body>
<ns:MessageResponse>
<ns:return>
<xsd:ResponseList xsi:nil="true" />
</ns:return>
</ns:MessageResponse>
</soapenv:Body>
</soapenv:Envelope>';
insert into @container values (@xml);
This query works
select *
from @container
where cast(Response as varchar(max))
like '%<xsd:ResponseList xsi:nil="true"%';
notice the wildcard character ends 3 characters (i.e.' />'
) before the XML node
but this is not
select *
from @container
where cast(Response as varchar(max))
like '%<xsd:ResponseList xsi:nil="true" %' -- with space
or cast(Response as varchar(max))
like '%<xsd:ResponseList xsi:nil="true" />%' -- whole XML node;
I suspect this is probably due to escape characters and tried a few other alternatives but to no avail, appreciate if someone can shed some light on this.
EDIT (ANSWERED)
Following query would work based on Mr. Browstone's insight:-
select *
from @container
where cast(Response as varchar(max))
like '%<xsd:ResponseList xsi:nil="true"/>%';
Here's my follow question @ CodeReview with XQuery expression:-
T-SQL Verify whether XML node from SOAP request contains any child nodes
sql-server t-sql xml like string-searching
add a comment |
Recently I attempt to search for a particular pattern by converting XML
data into varchar(max)
although I'm aware it's not the best practice and found out it's not working as expected:-
Setup
declare @container table(
[Response] xml not null
);
declare @xml xml =
'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://abc.com/xsd" xmlns:ns="http://abc.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soapenv:Header>
<ns:MessageHeader>
<xsd:ID>ABC</xsd:ID>
<xsd:Date>2018-12-31T23:59:59</xsd:Date>
</ns:MessageHeader>
</soapenv:Header>
<soapenv:Body>
<ns:MessageResponse>
<ns:return>
<xsd:ResponseList xsi:nil="true" />
</ns:return>
</ns:MessageResponse>
</soapenv:Body>
</soapenv:Envelope>';
insert into @container values (@xml);
This query works
select *
from @container
where cast(Response as varchar(max))
like '%<xsd:ResponseList xsi:nil="true"%';
notice the wildcard character ends 3 characters (i.e.' />'
) before the XML node
but this is not
select *
from @container
where cast(Response as varchar(max))
like '%<xsd:ResponseList xsi:nil="true" %' -- with space
or cast(Response as varchar(max))
like '%<xsd:ResponseList xsi:nil="true" />%' -- whole XML node;
I suspect this is probably due to escape characters and tried a few other alternatives but to no avail, appreciate if someone can shed some light on this.
EDIT (ANSWERED)
Following query would work based on Mr. Browstone's insight:-
select *
from @container
where cast(Response as varchar(max))
like '%<xsd:ResponseList xsi:nil="true"/>%';
Here's my follow question @ CodeReview with XQuery expression:-
T-SQL Verify whether XML node from SOAP request contains any child nodes
sql-server t-sql xml like string-searching
add a comment |
Recently I attempt to search for a particular pattern by converting XML
data into varchar(max)
although I'm aware it's not the best practice and found out it's not working as expected:-
Setup
declare @container table(
[Response] xml not null
);
declare @xml xml =
'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://abc.com/xsd" xmlns:ns="http://abc.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soapenv:Header>
<ns:MessageHeader>
<xsd:ID>ABC</xsd:ID>
<xsd:Date>2018-12-31T23:59:59</xsd:Date>
</ns:MessageHeader>
</soapenv:Header>
<soapenv:Body>
<ns:MessageResponse>
<ns:return>
<xsd:ResponseList xsi:nil="true" />
</ns:return>
</ns:MessageResponse>
</soapenv:Body>
</soapenv:Envelope>';
insert into @container values (@xml);
This query works
select *
from @container
where cast(Response as varchar(max))
like '%<xsd:ResponseList xsi:nil="true"%';
notice the wildcard character ends 3 characters (i.e.' />'
) before the XML node
but this is not
select *
from @container
where cast(Response as varchar(max))
like '%<xsd:ResponseList xsi:nil="true" %' -- with space
or cast(Response as varchar(max))
like '%<xsd:ResponseList xsi:nil="true" />%' -- whole XML node;
I suspect this is probably due to escape characters and tried a few other alternatives but to no avail, appreciate if someone can shed some light on this.
EDIT (ANSWERED)
Following query would work based on Mr. Browstone's insight:-
select *
from @container
where cast(Response as varchar(max))
like '%<xsd:ResponseList xsi:nil="true"/>%';
Here's my follow question @ CodeReview with XQuery expression:-
T-SQL Verify whether XML node from SOAP request contains any child nodes
sql-server t-sql xml like string-searching
Recently I attempt to search for a particular pattern by converting XML
data into varchar(max)
although I'm aware it's not the best practice and found out it's not working as expected:-
Setup
declare @container table(
[Response] xml not null
);
declare @xml xml =
'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://abc.com/xsd" xmlns:ns="http://abc.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soapenv:Header>
<ns:MessageHeader>
<xsd:ID>ABC</xsd:ID>
<xsd:Date>2018-12-31T23:59:59</xsd:Date>
</ns:MessageHeader>
</soapenv:Header>
<soapenv:Body>
<ns:MessageResponse>
<ns:return>
<xsd:ResponseList xsi:nil="true" />
</ns:return>
</ns:MessageResponse>
</soapenv:Body>
</soapenv:Envelope>';
insert into @container values (@xml);
This query works
select *
from @container
where cast(Response as varchar(max))
like '%<xsd:ResponseList xsi:nil="true"%';
notice the wildcard character ends 3 characters (i.e.' />'
) before the XML node
but this is not
select *
from @container
where cast(Response as varchar(max))
like '%<xsd:ResponseList xsi:nil="true" %' -- with space
or cast(Response as varchar(max))
like '%<xsd:ResponseList xsi:nil="true" />%' -- whole XML node;
I suspect this is probably due to escape characters and tried a few other alternatives but to no avail, appreciate if someone can shed some light on this.
EDIT (ANSWERED)
Following query would work based on Mr. Browstone's insight:-
select *
from @container
where cast(Response as varchar(max))
like '%<xsd:ResponseList xsi:nil="true"/>%';
Here's my follow question @ CodeReview with XQuery expression:-
T-SQL Verify whether XML node from SOAP request contains any child nodes
sql-server t-sql xml like string-searching
sql-server t-sql xml like string-searching
edited yesterday
Zephyr
asked yesterday
ZephyrZephyr
5815
5815
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
This is by design.
When you store a document using the XML data type it is compressed and organised into a structure that Sql Server can perform operations on efficiently. One of the steps that it goes through to do this is to generate the InfoSet. When it does this, it removes anything that it determines to not be necessary, in your example, whitespace:
The InfoSet content 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.
When you select the entire contents of the field (such as when you are converting it to NVARCHAR(MAX)
it rebuilds the XML document before returning it. This document may not be an identical copy of the document that you inserted. For example, if you have used self-closing elements, Sql Server may return opening and closing elements instead.
The documentation also continues on to say:
Example: Retaining Exact Copies of XML Data
For illustration, assume that government regulations require you to retain exact textual copies of your XML documents. For example, these could include signed documents, legal documents, or stock transaction orders. You may want to store your documents in a [n]varchar(max) column.
So, if you want to store the exact copy of your document, then NVARCHAR(MAX)
or VARCHAR(MAX)
is the best option. You can then convert it to XML to query it later on (though this can be costly).
For more information, see the documentation on XML Data Type and Columns (SQL Server) and also Define the Serialization of XML Data which outlines the rules that Sql Server applies when converting XML to a string type.
2
Ah good to know that, I've tried to remove space-in-between the self-closing tag and everything works fine! Many thanks Mr. Brownstone!
– Zephyr
yesterday
add a comment |
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
);
);
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%2f232358%2ft-sql-like-predicate-failed-to-match-with-whitespace-in-xml-converted-varchar%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
This is by design.
When you store a document using the XML data type it is compressed and organised into a structure that Sql Server can perform operations on efficiently. One of the steps that it goes through to do this is to generate the InfoSet. When it does this, it removes anything that it determines to not be necessary, in your example, whitespace:
The InfoSet content 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.
When you select the entire contents of the field (such as when you are converting it to NVARCHAR(MAX)
it rebuilds the XML document before returning it. This document may not be an identical copy of the document that you inserted. For example, if you have used self-closing elements, Sql Server may return opening and closing elements instead.
The documentation also continues on to say:
Example: Retaining Exact Copies of XML Data
For illustration, assume that government regulations require you to retain exact textual copies of your XML documents. For example, these could include signed documents, legal documents, or stock transaction orders. You may want to store your documents in a [n]varchar(max) column.
So, if you want to store the exact copy of your document, then NVARCHAR(MAX)
or VARCHAR(MAX)
is the best option. You can then convert it to XML to query it later on (though this can be costly).
For more information, see the documentation on XML Data Type and Columns (SQL Server) and also Define the Serialization of XML Data which outlines the rules that Sql Server applies when converting XML to a string type.
2
Ah good to know that, I've tried to remove space-in-between the self-closing tag and everything works fine! Many thanks Mr. Brownstone!
– Zephyr
yesterday
add a comment |
This is by design.
When you store a document using the XML data type it is compressed and organised into a structure that Sql Server can perform operations on efficiently. One of the steps that it goes through to do this is to generate the InfoSet. When it does this, it removes anything that it determines to not be necessary, in your example, whitespace:
The InfoSet content 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.
When you select the entire contents of the field (such as when you are converting it to NVARCHAR(MAX)
it rebuilds the XML document before returning it. This document may not be an identical copy of the document that you inserted. For example, if you have used self-closing elements, Sql Server may return opening and closing elements instead.
The documentation also continues on to say:
Example: Retaining Exact Copies of XML Data
For illustration, assume that government regulations require you to retain exact textual copies of your XML documents. For example, these could include signed documents, legal documents, or stock transaction orders. You may want to store your documents in a [n]varchar(max) column.
So, if you want to store the exact copy of your document, then NVARCHAR(MAX)
or VARCHAR(MAX)
is the best option. You can then convert it to XML to query it later on (though this can be costly).
For more information, see the documentation on XML Data Type and Columns (SQL Server) and also Define the Serialization of XML Data which outlines the rules that Sql Server applies when converting XML to a string type.
2
Ah good to know that, I've tried to remove space-in-between the self-closing tag and everything works fine! Many thanks Mr. Brownstone!
– Zephyr
yesterday
add a comment |
This is by design.
When you store a document using the XML data type it is compressed and organised into a structure that Sql Server can perform operations on efficiently. One of the steps that it goes through to do this is to generate the InfoSet. When it does this, it removes anything that it determines to not be necessary, in your example, whitespace:
The InfoSet content 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.
When you select the entire contents of the field (such as when you are converting it to NVARCHAR(MAX)
it rebuilds the XML document before returning it. This document may not be an identical copy of the document that you inserted. For example, if you have used self-closing elements, Sql Server may return opening and closing elements instead.
The documentation also continues on to say:
Example: Retaining Exact Copies of XML Data
For illustration, assume that government regulations require you to retain exact textual copies of your XML documents. For example, these could include signed documents, legal documents, or stock transaction orders. You may want to store your documents in a [n]varchar(max) column.
So, if you want to store the exact copy of your document, then NVARCHAR(MAX)
or VARCHAR(MAX)
is the best option. You can then convert it to XML to query it later on (though this can be costly).
For more information, see the documentation on XML Data Type and Columns (SQL Server) and also Define the Serialization of XML Data which outlines the rules that Sql Server applies when converting XML to a string type.
This is by design.
When you store a document using the XML data type it is compressed and organised into a structure that Sql Server can perform operations on efficiently. One of the steps that it goes through to do this is to generate the InfoSet. When it does this, it removes anything that it determines to not be necessary, in your example, whitespace:
The InfoSet content 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.
When you select the entire contents of the field (such as when you are converting it to NVARCHAR(MAX)
it rebuilds the XML document before returning it. This document may not be an identical copy of the document that you inserted. For example, if you have used self-closing elements, Sql Server may return opening and closing elements instead.
The documentation also continues on to say:
Example: Retaining Exact Copies of XML Data
For illustration, assume that government regulations require you to retain exact textual copies of your XML documents. For example, these could include signed documents, legal documents, or stock transaction orders. You may want to store your documents in a [n]varchar(max) column.
So, if you want to store the exact copy of your document, then NVARCHAR(MAX)
or VARCHAR(MAX)
is the best option. You can then convert it to XML to query it later on (though this can be costly).
For more information, see the documentation on XML Data Type and Columns (SQL Server) and also Define the Serialization of XML Data which outlines the rules that Sql Server applies when converting XML to a string type.
edited yesterday
answered yesterday
Mr.BrownstoneMr.Brownstone
9,64232342
9,64232342
2
Ah good to know that, I've tried to remove space-in-between the self-closing tag and everything works fine! Many thanks Mr. Brownstone!
– Zephyr
yesterday
add a comment |
2
Ah good to know that, I've tried to remove space-in-between the self-closing tag and everything works fine! Many thanks Mr. Brownstone!
– Zephyr
yesterday
2
2
Ah good to know that, I've tried to remove space-in-between the self-closing tag and everything works fine! Many thanks Mr. Brownstone!
– Zephyr
yesterday
Ah good to know that, I've tried to remove space-in-between the self-closing tag and everything works fine! Many thanks Mr. Brownstone!
– Zephyr
yesterday
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.
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%2f232358%2ft-sql-like-predicate-failed-to-match-with-whitespace-in-xml-converted-varchar%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