XQuery - Use column value to display correct node

  • Norty303

    SSC Enthusiast

    Points: 119

    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: 26734

    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 weeks 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: 119

    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: 119

    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: 26734

    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 weeks ago by  Mark Cowne.
    • This reply was modified 4 weeks 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: 119

    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 weeks ago by  Norty303.
  • This was removed by the editor as SPAM

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

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