Searching XML column in sql server

Question

Trying to search column Data, its an xml column

[dbo].[HistoryData].[Data].exist('//text()[CONTAINS(., 'Request viewed' )]') = 1

I get error Incorrect syntax near 'Request'. What am i missing here? kindly assist.

Column Data has this:

<MessageNotification xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<AttorneyID xsi:nil="true" />
<MatterID xsi:nil="true" />
<NotificationID xsi:nil="true" />
<MessageId>3566913</MessageId>
<ParentMessageGuid xsi:nil="true" />
<MessageGuid>fc54e518-e45d-4564-8b80-2593796d5b</MessageGuid>
<Subject>Firm not registered </Subject>
<MessageDate>2015-06-19T10:05:48.4493646+02:00</MessageDate><Body>

This is just a message

Thank you

</Body>
<ContactPerson> N/A </ContactPerson><ContactDetails xsi:nil="true" />
<PreferredContactMethod>NotApplicable</PreferredContactMethod>
</MessageNotification>

Have you tried putting your text in a column and use that instead? Also, could you post your XML content. — Abhishek
– Abhishek, Commented Apr 4, 2018 at 7:45
because Request viewed isn't encapsulated in the string, you end the string and type Request viewed then start the string again. so it is treating Request viewed as something meaningful — WhatsThePoint
– WhatsThePoint, Commented Apr 4, 2018 at 7:52
@WhatsThePoint, I set the searchString to "Request viewed" at the top but I get the error :Remote function reference 'dbo.HistoryData.Data.exist' is not allowed, and the column name 'dbo' could not be found or is ambiguous. — Anele Ngqandu
– Anele Ngqandu, Commented Apr 4, 2018 at 8:22

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

The syntax error are the single quotes obviously.

But I think there is more around... What are you trying to achieve?

The exist() is checking for the pure existance and in most cases used within a WHERE clause in order to reduce the rows before a more expensive operations.

Try this:

DECLARE @dummyTable TABLE(ID INT IDENTITY, YourXML XML);
INSERT INTO @dummyTable VALUES
 (N'<root>
    <a>test</a>
    <a>Some other</a>
   </root>')
,(N'<root>
    <a>no</a>
    <a>Some other</a>
   </root>')
,(N'<root>
    <a>no</a>
    <a>test</a>
   </root>');

SELECT * 
FROM @dummyTable
WHERE YourXML.exist(N'//*[contains(text()[1],"test")]')=1;

The result set comes without line 2

UPDATE: Use a variable

In order to get this more generic I'd suggest to use a variable:

DECLARE @SearchFor NVARCHAR(100)='other';

SELECT * 
FROM @dummyTable
WHERE YourXML.exist(N'//*[contains(text()[1],sql:variable("@SearchFor"))]')=1;

This comes without line 3

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 4, 2018 at 7:54

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

6 Comments

Anele Ngqandu Over a year ago

this what i done : [dbo].[HistoryData].[Data].exist(N'//*[contains(text()[1],sql:variable("@@SearchText_"))]')=1 then the result is "Remote function reference 'dbo.HistoryData.Data.exist' is not allowed, and the column name 'dbo' could not be found or is ambiguous. "

Anele Ngqandu Over a year ago

I set the variable @@SearchText_ at the top with value Request viewed

Gottfried Lesigang Over a year ago

Don't use the doubled @@ here. Try this with my reduced MCVE (the code above). Is this the same error you get on your query?

Gottfried Lesigang Over a year ago

Read this link. A variable with @@ is not a global variable as some people think!

Anele Ngqandu Over a year ago

the code you posted return error:Remote function reference 'dbo.HistoryData.Data.exist' is not allowed, and the column name 'dbo' could not be found or is ambiguous.

|

Collectives™ on Stack Overflow

Searching XML column in sql server

1 Answer 1

UPDATE: Use a variable

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE: Use a variable

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related