SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table TableB ( 
    id int identity primary key,
    x  xml
)
create table TableA (
    name varchar(10)
);
insert TableA(name)
values
('John'),
('Jim'),
('Jason'),
('Julio');
4 rows affected

insert TableB ( x )
values
('<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="">
  <ShowOption Sequence="1" Name="Jim" Caption="Test" SelectOptionsImageLinkFieldExpression="ImageLink" />
</Show>'),
('<vars>
  <var name="MatrixName">
    <value>="LockExitPairCompatability"</value>
  </var>
  <var name="Jason">
    <value>=If(Exists(Minute.Value.Min), ToNumber(Minute.Value.Min), 0)</value>
  </var>
  <var name="MinNum">
    <value>=If(Exists(Agency) AND Agency = "Cert", 0, MinNum)</value>
  </var>
  <var name="Where">
    <value>="Site=''" + Root.Site + "'' AND HWType IN " + ToSQLArray(Root.Components[ActiveDoor].HardwareType)</value>
  </var>
  <var name="Where2">
    <value>=" AND HWSubType IN " + ToSQLArray(Root.Components[ActiveDoor].LockSubType)</value>
  </var>
  <var name="CompatibleList">
    <value>=usr.FetchMatrix</value>
  </var>
</vars>
'),
('<rule>
  <property name="CollectionVariable" DisplayName="Collection Variable" ValueType="RValueExpression">John</property>
  <property name="KeyVariable" DisplayName="Key Variable" ValueType="LValueExpression">NextLock</property>
</rule>');
3 rows affected

-- credit to https://stackoverflow.com/a/10885014/4709762

CREATE OR ALTER FUNCTION dbo.fnXmlNodeValue 
(	
	@x xml 	 
)
RETURNS TABLE 
AS
RETURN 
(
	WITH cte AS 
	(  
		SELECT 
			lvl =				1,  
			Name =				x.value('local-name(.)','NVARCHAR(MAX)'),  
			ParentName =		CAST(NULL AS NVARCHAR(MAX)), 
			ParentPosition =	CAST(1 AS INT), 
			NodeType =			CAST(N'Element' AS NVARCHAR(20)),  
			FullPath =			x.value('local-name(.)','NVARCHAR(MAX)'),  
			XPath =				x.value('local-name(.)','NVARCHAR(MAX)')  
				+ N'[' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NVARCHAR) + N']',  
			Position =			ROW_NUMBER() OVER(ORDER BY (SELECT 1)), 
			Tree =				x.value('local-name(.)','NVARCHAR(MAX)'),  
			Value =				x.value('text()[1]','NVARCHAR(MAX)'), 
			this =				x.query('.'),         
			t =					x.query('*'),  
			Sort =				CAST(CAST(1 AS VARBINARY(4)) AS VARBINARY(MAX)),  
			ID =				CAST(1 AS INT)  
		FROM @x.nodes('/*') a(x)  
		UNION ALL 
		SELECT 
			lvl =				p.lvl + 1,  
			Name =				c.value('local-name(.)','NVARCHAR(MAX)'),  
			ParentName =		CAST(p.Name AS NVARCHAR(MAX)), 
			ParentPosition =	CAST(p.Position AS INT), 
			NodeType =			CAST(N'Element' AS NVARCHAR(20)),  
			FullPath =			CAST(p.FullPath + N'/' + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)),  
			XPath =				CAST(p.XPath + N'/'+ c.value('local-name(.)','NVARCHAR(MAX)') 
				+ N'['+ CAST(ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)') ORDER BY (SELECT 1)) AS NVARCHAR)+ N']' AS NVARCHAR(MAX)), 
			Position =			ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)') ORDER BY (SELECT 1)), 
			Tree =				CAST( SPACE(2 * p.lvl - 1) + N'|' + REPLICATE(N'-', 1) + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)), 
			Value =				CAST( c.value('text()[1]','NVARCHAR(MAX)') AS NVARCHAR(MAX) ), 
			this =				c.query('.'),  
			t =					c.query('*'),  
			Sort =				CAST(p.Sort + CAST( (lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS VARBINARY(4)) AS VARBINARY(MAX) ),  
			CAST((lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS INT)  
		FROM cte p  
		CROSS APPLY p.t.nodes('*') b(c)
	)
	,cte2 AS 
	(
		SELECT 
			lvl AS Depth,  
			Name AS NodeName,  
			ParentName, 
			ParentPosition, 
			NodeType,  
			FullPath,  
			XPath,  
			Position, 
			Tree AS TreeView,  
			Value,  
			this AS XMLData,  
			Sort, ID  
		FROM cte  
		UNION ALL 
		SELECT 
			p.lvl,  
			x.value('local-name(.)','NVARCHAR(MAX)'),  
			p.Name, 
			p.Position, 
			CAST(N'Attribute' AS NVARCHAR(20)),  
			p.FullPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
			p.XPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
			1, 
			SPACE(2 * p.lvl - 1) + N'|' + REPLICATE('-', 1)  
			+ N'@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
			x.value('.','NVARCHAR(MAX)'),  
			NULL,  
			p.Sort,  
			p.ID + 1  
		FROM cte p  
		CROSS APPLY this.nodes('/*/@*') a(x)  
	)  
	SELECT 
		ID = ROW_NUMBER() OVER(ORDER BY Sort, ID),  
		ParentName, 
		ParentPosition, 
		Depth, 
		NodeName, 
		Position,   
		NodeType, 
		FullPath, 
		XPath, 
		TreeView, 
		Value, 
		XMLData 
	FROM cte2
);
-- the actual question
-- How can I search to find which names in Table A aren't in any of the xml strings? 
with shredder as (
    select b.id, 
           x.NodeName, 
           x.NodeType,
           x.FullPath, 
           x.Value 
    from TableB as b 
    cross apply dbo.fnXmlNodeValue(b.x) as x
) 
select * 
from TableA as a
where not exists (
    select 1 
    from shredder as s
    where s.Value = a.name
); 
-- lonely Julio


name


Julio


-- find by literal list
-- set statistics xml on;
select b.id, 
       x.NodeName, 
       x.NodeType,
       x.FullPath, 
       x.Value 
from TableB as b 
cross apply dbo.fnXmlNodeValue(b.x) as x
where x.Value in ('John','Jim','Jason');


id
NodeName
NodeType
FullPath
Value


1
Name
Attribute
Show/ShowOption/@Name
Jim

2
name
Attribute
vars/var/@name
Jason

3
property
Element
rule/property
John


-- find from table
-- set statistics xml on;
select b.id, 
       x.NodeName, 
       x.NodeType,
       x.FullPath, 
       x.Value 
from TableB as b 
cross apply dbo.fnXmlNodeValue(b.x) as x
where exists (
    select 1
    from TableA as a
    where x.Value = a.name
);


id
NodeName
NodeType
FullPath
Value


1
Name
Attribute
Show/ShowOption/@Name
Jim

2
name
Attribute
vars/var/@name
Jason

3
property
Element
rule/property
John
id	NodeName	NodeType	FullPath	Value
1	Name	Attribute	Show/ShowOption/@Name	Jim
2	name	Attribute	vars/var/@name	Jason
3	property	Element	rule/property	John