How to join ids from an xml stored in a column with ids from a column in another table

Question

I try to join a list of ids from a xml stored in a column with ids from a column in another table. How can I extract them to get the matching rows.

I cant change the table. The schema can be reduced as:

CREATE TABLE abc([Data] [ntext] 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')
;
GO

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

And here is the query I wrote:

SELECT
      [productName]
      , [description]
  FROM ForgeRock
 INNER JOIN abc on ForgeRock.[Group] = abc.[Group]
 WHERE cast(abc.[Data] as xml).exist('//div/test[@name="Test1" and @enabled="True"]') = 1
  AND ('t.' + productName) LIKE ('%' + cast(abc.[Data] as xml).value('(//ul/i/a/@id)[1]', 'varchar(50)') + '%')

I know that the problem come from the last line, but I am not sure how to tackle the problem.

The actual output is:

productName     description
foo             Platform for building enterprise provisioning solutions

But the expected output should be:

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

I tried two solutions from SO (no they are not duplicated as far as I tried):

• XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
• Extracting Attributes from XML Fields in SQL Server 2008 Table

Try it Online

Answer 1

I would personally use STUFF to remove the 2 leading characters, and then compare, rather than a LIKE. I also, for my tests, have changed the data type of the column Data to xml as it is storing XML data, and ntext has been deprecated for 15~ years:

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

db<>fiddle

Answer 2

Please try the following solution.

I used NVARCHAR(MAX) data type instead of NTEXT. You may need to adjust the finalSELECT clause columns to accommodate your needs. I picked all of them just to show the all column values.

SQL

DECLARE @abc TABLE (xmldata NVARCHAR(MAX) NOT NULL, [Group] CHAR(3));
INSERT INTO @abc (xmldata, [Group]) VALUES
(N'<div>
    <test name="Test1" enabled="True"/>
    <ul>
        <i>
            <a id="t.foo"/>
        </i>
        <i>
            <a id="t.bar"/>
        </i>
    </ul>
</div>', 'ADG');

DECLARE @ForgeRock TABLE ([productName] VARCHAR(13), [description] VARCHAR(57), [Group] char(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');

;WITH rs AS
(
    SELECT [Group], TRY_CAST(xmldata AS XML) AS xmldata
    FROM @abc
)
SELECT rs.[Group], c.value('.', 'VARCHAR(30)') AS id
    , f.*
FROM rs
    CROSS APPLY xmldata.nodes('div[test[@name="Test1" and @enabled="True"]]/ul/i/a/@id') AS t(c)
    INNER JOIN @ForgeRock AS f ON rs.[Group] = f.[Group]
        AND SUBSTRING(c.value('.', 'VARCHAR(30)'), 3, 100) = f.productName;

Answer 3

Here is a pretty straightforward approach:

DECLARE @abc table ( [Data] [ntext] 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' );

DECLARE @ForgeRock table ( [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');

SELECT
    [productName], [description], x.f.value( '@id', 'VARCHAR(13)' ) AS id
FROM @ForgeRock ForgeRock
INNER JOIN @abc abc 
    ON ForgeRock.[Group] = abc.[Group]
CROSS APPLY ( SELECT CAST( abc.[data] AS xml ) AS x ) cx
CROSS APPLY cx.x.nodes( 'div[test[@name="Test1" and @enabled="True"]]/ul/i/a' ) x(f)
WHERE
    x.f.value( '@id', 'VARCHAR(13)' ) LIKE '%' + productName + '%';

Returns

+-------------+---------------------------------------------------------+-------+
| productName |                       description                       |  id   |
+-------------+---------------------------------------------------------+-------+
| foo         | Platform for building enterprise provisioning solutions | t.foo |
| bar         | Full-featured access management                         | t.bar |
+-------------+---------------------------------------------------------+-------+

Optionally, you can pass your @name value in as a SQL variable:

DECLARE @name varchar(50) = 'Test1';

SELECT
    [productName], [description], x.f.value( '@id', 'VARCHAR(13)' ) AS id
FROM @ForgeRock ForgeRock
INNER JOIN @abc abc 
    ON ForgeRock.[Group] = abc.[Group]
CROSS APPLY ( SELECT CAST( abc.[data] AS xml ) AS x ) cx
CROSS APPLY cx.x.nodes( 'div[test[@name=sql:variable("@name") and @enabled="True"]]/ul/i/a' ) x(f)
WHERE
    x.f.value( '@id', 'VARCHAR(13)' ) LIKE '%' + productName + '%';