Without loop how to insert values into table from XML node

I have the following requirement.

I have an XML variable of values.

I now should insert the values for this XML variable to a table (Table A), but need to check if the items (one by one) in the XML variable are already present in another table (Table B) and the count of that individual item present in table B is only once.

If more than once/ or not present in Table B, do not insert into the main table – Table A.

How to achieve this without using loops.

Declare @Names xml
set @Names ='<List><CNames>One</CNames><CNames>Two</CNames></List>'

**When used below xml variable of values become a column of values :**

SELECT tbl.colname.value('text()[1]','varchar(10)') AS CN
FROM @Names ('/List/CNames') tbl(colname);

CN
-------
One
Two

Now in Table B — have to check if the items ‘One’ and ‘Two’ are present, if present are they present only once.

Tired using while loops which works fine, but want to achieve without loop.

Answer

Since you already have a selection of your rows to evaluate (@values in my solution), I started from there.

Sample data

-- existing table and data
declare @data table
(
    CN nvarchar(10)
);
insert into @data (CN) values
('One'),
('Three'),
('Three');

-- new values extracted from XML
declare @values table
(
    CN nvarchar(10)
);
insert into @values (CN) values
('One'),    -- exists 1x and will be inserted
('Two'),    -- does not exist and will not be inserted
('Three');  -- exists 2x and will not be inserted

Solution

insert into @data (CN)
select v.CN
from @values v
cross apply (   select count(1) as 'Cnt'
                from @data d
                where d.CN = v.CN ) c
where c.Cnt = 1;

Result

select d.CN
from @data d
order by d.CN;


CN
----------
One
One      --> new inserted value
Three
Three