SQL Query based on XML (list) input

Question

I have table like this:

MYDATA

Company Reference  FirstName  Surname
1         A001       Test1Name  Test1Surname
2         A001       Test2Name  Test2Surname
3         A001       Test3Name  Test3Surname

and xml variable @searchList that hold this data:

<list>
    <item>
        <company>1</company>
        <reference>A001</reference>
    </item>
    <item>
        <company>2</company>
        <reference>A001</reference>
    </item>
    <item>
        <company>3</company>
        <reference>A001</reference>
    </item>
</list>

Usually where there is just one condition I would do something like:

select * from dbo.MYDATA rec
    where Reference in
        (
            select entity.value('(reference/text())[1]', 'varchar(32)') 
            from searchList.nodes('/list/item') as T(entity)
        )

Where there are 2 conditions (company = xml.company and reference = xml.reference) it gets more complex.

One way to do it is to create a temporary table with companyid and reference columns, insert everything from xml into that temporary table and do a join on the MYDATA table. Other way is a similar thing but with a subquery.

Is there any other more elegant and mostly important performance wise way to accomplish this?

---

UPDATE

This is what gave me the best performance result for now:

DECLARE @tbl TABLE(Id INT, Company INT,Reference VARCHAR(10),FirstName VARCHAR(100),Surname VARCHAR(100));
INSERT INTO @tbl VALUES
 (1, 1,'A001','Test1Name','Test1Surname')
,(2, 2,'A001','Test2Name','Test2Surname')
,(3, 3,'A001','Test3Name','Test3Surname');

DECLARE @xml XML=
N'<list>
    <item>
        <company>1</company>
        <reference>A001</reference>
    </item>
    <item>
        <company>2</company>
        <reference>A001</reference>
    </item>
    <item>
        <company>3</company>
        <reference>A001</reference>
    </item>
</list>';

WITH SEARCHLIST (company, reference) as
(
    select li.value('(company/text())[1]', 'int') AS company
          ,li.value('(reference/text())[1]', 'nvarchar(max)') AS reference
    from @xml.nodes('/list/item') AS A(li)
)

select rec.* from SEARCHLIST srl
    left join @tbl rec on srl.reference = rec.Reference and srl.company = rec.Company
where rec.Id is not null;

Answer 1

You can use a CTE to get a derived table out of your XML using .nodes().

This allows you, to deal with the values taken from your XML as if they were a normal table:

DECLARE @tbl TABLE(Company INT,Reference VARCHAR(10),FirstName VARCHAR(100),Surname VARCHAR(100));
INSERT INTO @tbl VALUES
 (1,'A001','Test1Name','Test1Surname')
,(2,'A001','Test2Name','Test2Surname')
,(3,'A001','Test3Name','Test3Surname');

DECLARE @xml XML=
N'<list>
    <item>
        <company>1</company>
        <reference>A001</reference>
    </item>
    <item>
        <company>2</company>
        <reference>A001</reference>
    </item>
    <item>
        <company>3</company>
        <reference>A001</reference>
    </item>
</list>';

WITH CTE AS
(
    SELECT li.value(N'company[1]',N'int') AS company
          ,li.value(N'reference[1]',N'nvarchar(max)') AS reference
    FROM @xml.nodes(N'/list/item') AS A(li)
)
SELECT *
FROM @tbl AS t
INNER JOIN CTE ON t.Reference=CTE.reference --use any column however you like it

UPDATE Performance

To be honest: You write Other way is a similar thing but with a subquery. The CTE approach is technically spoken quite the same as a sub-select in this case.

Depending on the size of your XML it is - in most cases - faster to use the XML method .exist() where you can check the existance of an XPath with predicates.

The CTE appraoch must read out the whole set, just to tear it down again. If performance matters, you can include a filter predicate in .nodes() too.

But the best solution is highly depending on your actual needs...

UPDATE 2 working example with .exist()

Try this (changed the sample data to carry differing values)

DECLARE @tbl TABLE(Company INT,Reference VARCHAR(10),FirstName VARCHAR(100),Surname VARCHAR(100));
INSERT INTO @tbl VALUES
 (1,'A001','Test1Name','Test1Surname')
,(2,'A002','Test2Name','Test2Surname')
,(3,'A004','Test3Name','Test3Surname'); <-- Will not be returned

DECLARE @xml XML=
N'<list>
    <item>
        <company>1</company>
        <reference>A001</reference>
    </item>
    <item>
        <company>2</company>
        <reference>A002</reference>
    </item>
    <item>
        <company>3</company>
        <reference>A003</reference>
    </item>
</list>';

select * from @tbl rec
where @xml.exist(N'/list/item/reference[text()=sql:column("Reference")]')=1