Search for values in xml data I have a table of names:

Table A

John
Jim
Jason


and a table of xml strings:

Table B

Example 1

    &lt;Show Title="" ShowTitle="=False" ShowLine="=False" ShowDescription="=False" ShowExpandCollapse="=False" IsVisible="=True" Description="" PageBreakAfter="=False" PageCaption="" AppLink="" InfoLink="" ImageLink="" Pause="=False" PauseMessage="" PauseMessageStyle="" PauseTitle="" ScreenStyle=""&gt;
  &lt;ShowOption Sequence="1" Name="Jim" Caption="Test" SelectOptionsImageLinkFieldExpression="ImageLink" /&gt;
&lt;/Show&gt;


Example 2

&lt;vars&gt;
  &lt;var name="MatrixName"&gt;
    &lt;value&gt;="LockExitPairCompatability"&lt;/value&gt;
  &lt;/var&gt;
  &lt;var name="Jason"&gt;
    &lt;value&gt;=If(Exists(Minute.Value.Min), ToNumber(Minute.Value.Min), 0)&lt;/value&gt;
  &lt;/var&gt;
  &lt;var name="MinNum"&gt;
    &lt;value&gt;=If(Exists(Agency) AND Agency = "Cert", 0, MinNum)&lt;/value&gt;
  &lt;/var&gt;
  &lt;var name="Where"&gt;
    &lt;value&gt;="Site='" + Root.Site + "' AND HWType IN " + ToSQLArray(Root.Components[ActiveDoor].HardwareType)&lt;/value&gt;
  &lt;/var&gt;
  &lt;var name="Where2"&gt;
    &lt;value&gt;=" AND HWSubType IN " + ToSQLArray(Root.Components[ActiveDoor].LockSubType)&lt;/value&gt;
  &lt;/var&gt;
  &lt;var name="CompatibleList"&gt;
    &lt;value&gt;=usr.FetchMatrix&lt;/value&gt;
  &lt;/var&gt;
&lt;/vars&gt;


Example 3

&lt;rule&gt;
  &lt;property name="CollectionVariable" DisplayName="Collection Variable" ValueType="RValueExpression"&gt;John&lt;/property&gt;
  &lt;property name="KeyVariable" DisplayName="Key Variable" ValueType="LValueExpression"&gt;NextLock&lt;/property&gt;
&lt;/rule&gt;


How can I search to find which names in Table A aren't in any of the xml strings? 
The data shown are just examples. The xml in the database is of all different formats.

In the xml data it might be "Root.Name". I would still want it to find it even with the "Root." attached to it.
I assume you want to look in all attributes and all elements.

Use the function exist against the XML column in table B with a predicate to check for the existence of values from Table A. The function sql:column is used to get values from A into the XQuery expression.

declare @A table(Name varchar(50));
insert into @A(Name) values('John'),('Jim'),('Jason'),('Mike');

declare @B table(XMLCol xml);

insert into @B(XMLCol) values
('&lt;Show Title="" ShowTitle="=False" ShowLine="=False" ShowDescription="=False" ShowExpandCollapse="=False" IsVisible="=True" Description="" PageBreakAfter="=False" PageCaption="" AppLink="" InfoLink="" ImageLink="" Pause="=False" PauseMessage="" PauseMessageStyle="" PauseTitle="" ScreenStyle=""&gt;
  &lt;ShowOption Sequence="1" Name="Jim" Caption="Test" SelectOptionsImageLinkFieldExpression="ImageLink" /&gt;
&lt;/Show&gt;'),
('&lt;vars&gt;
  &lt;var name="MatrixName"&gt;&lt;value&gt;="LockExitPairCompatability"&lt;/value&gt;&lt;/var&gt;
  &lt;var name="Jason"&gt;&lt;value&gt;=If(Exists(Minute.Value.Min), ToNumber(Minute.Value.Min), 0)&lt;/value&gt;&lt;/var&gt;
  &lt;var name="MinNum"&gt;&lt;value&gt;=If(Exists(Agency) AND Agency = "Cert", 0, MinNum)&lt;/value&gt;&lt;/var&gt;
  &lt;var name="Where"&gt;&lt;value&gt;="Site=''" + Root.Site + "'' AND HWType IN " + ToSQLArray(Root.Components[ActiveDoor].HardwareType)&lt;/value&gt;&lt;/var&gt;
  &lt;var name="Where2"&gt;&lt;value&gt;=" AND HWSubType IN " + ToSQLArray(Root.Components[ActiveDoor].LockSubType)&lt;/value&gt;&lt;/var&gt;
  &lt;var name="CompatibleList"&gt;&lt;value&gt;=usr.FetchMatrix&lt;/value&gt;&lt;/var&gt;
&lt;/vars&gt;'),
('&lt;rule&gt;
  &lt;property name="CollectionVariable" DisplayName="Collection Variable" ValueType="RValueExpression"&gt;John&lt;/property&gt;
  &lt;property name="KeyVariable" DisplayName="Key Variable" ValueType="LValueExpression"&gt;NextLock&lt;/property&gt;
&lt;/rule&gt;');

select A.Name
from @A as A
where not exists (
                 select *
                 from @B as B
                 where B.XMLCol.exist('(//@*, //text())[. = sql:column("A.Name")]') = 1
                 );


// searches all descendants.

@ specifies that you are looking for attributes.

* is a wild card for the attribute name.

//text() gives you all descendant text values (not attributes).

(//@*, //text()) combines the attributes with the text values

[. = sql:column("A.Name")] a predicate that checks the current value against A.Name.



If you want to check if a value contains A.Name you should use the contains function.

select A.Name
from @A as A
where not exists (
                 select *
                 from @B as B
                 where B.XMLCol.exist('(//@*, //text())[contains(., sql:column("A.Name"))]') = 1
                 );


If need a case-insensitive comparison, lower-case both parameters to contains.