XQuery - Use column value to display correct node

Question

Post reply

XQuery - Use column value to display correct node

Norty303

SSC Enthusiast

Points: 120
More actions
January 29, 2020 at 10:50 am
Go to Answer
#3721130
Hi All
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>
```
SELECT TemplateId
,QuestionId
,Question.query('.') AS QuestionXml
FROM Template t
JOIN Question q ON q.TemplateId = t.TemplateId
JOIN TemplateXml x ON x.TemplateId = t.TemplateId
CROSS APPLY
x.XmlDoc.nodes('/assessment/template/question') AS Form(Question)
```
Mark Cowne

One Orange Chip

Points: 26952
More actions
January 29, 2020 at 11:52 am
Answer
#3721140
Sounds like you want this
```
cross apply x.XmlDoc.nodes('/assessment/template/question[@key=sql:column("QuestionID")]') AS Form(Question)
```
- This reply was modified 4 years, 9 months ago by Mark Cowne.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
January 29, 2020 at 5:47 pm

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Norty303 SSC Enthusiast Points: 120 More actions · Answer 1

Thank you Mark, that was the nudge I needed!

I'd looked at sql:column briefly and thought it looked somewhat set based.

Am I right in thinking you could do a similar thing with sql:variable but you'd need a loop to handle assigning each value of QuestionID to a variable?

Norty303 SSC Enthusiast Points: 120 More actions · Answer 2

Hmm, it appears that our XML Doc format is not consistent.

Sometimes the <question> sits at different levels.

So we see variations like this:

assessment/template/question

assessment/template/group/question

assessment/template/heading/group/question

and so forth.

Is there a syntax for selecting a node that includes <question> regardless of its position in the node?

I'm not sure that I can predict how many variations there might be.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

As simple as

cross apply x.XmlDoc.nodes('/assessment/template//question[@key=sql:column("QuestionID")]') AS Form(Question)

Note the double //

This reply was modified 4 years, 9 months ago by Mark Cowne.
This reply was modified 4 years, 9 months ago by Mark Cowne.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Norty303 SSC Enthusiast Points: 120 More actions · Answer 4

That is wonderfully simple, thanks Mark.

Seeing as we're on a roll, lets try for the complete set:

I can get the full Question node returned using this:

Question.query('.') AS QuestionXml

However, if I want to return just the value of an attribute within the node (key for example) I thought I might be able to do the following, but it doesn't seem to return the value for the QuestionID. It returns the first instance in the whole XML document, not the value returned in Form(Question) node.

Question.value('(/assessment/template//question/@key)[1]', 'varchar(128)')

Edit: It would appear I was over complicating it, and confused by the Stairway to XML guide I'd been reading. For the benefit of anyone else in my position...

Question.value('(@key)', 'varchar(128)')

This reply was modified 4 years, 9 months ago by Norty303.