Запрос столбца XML к другому столбцу

У меня есть таблица пользователей, содержащая столбец xml для хранения пользовательских полей для демографических данных, которые приходят из анкеты профиля.

Демографическая выборка для пользователя: 

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

Таблица назначений содержит еще один столбец xml для определения приемлемости пользователей, который также основан на анкете профиля.

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

Мне нужно найти все назначения, соответствующие демографическим данным пользователя, на основе критериев приемлемости xml, определенных в таблице. оператор между критериями приемлемости должно быть " и " . Ниже приведен запрос, который я написал

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
Предположим, что у нас есть два задания A1 и A2. A1 имеет критерии в виде Question1 = Answer1 и A2 имеет критерии Question2 = Answer2. Оба назначения удовлетворяют требованиям пользователя. Однако приведенный выше запрос не возвращает ни одно из двух назначений из-за " и " между критериями приемлемости.

Любая помощь будет признательна.

1 2
sql-server xquery

1 ответ:

Это фактически запросреляционного подразделения , который использует XML-данные.

Комментарии в коде описывают то, что происходит в каждой части запроса. 
-- 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

4