CODEWITHSUNDEEP
sql-serverxquery
Ahmed
Ahmed

Reputation: 23

Querying XML column against another

I have users table that contains an xml column to store user defined fields for demographics, which come from a profile questionnaire.

Demographic sample entry for a user:

 <demographics>
      <question_1>answer_1</question_1>
      <question_2>answer_2</question_2>
      <question_3>answer_3</question_3>
    </demographics>

An assignments table has another xml column for eligibility of users, which again is based on the profile questionnaire.

<Eligibility>
  <Expression>
    <Question>question_1</Question>
    <Answer>answer_1</Answer>
  </Expression>
  <Expression>
    <Question>question_3</Question>
    <Answer>answer_3</Answer>
  </Expression>
</Eligibility>

I need to find all the assignments that match a user's demographics based on the xml eligibility criteria defined in the table. The operator between the eligibility criteria has to be an 'AND'. Listed below is the query I have written

SELECT * 
    FROM Assignments AS a
    WHERE Eligibility.exist('/Eligibility/Expression[Question= 1 and Answer=1]') = 1 
    AND Eligibility.exist('/Eligibility/Expression[Question= 2 and Answer=2]') = 1

Suppose we have two assignments A1 and A2. A1 has the criteria as Question1 = Answer1 and A2 has the criteria Question2 = Answer2. Both assignments satisfy the eligibility for the user. However, the above query does not return either of the two assignments, because of the 'AND' between the eligibility criteria.

Any help would be appreciated.

Upvotes: 2

Views: 79

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

This is actually a relational division query that uses XML data.

Comments in the code describes what is going on in each part of the query.

-- A users demographics
declare @UserDem xml = '
<demographics>
  <question_1>answer_1</question_1>
  <question_2>answer_2</question_2>
  <question_3>answer_3</question_3>
</demographics>';

-- Get ID for the assignment
select A.ID
from Assignments as A 
  -- Calculate the number of Expression there are in each assignment
  cross apply (select A.Eligibility.value('count(/Eligibility/Expression)', 'int')) as C(ECount)
  -- Shred on Expression 
  cross apply A.Eligibility.nodes('/Eligibility/Expression') as E(X)
  -- Join to demographics on question and answer
  inner join @UserDem.nodes('demographics/*') as D(X)
    on D.X.value('local-name(.)[1]', 'nvarchar(100)') = E.X.value('(Question/text())[1]', 'nvarchar(100)') and
       D.X.value('text()[1]', 'nvarchar(100)') = E.X.value('(Answer/text())[1]', 'nvarchar(100)')
-- Group on ID and get the Assignments that are fully covered by the demographic
group by A.ID, C.ECount
having count(*) = C.ECount;

SQL Fiddle

Upvotes: 4

Related Questions