MS SQL XQuery Issue

I was facing an issue with AJAX for last two-three days. Let me explain the issue:

I have a search screen. On this screen I have a Search “TextBox” and Search “Button”. While user is inputting any text and click on Search button, it is going to search into the database and retrieve the records.

Now I have SQL Express 2008 installed in my machine. I have to search in database table “Test” and this table contain columns named “textID” as integer & “testXml” as XML data type.

Now here I have to made search on this column “testXML” in “test” table. If during search records will find, we have to retrieve those records and display them on search screen. I have written the following query to retrieve records where keyword should match the

Name” element in “testXML” datatype.
SELECT
[TestID],
[TestXML]
FROM
Test
WHERE
[TestXML].exist(‘/Test/Name()[contains(fn:lower-case(.),fn:lower-case(“aaa”))]’) = 1
Note: In above query we have used XQuery to check the “Name” element contains “aaa” text. Format of the “TestXml” is as follows:

Suppose we have following entries in “Test” table:

testID testXML
1 <NewTest>

<Test><Name>aaa</Name></Test>

<Test><Name>bbb</Name></Test>

</NewTest>

2 <NewTest>

<Test><Name>ccc</Name></Test>

<Test><Name>ddd</Name></Test>

</NewTest>

3 <NewTest>

<Test><Name>aaa</Name></Test>

<Test><Name>eee</Name></Test>

</NewTest>

4 <NewTest>

<Test><Name>AAA</Name></Test>

<Test><Name>ggg</Name></Test>

</NewTest>

Now, when we are running following query, it is retrieving row with testID=1 and 4.

SELECT

[TestID],

[TestXML]

FROM

Test

WHERE

[TestXML].exist(‘/Test/name()[contains(fn:lower-case(.),fn:lower-case(“aaa”))]’) = 1

So when I am doing search in my search screen, it is working fine and retrieving two records with testID=1 & 4.

Now I have sent same code to client. I have sent all updated files to client. During testing we have found that it is not working on client’s server and giving following Ajax error:

Line: 4723

Error: Sys.WebForms.PageRequestManagerServerErrorException: An unknown error occurred while processing the request on the server. The status code returned from the server was: 500

 

I tried to understand the issue. However did not success as I have not access of ftp and check files on server. I have updated the code in files and sent them to client. Then I have tested the solution. I have made the same thing many time, however did not understand the issue with the above AJAX issue.

At last to understand the issue, I have commented the code in code-behind file of that search scree. Then I uncommented some code and sent back to client. He has uploaded the source and I have tested the solution. I have repeated the same process many times and at last I have found the issue which is generating the AJAX error.

So now I have understood the issue. Do you know where the issue was?

The issue was in the SQL Query, which we have created to retrieve records from “test” table.

SELECT

[TestID],

[TestXML]

FROM

Test

WHERE

[TestXML].exist(‘/Test/name()[contains(fn:lower-case(.),fn:lower-case(“aaa”))]’) = 1

 

In this query, the issue is in the where condition. Note here we have user fn:lower-case() function in XQuery. Now as I have written above I have MS SQL 2008 on my machine. So I hve tested this query in MS SQL 2008 and it is working fine.

However the same query is not working on client’s server as he has “MS SQL 2005”.

I have searched on google for the same and I have found that the MS SQL 2005 is not supporting fn:lower-case(), while MS SQL 2008 is supporting this function.

At last I have updated this function and functionality is working fine.

I have learn one more stuff of “Khoda Kua Aur Nikla Chuha”:)

Best Regards,
Vijay
http://www.ysoftsolution.com/

Advertisements

About Vijay Modi

Having 12+ years of experience in web application development. Expertise in various domains like E-Commerce, E-Learning, Insurance. I have expertise in web application development, window application development, Performance improvement, bug fixing etc. I have worked on various Microsoft technologies including ASP.Net, MVC, WebApi, WCF, Entity Framework, WPF, Window Application Development, AWS, Azure, AngularJS 2.0 / 1.5.2, .NetCore framework, jQuery. I am believing in quality work and achieving deadlines. Also like to work on new technologies and quick learner.
This entry was posted in BizTalk Server and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s