I have a table tblUserData in SQL Server Database which has two columns:
tblUSerData(ID BIGINT, UserData XML)
The XML structure would be like:
<user>
<name>
Akshay
</name>
<age>
23
</age>
</user>
I want to write a stored procedure such that would read the table and return records of users who are more than 20 years old. I am confused how to parse the table for data in efficient manner. Thanks in advance.
Your query could be written using the exist() Method (xml Data Type)
select ID
from tblUserData
where UserData.exist('/user[age > 20]') = 1
From SQL Server 2012 SP1 you can use Selective XML Indexes (SXI) and the index that supports the query above would look like this.
create selective xml index sxi_UserData on tblUserData(UserData)
for (
u = '/user' as xquery 'node()' singleton,
a = '/user/age' as xquery 'xs:double' singleton
)
To use Selective XML Indexes you have to enabled it using sp_db_selective_xml_index (Transact-SQL)
For more information you can have a look at a number of blog posts by Bob Beauchemin.
Assuming that each row in tblUserData will only contain one user node, and one age node in that user you can use:
WHERE UserData.value('user[1]/age[1]', 'int') > 20
So I have created the table tblUserData and then I have written the query that will extract the Users whose age is more than 20
I have used the provided xml structure
<user>
<name>
Akshay
</name>
<age>
23
</age>
</user>
create table tblUserData ( ID BIGINT,
UserData xml )
insert into tblUserData values (1, '<user> <name> Akshay </name> <age> 23 </age> </user>')
insert into tblUserData values (2, '<user> <name> Tom </name> <age> 26 </age> </user>')
Following is the query that will extract the users having age more than 20
select node.value('name[1]', 'varchar(255)') as userName, node.value('age[1]','int') as age
from dbo.tblUserData tblu
CROSS APPLY UserData.nodes('/user') nodes(node)
where node.value('age[1]', 'int') > 20
