SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE abc([Data] xml NOT NULL, [Group] varchar(3));
INSERT INTO abc
    ([Data], [Group])
VALUES
    ('<div>
     <test name="Test1" enabled="True"/>
     <ul>
     <i><a id="t.foo"/></i>
     <i><a id="t.bar"/></i>
     </ul>
    </div>', 'ADG')
;
1 rows affected

CREATE TABLE ForgeRock
    ([productName] varchar(13), [description] varchar(57), [Group] varchar(3))

INSERT INTO ForgeRock ([productName], [description], [Group])
VALUES
    ('foo', 'Platform for building enterprise provisioning solutions', 'ADG'),
    ('bar', 'Full-featured access management', 'ADG'),
    ('taz', 'Robust LDAP server for Java', 'ADG')
;
3 rows affected

SELECT *
FROM dbo.ForgeRock FR
WHERE EXISTS (SELECT 1
              FROM dbo.abc a
                   CROSS APPLY a.[Data].nodes('/div/ul/i/a') i(a)
              WHERE STUFF(i.a.value('@id','varchar(15)'),1,2,'') = FR.productName);


productName
description
Group


foo
Platform for building enterprise provisioning solutions
ADG

bar
Full-featured access management
ADG


DROP TABLE dbo.ForgeRock;
DROP TABLE dbo.abc;

productName	description	Group
foo	Platform for building enterprise provisioning solutions	ADG
bar	Full-featured access management	ADG