如何从填充XML在SQL列搜索得到具体结果 [英] How to get specific result from a search in a SQL column populated with XML

问题描述

我有下面的XML这是在一个SQL表中的列（ CONTENT_HTML ）（ NTEXT 类型）：

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

<?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>

我有以下SQL存储过程它通过基于DropDownList的选择返回一个搜索结果中的搜索栏（有在ASP.net页面的前端5的DropDownList）：

I have the following SQL stored procedure which searches through the column based on the dropdownlist selection to return a search result (There are 5 dropdownlist in the front end of an 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 '%[^a-z]' + @strService + '[^a-z]%' OR
     content_html LIKE @strService + '[^a-z]%' OR
     content_html LIKE '%[^a-z]' + @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')

如果我离开的每DropDownList的所有，除了这是内科医学的strService，在SQL SP返回上面的医生。这是因为，我使用 LIKE ，＆LT; residency1＆GT;内科＆LT; / residency1＆GT; 被作为一个匹配这不是这种情况。

If I leave every dropdownlist to "All" except the strService which is "Internal Medicine", the SQL SP returns the doctor above. It is because, I am using LIKE, <residency1>Internal Medicine</residency1> is taken as a match which is not the case.

请帮我修改所以，

• 服务DropDownList的：它只会搜索特种标签（1
  至6）


• 位置DropDownList的：它只会搜索办公室标签（1
  至6）


• 保险DropDownList的：它只会搜索 noins 标签（1
  通过10）


• 语言的DropDownList：它只会搜索 langF 标签（1
  经过5）

• service dropdownlist: it will only search for the specialty tags (1 through 6)
• location dropdownlist: it will only search for the office tags (1 through 6)
• insurance dropdownlist: it will only search for the noins tags (1 through 10)
• language dropdownlist: it will only search for the langF tags (1 through 5)

我发送一个％如果一切从任何DropDownList中的选择，否则我从$ C $发送DropDownList中选定项的值c-后面。

I send a % if ALL is selected from any of the dropdownlist, otherwise I send the value of the dropdownlist selected item from the code-behind.

推荐答案

您应按照您使用相同的模式＆LT;性别＆GT; ：

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

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

和

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

等。

这篇关于如何从填充XML在SQL列搜索得到具体结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！