修改现有过程——过程返回类似结果

Question

下面我包含了一些示例数据和一个用于返回搜索信息的过程。我遇到了一个问题，如果我搜索格式，它是返回信息，以及!我怎么能排除如果文本是在一个更大的字符串?

表格如下:

CREATE TABLE [dbo].[XmlTable](
        [XmlId] [int] IDENTITY(1,1) NOT NULL,
        [XmlDocument] [xml] NOT NULL,
     CONSTRAINT [PK_XmlTable] PRIMARY KEY CLUSTERED 
    (
        [XmlId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

这是xml

<dev:Doc xmlns:dev="http://www.w3.org/2001/XMLSchema" SchemaVersion="0.1" Settings="Testing" Title="Ordering">
  <dev:Base RevisionNumber="0" Baseid="34433" />
  <dev:Rev Time="2013-01-21T15:08:00">
    <dev:Person Name="Me" Systemid="54654" />
  </dev:Rev>
  <dev:Functions Id="A1">
    <dev:A1 Number="1">
      <dev:Codes>D</dev:Codes>
      <dev:Required>true</dev:Required>
      <dev:Informational>false</dev:Informational>
      <dev:Visitors>
        <dev:Visitor Name="Dev01" Location="STLRF">
          <dev:Divisions>
            <dev:Division Number="1" Name="TFR3" Usage="Monitor">
              <dev:Description>Development Fundamentals</dev:Description>
            </dev:Division>
            <dev:Division Number="2" Name="DEF32" Usage="Monitor">
              <dev:Description>Testing Fundamentals</dev:Description>
            </dev:Division>
            <dev:Division Number="3" Name="DEP13" Usage="None">
              <dev:Description>Guided Fundamentals</dev:Description>
            </dev:Division>
          </dev:Divisions>
        </dev:Visitor>
        <dev:Visitor Name="Dev02" Location="STLRF">
          <dev:Divisions>
            <dev:Division Number="1" Name="TFR3" Usage="Monitor">
              <dev:Description>Development Fundamentals</dev:Description>
            </dev:Division>
            <dev:Division Number="2" Name="DEF32" Usage="Monitor">
              <dev:Description>Testing Fundamentals</dev:Description>
            </dev:Division>
            <dev:Division Number="3" Name="DEP13" Usage="None">
              <dev:Description>Guided Fundamentals</dev:Description>
            </dev:Division>
          </dev:Divisions>
        </dev:Visitor>
        <dev:Visitor Name="Dev03" Location="FGRTY">
          <dev:Divisions>
            <dev:Division Number="1" Name="TFR3" Usage="Monitor">
              <dev:Description>Format Fundamentals</dev:Description>
            </dev:Division>
            <dev:Division Number="2" Name="DEF32" Usage="Monitor">
              <dev:Description>Testing Fundamentals</dev:Description>
            </dev:Division>
            <dev:Division Number="3" Name="DEP13" Usage="None">
              <dev:Description>Guided Fundamentals</dev:Description>
            </dev:Division>
          </dev:Divisions>
        </dev:Visitor>
      </dev:Visitors>
      <dev:Senders>
        <dev:Sender Name="FGY(14A)" />
      </dev:Senders>
    </dev:A1>
  </dev:Functions>
  <dev:Functions Id="A2">
    <dev:A2 Number="1">
      <dev:Codes>C</dev:Codes>
      <dev:Required>true</dev:Required>
      <dev:Informational>false</dev:Informational>
      <dev:Remarks>Support</dev:Remarks>
      <dev:Notes>Ready</dev:Notes>
      <dev:Visitors>
        <dev:Visitor Name="GHFF">
          <dev:Divisions>
            <dev:Division Number="0" Name="Trial" Usage="None">
              <dev:FromLocation>LOPO</dev:FromLocation>
              <dev:ToLocation>RDSS</dev:ToLocation>
              <dev:Description>Rich Information</dev:Description>
            </dev:Division>
          </dev:Divisions>
        </dev:Visitor>
      </dev:Visitors>
      <dev:Senders>
        <dev:Sender Name="W33R" />
      </dev:Senders>
      <dev:IsReady>true</dev:IsReady>
      <dev:IsCall>false</dev:IsCall>
    </dev:A2>
    <dev:A2 Number="2">
      <dev:Codes>A</dev:Codes>
      <dev:Required>true</dev:Required>
      <dev:Informational>false</dev:Informational>
      <dev:Remarks>Loader Ready</dev:Remarks>
      <dev:Notes>Ready</dev:Notes>
      <dev:Visitors>
        <dev:Visitor Name="UDT">
          <dev:Divisions>
            <dev:Division Number="0" Name="Trial" Usage="None">
              <dev:FromLocation>TYUJ</dev:FromLocation>
              <dev:ToLocation>DETF</dev:ToLocation>
              <dev:Description>Web Format</dev:Description>
            </dev:Division>
          </dev:Divisions>
        </dev:Visitor>
      </dev:Visitors>
      <dev:Senders>
        <dev:Sender Name="RJ4" />
      </dev:Senders>
      <dev:IsReady>true</dev:IsReady>
      <dev:IsCall>false</dev:IsCall>
    </dev:A2>
  </dev:Functions>
</dev:Doc>

Insert语句

INSERT INTO XmlTable(XMLDocument)
SELECT * FROM OPENROWSET(
   BULK 'C:Users123DesktopPractice.xml',
   SINGLE_BLOB) AS x;

程序

    CREATE PROCEDURE [dbo].[GetXmlSearchString]
        @findString NVARCHAR(100)
    AS
    BEGIN
        SET NOCOUNT ON
        SET @findstring = UPPER(@findstring);
    ;WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema' )
    SELECT      
     XmlId,

               CAST(x.value('.' , 'nvarchar(MAX)') As nvarchar(MAX)) AS Value,
                    a.value('(@Settings)[1]', 'NVARCHAR(200)') AS Title,
                    b.value('(@Id)[1]','nvarchar(MAX)') As [Function],
                    d.value('@Number[1]', 'INT') AS TeamNumber,
                    c.value('(@Name)[1]','nvarchar(MAX)') As  Visitor,
                    f.value('(@Name)[1]', 'NVARCHAR(MAX)') As Sender
              --,RANK() OVER 
           --        (PARTITION BY d.value('@Number[1]', 'INT') 
              --      Order By XmlId  DESC) As RANK
        FROM dbo.XmlTable x
        CROSS APPLY XmlDocument.nodes('Doc') As aa(a)
        CROSS APPLY a.nodes('Functions') As bb(b)
        CROSS APPLY b.nodes('*') dd(d) 
        CROSS APPLY d.nodes('Visitors/Visitor') As cc(c)
        CROSS APPLY d.nodes('Senders/Sender') ff(f)
        CROSS APPLY d.nodes('(.//*[contains(upper-case(text()[1]), sql:variable("@findString"))])[1]
        , (.//@*[contains(upper-case(.), sql:variable("@findString"))])[1]') a(x)
--Where a.x.value('.','nvarchar(MAX)') like '%'+@findstring+'%'
        Order By XmlId
            RETURN
        END

        GO

Answer 1

要搜索确切的单词，请添加WHERE子句:

WHERE a.x.value('.','nvarchar(MAX)') like @findstring+' %'
    or a.x.value('.','nvarchar(MAX)') like '% ' + @findstring+' %' 
    or a.x.value('.','nvarchar(MAX)') like '% ' + @findstring 
    or a.x.value('.','nvarchar(MAX)') = @findstring 

我找到了一个更优雅的方式:

Where ' ' + a.x.value('.','nvarchar(MAX)') + ' ' like '% ' + @findstring + ' %'

或者，如果你想在单词后面加上标点符号(比如"Format.")，那么使用这个:

Where ' ' + a.x.value('.','nvarchar(MAX)') + ' ' like '%[^a-z]' + @findstring + '[^a-z]%'