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.

About these ads