First time with XQuery and despite trying to read as many guides as I can, I'm hitting a bit of a mental block.
We have a form design application that stores templates and details of the questions that relate to each template in relational tables.
The attributes for the template and questions are stored in an XML document, one for each template ID.
I'm trying to return some attributes from the XML document that relate to each QuestionId, but I'm stuck at how I relate each ID value from the Question table with the ID attribute that is within the <Question> node.
I believe this may involve use of sql:variable but I could do with a nudge in terms of seeing how it is used.
The following is not a practical example but explains the issue in its simplist form. This returns the first <Question> node from the XML document, not the one that relates to the QuestionId
The XML node is defined as
<question key=[QuestionId] Attrib1="Y" Attrib2="N">
,Question.query('.') AS QuestionXml
FROM Template t
JOIN Question q ON q.TemplateId = t.TemplateId
JOIN TemplateXml x ON x.TemplateId = t.TemplateId
x.XmlDoc.nodes('/assessment/template/question') AS Form(Question)