0

I have the following XML in my SQL table:

<SiteSurveyData>
  <SurveyQuestionAnswers>
    <QuestionGUID>61</QuestionGUID>
    <Answer>XXXXXXX</Answer>
  </SurveyQuestionAnswers>
  <SurveyQuestionAnswers>
    <QuestionGUID>62</QuestionGUID>
    <Answer>XXX</Answer>
  </SurveyQuestionAnswers>
  <SurveyQuestionAnswers>
    <QuestionGUID>64</QuestionGUID>
    <Answer>Both</Answer>
  </SurveyQuestionAnswers>
  <SurveyQuestionAnswers>
    <QuestionGUID>63</QuestionGUID>
    <Answer>aal5snap</Answer>
  </SurveyQuestionAnswers>
  <SurveyQuestionAnswers>
    <QuestionGUID>81</QuestionGUID>
    <Answer>00</Answer>
  </SurveyQuestionAnswers>

What I'm trying to query is that I want the value in /Answer where the /QuestionGUID = 61 and 62.

I tried using this:

value('(/SiteSurveyData/SurveyQuestionAnswers/QuestionGUID)[1]'

But it just return the first QuestionGUID.
How do i write the query so that it returns the answer for QuestionGUID 61 and 62?

Or what I just want to get the /Answer for QuestionGuid = 61.
It's not always the first element, is it possible to write the query so that it will return the value for QuestionGuid=61?

user2665372
  • 193
  • 1
  • 2
  • 5
  • You may need to use a cursor. Here is a sample https://stackoverflow.com/questions/19034382/sql-2012-iterate-through-an-xml-list-better-alternative-to-a-while-loop – Si8 Aug 15 '17 at 17:03
  • What if i just want all the ones with Question = 61 returned? It's not necessarily always the first one. – user2665372 Aug 15 '17 at 17:10

1 Answers1

1
DECLARE @t TABLE(id INT IDENTITY(1,1),x XML);
INSERT INTO @t(x)VALUES('<SiteSurveyData>
  <SurveyQuestionAnswers>
    <QuestionGUID>61</QuestionGUID>
    <Answer>XXXXXXX</Answer>
  </SurveyQuestionAnswers>
  <SurveyQuestionAnswers>
    <QuestionGUID>62</QuestionGUID>
    <Answer>XXX</Answer>
  </SurveyQuestionAnswers>
  <SurveyQuestionAnswers>
    <QuestionGUID>64</QuestionGUID>
    <Answer>Both</Answer>
  </SurveyQuestionAnswers>
  <SurveyQuestionAnswers>
    <QuestionGUID>63</QuestionGUID>
    <Answer>aal5snap</Answer>
  </SurveyQuestionAnswers>
  <SurveyQuestionAnswers>
    <QuestionGUID>81</QuestionGUID>
    <Answer>00</Answer>
  </SurveyQuestionAnswers></SiteSurveyData>');

SELECT
    n.v.value('./QuestionGUID[1]','NVARCHAR(256)') AS QuestionGUID,
    n.v.value('./Answer[1]','NVARCHAR(256)') AS Answer
FROM
    @t AS t
    CROSS APPLY t.x.nodes('SiteSurveyData/SurveyQuestionAnswers[QuestionGUID="61" or QuestionGUID="62"]') AS n(v)

Result is what you want (two rows with XXXXXXX and XXX as answers). This applies an XPath expression looking for SurveyQuestionAnswers with a QuestionGUID child of either 61 or 62.

TT.
  • 15,774
  • 6
  • 47
  • 88
  • What does the n come from? The n.v.value? – user2665372 Aug 15 '17 at 18:44
  • @user2665372 When you cross apply with a function that returns nodes, you need to assign a table alias and column alias. That's the `AS n(v)` following the nodes function. The `n` is the tabe alias, the `v` is the column alias. Then, since the `v` column is a node, you need to extract a value from it using the `value`function. So the `n.v.value(...)` applies the value function to the node in column `n.v`. – TT. Aug 15 '17 at 19:31