MS SQL – XQuery : Retrieve element value from a Column with XML Datatype using XQuery?

Suppose we have a “test” table with the following fields:

testID int
testXML XML

Now we have the following records in this “test” table:

1 1Test1

address1

2 2Test2
address2

3 3Test3
address3

4 4Test4
address4

Now, suppose develope has requirements to retrieve Address information for TestID=1.

You can retrieve values of elements from “testXMl” column using the following SQL Query:

SELECT
testID,
[testXML].query(‘/testXML/test/testName/text()’) as Name,
[testXML].query(‘/testXML/test/Address/text()’) as Address
FROM test

Execute this query and you will get the result as below:

testID Name Address
—————————
1 Test1 address1
2 Test2 address2
3 Test3 address3
4 Test4 address4

(4 row(s) affected)

Now, suppose developer has requirements where he has to search only those records which has the testName as “Test2” or “Test3”.
You can use the following query to achieve the solution:

SELECT
testID,
[testXML].query(‘/testXML/test/testName/text()’) as Name,
[testXML].query(‘/testXML/test/Address/text()’) as Address
FROM test
Where
[testXML].exist(‘/testXML/test/testName/text()[contains(.,”Test1″)]’) = 1

This will retrieve the following result:

testID Name Address
———– ——————
1 Test1 address1

(1 row(s) affected)

Using XQuery, user can retrieve records fast and easily.

Posted in BizTalk Server | 1 Comment

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:

Continue reading

| Tagged | Leave a comment

IDEA: Add Some One as BCC in Chat

Friends,

You all aware of sending email feature of Yahoo, Gmail, Rediff, etc.. Free Email Service providers. We can send email by insertng Email Address in To, Cc & Bcc address. Here user can see the Email addresses entered in entered in To & Cc. However we cannot see the email address which is added in Bcc.

Let us create a Chat application which can provide such a feature. Whats IDEA Sirji?

Regards,
Vijay Modi

| Tagged , , , | Leave a comment

ASP.NET 4 and Visual Studio 2010 Released

Today, I just visited http://ASP.Net and found that Microsoft has released ASP.Net 4 Beta 2 and Visual Studio 2010 Beta 2. You can find some of the exciting changes on the following URL:

http://www.asp.net/learn/whitepapers/aspnet4/#_TOC1_1

And you can find Breaking changes on the following URL:

http://www.asp.net/learn/whitepapers/aspnet4/breaking-changes/

Some new features are really amazing. Lets start with this new one instead of using old.

Cheers with ASP.Net 4 Beta 2 🙂
Vijay Modi

| Tagged , , | 1 Comment

Find table from Column Name in an MSSQL Database

From the following query you can find all the tables contains the ‘FirstName’ column.

SELECT
table_name=sysobjects.name,
column_name=syscolumns.name
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
WHERE sysobjects.xtype=’U’ AND syscolumns.name like ‘FirstName’
ORDER BY sysobjects.name,syscolumns.colid

Regards,
Vijay Modi

Posted in BizTalk Server | Tagged , | Leave a comment

Let me know your Suggessions…

Hi Friends,

Its Diwali celebrations here in India.

May the festival of lights be the harbinger of joy and prosperity. As the holy occasion of Diwali is here and the atmosphere is filled with the spirit of mirth and love, here’s hoping this festival of beauty brings your way, bright sparkles of contentment, that stay with you through the days ahead.
Best wishes on Diwali and New year.

For this Diwali and coming New Year, I want to know your suggessions for this blog. I want to improve my blog and for this I need your help. Please add your suggessions, so that I can improve this blog and write more interesting articles.

Happy Diwali and Happy New Year 🙂

Regards,

Vijay Modi

Posted in BizTalk Server | Tagged , | 2 Comments

Validation on User Registration

Mostly we are giving functionality for registration on the website and use the website. And using our registration page, user can register him/her and set the UserName and password whatever he/she want. Here we are inserting some validations on client side and server side for checking the data entered by user is valid or not. However one more validation we require on Server side. This is regarding the UserName. We are providing functionality like user can create any UserName. We are checking on server side that the UserName enetered by User is exist or not. And if userName not exist we are creating that User with the UserName that he/she set on registration page.

However we should set one more validation on this registration page for UserName that the user cannot create UserName with the website domain name. Like if site domain name is “www.testing.com”, then user cannot create UserName, which contains the Domain Name in his UserName. Means user cannot register with UserNmae like ‘testing’, ‘testing123′, ‘123testing123′, ‘123testing’, ect…
This is very important as once user register with these name, he can sent mail to other person using that same ID from the website. So we should add validation on server side that the User cannot register with the UserName which contains domain name.

We should also add validation for some common names too. Like we should add validation on server side that the user cannot register with some common names like “hr”, “info”, “information”, “contacts”. So we should also add these list in our Server Side validation. So that user cannot register with these types of UserName.

Let me know your input for the same.

Thanks & Regards,
Vijay Modi

| Tagged , , | 1 Comment