How do you query text in a SQL Server XML column

Question

I have a XML column in a SQL Server database table. It stores XML responses from a web service.

If that web service times out, it stores the actual text

Error: Timed out waiting for a reply

It's actually storing that text in the XML column - and I can't seem to query it because I cannot compare a string to an XML value. Any ideas?

Just wrap it into a <ErrorMsg> ..... </ErrorMsg> XML wrapper? — marc_s
– marc_s, Commented May 29, 2020 at 19:54
Woah, scary... TIL that SQL Server doesn't actually confirm that data inserted into XML columns really is well-formed XML: create table dbo.Test ( Foo xml ); insert dbo.Test (Foo) values (N'Error: Timed out waiting for a reply'); select * from dbo.Test; — AlwaysLearning
– AlwaysLearning, Commented May 29, 2020 at 23:45
@AlwaysLearning - the XML datatype supports XML fragments. You don't have to give it a complete well formed document. In the case of a string like 'Error: Timed out waiting for a reply' - this is just treated as a single text node. This is why the XML PATH string concatenation method works and doesn't complain that the output isn't valid XML — Martin Smith
– Martin Smith, Commented May 30, 2020 at 9:58

Martin Smith · Accepted Answer · 2020-05-30 11:02:20Z

2

A non empty string without any tags will be treated as a standalone text node in SQL Server.

So you can use 'text()[1]' to get the first top level text node in the XML content and inspect that.

you can use exist if you want to exclude from consideration any content that is a mix of top level text nodes and elements.

SELECT *,
        your_col.value('text()[1]', 'varchar(8000)')
FROM t
WHERE your_col.exist('*') = 0 AND your_col.exist('text()') = 1

answered May 30, 2020 at 10:24

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1