Find rows not used by xml column

Question

I have a status table, and I must find the unused statuses.
The status code can be used in activity templates, which have an actionlist xml column with custom formulas.

So far I have written this, which works, but is incredibly slow (more than a minute to get 5000 lines), and I would need to speed it up a bit.

select *
from [status] s
where not exists (
    select top 1 1
    from Wf_ActivityTemplate at
    where at.actionlist.value('.', 'nvarchar(max)') like '%@GetStatusId("' + s.code + '")%'
)

The actionlist column looks like this (irrelevant nodes removed).
As you can see, I need to search in the //ActionTriplet/Argument node, which is itself an xml node stored in text.

<ArrayOfActionTriplet xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ActionTriplet>
    <Priority>1</Priority>
    <Argument>&lt;?xml version="1.0" encoding="UTF-8" standalone="yes"?&gt;&lt;string&gt;@SetProp("STATUS",@GetStatusId("INTERNAL DESIGN REVIEW"));True&lt;/string&gt;</Argument>
    <ActionCode>APPLY_FORMULA</ActionCode>
    <TriggerTaskCode />
    <TriggerTaskIsSecondary>false</TriggerTaskIsSecondary>
    <TriggerTaskConditionFormula />
  </ActionTriplet>
  <ActionTriplet>
    <Priority>2</Priority>
    <Argument>&lt;?xml version="1.0" encoding="UTF-8" standalone="yes"?&gt;&lt;string&gt;@SetProp("STATUS",@GetStatusId("VALID"));True&lt;/string&gt;</Argument>
    <ActionCode>APPLY_FORMULA</ActionCode>
    <TriggerTaskCode />
    <TriggerTaskIsSecondary>false</TriggerTaskIsSecondary>
    <TriggerTaskConditionFormula />
  </ActionTriplet>
</ArrayOfActionTriplet>

Answer 1

Your code is taking the whole content of your XML, converts it to nvarchar and performs a %x%-like-search, which is always slow. The bigger the text, the slower...

To show another approach I declare a table variable, which mocks up your table, with two rows (see the ID). I search for the code given in @YourCode:

DECLARE @YourCode NVARCHAR(100)=N'INTERNAL DESIGN REVIEW';

DECLARE @YourTable TABLE(ID INT IDENTITY, actionList XML);
INSERT INTO @YourTable(actionList) VALUES(
'<ArrayOfActionTriplet xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ActionTriplet>
    <Priority>1</Priority>
    <Argument>&lt;?xml version="1.0" encoding="UTF-8" standalone="yes"?&gt;&lt;string&gt;@SetProp("STATUS",@GetStatusId("INTERNAL DESIGN REVIEW"));True&lt;/string&gt;</Argument>
    <ActionCode>APPLY_FORMULA</ActionCode>
    <TriggerTaskCode />
    <TriggerTaskIsSecondary>false</TriggerTaskIsSecondary>
    <TriggerTaskConditionFormula />
  </ActionTriplet>
  <ActionTriplet>
    <Priority>2</Priority>
    <Argument>&lt;?xml version="1.0" encoding="UTF-8" standalone="yes"?&gt;&lt;string&gt;@SetProp("STATUS",@GetStatusId("VALID"));True&lt;/string&gt;</Argument>
    <ActionCode>APPLY_FORMULA</ActionCode>
    <TriggerTaskCode />
    <TriggerTaskIsSecondary>false</TriggerTaskIsSecondary>
    <TriggerTaskConditionFormula />
  </ActionTriplet>
</ArrayOfActionTriplet>')
,(
'<ArrayOfActionTriplet xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ActionTriplet>
    <Priority>1</Priority>
    <Argument>&lt;?xml version="1.0" encoding="UTF-8" standalone="yes"?&gt;&lt;string&gt;@SetProp("STATUS",@GetStatusId("INTERNAL DESIGN REVIEW"));True&lt;/string&gt;</Argument>
    <ActionCode>APPLY_FORMULA</ActionCode>
    <TriggerTaskCode />
    <TriggerTaskIsSecondary>false</TriggerTaskIsSecondary>
    <TriggerTaskConditionFormula />
  </ActionTriplet>
  <ActionTriplet>
    <Priority>2</Priority>
    <Argument>&lt;?xml version="1.0" encoding="UTF-8" standalone="yes"?&gt;&lt;string&gt;@SetProp("STATUS",@GetStatusId("VALID"));True&lt;/string&gt;</Argument>
    <ActionCode>APPLY_FORMULA</ActionCode>
    <TriggerTaskCode />
    <TriggerTaskIsSecondary>false</TriggerTaskIsSecondary>
    <TriggerTaskConditionFormula />
  </ActionTriplet>
</ArrayOfActionTriplet>');

--This is the query: You'll get back all Argument-elements, where the text within this element contains GetStatusId("TheCode").

SELECT s.ID
      ,arg.query('.')
FROM @YourTable AS s
CROSS APPLY actionList.nodes('/*:ArrayOfActionTriplet/*:ActionTriplet/*:Argument[fn:contains(.,fn:concat("GetStatusId("",sql:variable("@YourCode"),"")"))]') AS A(arg)

UPDATE

With this query you can cast the inner XML from the encoded form to real XML and get the full string readable:

SELECT s.ID
      ,arg.query('.')
      ,InnerXml.value('string[1]','nvarchar(max)')
FROM @YourTable AS s
CROSS APPLY actionList.nodes('/*:ArrayOfActionTriplet/*:ActionTriplet/*:Argument[fn:contains(.,fn:concat("GetStatusId("",sql:variable("@YourCode"),"")"))]') AS A(arg)
CROSS APPLY (SELECT CAST(arg.value('.','varchar(max)') AS XML)) AS Casted(InnerXml)

UPDATE 2: Faster, checking the existance only

If you want nothing more, than to check, wether there is an <Argument> containing your code or not, you might do this:

SELECT s.ID
FROM @YourTable AS s
WHERE actionList.exist('/*:ArrayOfActionTriplet/*:ActionTriplet/*:Argument[fn:contains(.,fn:concat("GetStatusId("",sql:variable("@YourCode"),"")"))]') =1

The final =1 means: The string is inlcuded. With =0 you'd get all rows, where this string is not included