0

I have a basic question but I did not manage to find an answer. I am new here, new to SQL and new to XML so I hope this level of question is not too basic.

I have an XML empty tags but want to address them in my select in sql:

<Question, Id='Q.01', Multiselect='False'>
    <Answers>
      <Answer, Id='A.01.01', IsChosen='False'>   
      <Ansewr, Id='A.01.02', IsChosen='True'>
    </Answers>
</Question>
<Question, Id='Q.02', Multiselect='False'>
    <Answers>
      <Answer, Id='A.02.01', IsChosen='True'>   
      <Ansewr, Id='A.02.02', IsChosen='False'>
    </Answers>
</Question>

I would like to make a table out of it:

A.01.01 | A.01.02 | A.02.01 | A.02.02 |
False   | True    | True    | False   |

I am trying

../Questions/Question[@Id="Q.01"]/ Answers/Answer[@Id="A.01.01"]/IsChosen'). value('.','nvarchar(10)') as A.01.01

But it is giving me an error. What is correct syntax in this case?

Anna Kubasiak
  • 5
  • 3
  • Your expression should consider the `IsChosen` value as an attribute `@IsChosen`. Then it should work. – zx485 May 26 '18 at 18:43
  • I tried it: I get XQuery [ClietTable.ClientData.query()]: Attribute may not appear outside of an element – Anna Kubasiak May 26 '18 at 18:57
  • You might want to add a tag or at least some detail in which context you use XQuery, try whether using `../Questions/Question[@Id="Q.01"]/ Answers/Answer[@Id="A.01.01"]/string(@IsChosen)` works. – Martin Honnen May 27 '18 at 05:22
  • The "XML" you have shown us isn't well-formed. This leaves us wondering what the real XML is like. – Michael Kay May 27 '18 at 08:05
  • Your query has an unbalanced single-quote and right-paren, and (since this is apparently an SQL query) is also missing an initial keyword like SELECT. Any of these would raise an error, but it might just be that you've mis-copied the query here. You should check that, and also show the error that you get. – Michael Dyck May 27 '18 at 14:16
  • Thank you for the hints so far. Of course this is a fragment of a larger file. I managed to solve my issue using function data() and specifying which element i am using: ClientData.value('data(/ClientProfile/Questions/Question[@Id="Question.01"]/Answers/Answer[@Id="A.01.01"]/@IsChosen)[1]' , 'nvarchar(10)')as Q0101 – Anna Kubasiak May 27 '18 at 16:45

0 Answers0