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.

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 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. Bookmark the permalink.

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

  1. test3 says:

    Thank you for some other informative site. Where else may just I get that kind of info written in such an ideal manner? I’ve a challenge that I’m simply now operating on, and I have been at the look out for such information.

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