How to get a specific result from a search in a SQL column filled with XML

Question

I have the following XML, which is a column ( content_html ) in an SQL table ( ntext ):

 <?xml version="1.0" encoding="UTF-8"?> <root> <Physicians> <name>Boston, John MD</name> <picture> <img alt="Bostom" src="Boston.jpg" /> </picture> <gender>M</gender> <langF1> <a href="default.aspx" title="English">English</a> </langF1> <langF2 /> <langF3 /> <langF4 /> <langF5 /> <insAll>true</insAll> <notIns1 /> <notIns2 /> <notIns3 /> <notIns4 /> <notIns5 /> <notIns6 /> <notIns7 /> <notIns8 /> <notIns9 /> <notIns10 /> <specialty> <a title="Gastroenterology" href="liit.aspx">Gastroenterology</a> </specialty> <specialty2 /> <specialty3 /> <specialty4 /> <specialty5 /> <specialty6 /> <additional_specialty /> <OfficeLocations> <office1> <a title="1 West Avenue" href="lit.aspx">1 West Avenue</a> </office1> <office2 /> <office3 /> <office4 /> <office5 /> <office6 /> </OfficeLocations> <phone1>[phone]</phone1> <phone2 /> <phone3 /> <fax1>[phone]</fax1> <fax2 /> <fax3 /> <Degree> <school_years1>1997 - 2001</school_years1> <school1>Temple University</school1> </Degree> <Residency> <residency_years1>2001</residency_years1> <residency1>Internal Medicine</residency1> <residency_years2 /> <residency2 /> <residency_years3 /> <residency3 /> <residency_years4 /> <residency4 /> </Residency> </Physicians> </root> 

I have the following SQL stored procedure that searches a column based on a drop-down list to return a search result (there are 5 drop-down lists on the front of the ASP.net page):

 @strService varchar(200), --service dropdownlist @strLocation varchar(200), --location dropdownlist @strGender varchar(20), --gender dropdownlist @strInsurance varchar(200), --insurance dropdownlist @strLanguage varchar(200) --language dropdownlist SELECT [content_id] AS [LinkID] , dbo.usp_ClearHTMLTags(CONVERT(nvarchar(600), CAST([content_html] AS XML).query('root/Physicians/name'))) AS [Physician Name] , [content_status] AS [Status] , CAST ([content_html] AS XML).value('(root/Physicians/picture/img/@src)[1]','varchar(255)') AS [Image] , dbo.usp_ClearHTMLTags(CONVERT(nvarchar(600), CAST([content_html] AS XML).query('root/Physicians/gender'))) AS [Gender] , CAST ([content_html] AS XML).query('/root/Physicians/OfficeLocations/office1/a') AS [Office1] , CAST ([content_html] AS XML).query('/root/Physicians/specialty/a') AS [Specialty1] , dbo.usp_ClearHTMLTags(CONVERT(nvarchar(600), CAST([content_html] AS XML).query('/root/Physicians/phone1'))) AS [PhoneNum1] FROM [DB].[dbo].[table1] WHERE [folder_id] = '188' AND (content_html LIKE @strService OR content_html LIKE '%[^az]' + @strService + '[^az]%' OR content_html LIKE @strService + '[^az]%' OR content_html LIKE '%[^az]' + @strService) AND (content_html LIKE '%' +@strLocation +'%') AND (content_html LIKE '%<gender>%'+ @strGender+'%</gender>%') AND (content_html LIKE '%' +@strInsurance +'%') AND (content_html LIKE '%' +@strLanguage +'%') AND (content_status = 'A') 

If I leave every drop-down list to Everything except strService, which is Internal Medicine, SQL SP returns the doctor above. This is because I use LIKE , <residency1>Internal Medicine</residency1> taken for a match that does not match.

Please help me change it, so,

• drop-down list of service: it will only search for specialty tags (1 through 6)
• location dropdownlist: it will only search office tags (1 through 6)
• insurance drop-down list: it will only search for noins tags (1 through 10)
• drop-down list of languages: it will only search for langF tags (1 through 5)

I send % if ALL is selected from any of the drop-down list, otherwise I will send the value of the selected list from the selected code.

Answer 1

You should follow the same pattern that you use for <gender> :

 content_html LIKE '%<speciality%' + @strService + '%</speciality%' 

and

 content_html LIKE '%<OfficeLocations>%' + @strLocation + '%</OfficeLocations>%' 

and etc.