Sql-server – Search for values in xml data

sql serverxml

I have a table of names:

Table A

John
Jim
Jason

and a table of xml strings:

Table B

Example 1

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

Example 2

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

Example 3

<rule>
  <property name="CollectionVariable" DisplayName="Collection Variable" ValueType="RValueExpression">John</property>
  <property name="KeyVariable" DisplayName="Key Variable" ValueType="LValueExpression">NextLock</property>
</rule>

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.

Best Answer

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
('<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>');

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.

Related Question