This article has the examples on how to search for a string in XML column of a table .
Consider below sample data :
DECLARE @StudentInfo TABLE (Id INT, Name VARCHAR(20),Subjects XML)
INSERT @StudentInfo SELECT 1,'Sathya',
'<Subjects>
<Subject>
<Name>English</Name>
<Marks>70</Marks>
</Subject>
<Subject>
<Name>Maths</Name>
<Marks>60</Marks>
</Subject>
</Subjects>'
INSERT @StudentInfo SELECT 2,'Deepak',
'<Subjects>
<Subject>
<Name>English</Name>
<Marks>70</Marks>
</Subject>
<Subject>
<Name>Science</Name>
<Marks>60</Marks>
</Subject>
</Subjects>'
SELECT * FROM @StudentInfo

--To search for subject named science in the XML column
SELECT *
FROM @StudentInfo
WHERE Subjects.exist('Subjects/Subject[Name="Science"]') = 1
--To search for subject named science and marks >= 50 in the XML column
SELECT *
FROM @StudentInfo
WHERE Subjects.exist('Subjects/Subject[Name="Science"][Marks >= "50"]') = 1
--Using Contains function to search for subject named science
SELECT *
FROM @StudentInfo
WHERE Subjects.exist('(Subjects/Subject/Name[contains(.,"Science")])')=1
Related reference links :
http://technet.microsoft.com/en-us/library/ms189869.aspx
http://msdn.microsoft.com/en-us/library/ms178026.aspx
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/601f6859-3567-435b-b764-859fb08a1044/search-for-xml-string
Consider below sample data :
DECLARE @StudentInfo TABLE (Id INT, Name VARCHAR(20),Subjects XML)
INSERT @StudentInfo SELECT 1,'Sathya',
'<Subjects>
<Subject>
<Name>English</Name>
<Marks>70</Marks>
</Subject>
<Subject>
<Name>Maths</Name>
<Marks>60</Marks>
</Subject>
</Subjects>'
INSERT @StudentInfo SELECT 2,'Deepak',
'<Subjects>
<Subject>
<Name>English</Name>
<Marks>70</Marks>
</Subject>
<Subject>
<Name>Science</Name>
<Marks>60</Marks>
</Subject>
</Subjects>'
SELECT * FROM @StudentInfo
--To search for subject named science in the XML column
SELECT *
FROM @StudentInfo
WHERE Subjects.exist('Subjects/Subject[Name="Science"]') = 1
--To search for subject named science and marks >= 50 in the XML column
SELECT *
FROM @StudentInfo
WHERE Subjects.exist('Subjects/Subject[Name="Science"][Marks >= "50"]') = 1
--Using Contains function to search for subject named science
SELECT *
FROM @StudentInfo
WHERE Subjects.exist('(Subjects/Subject/Name[contains(.,"Science")])')=1
Related reference links :
http://technet.microsoft.com/en-us/library/ms189869.aspx
http://msdn.microsoft.com/en-us/library/ms178026.aspx
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/601f6859-3567-435b-b764-859fb08a1044/search-for-xml-string
No comments:
Post a Comment