How do I query an XML variable in SQL?-CodePudding

I want to get the value of the QuestionID node (1000000), but all I get in return is just a blank output.

Can you please point me in the right direction?

DECLARE @xml XML =
N'<QuestionaryAnswerList xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="schemas/src/system/antagelse/x20160401">
  <Answer>
    <QuestionId>1000000</QuestionId>
    <PossibleAnswerId>100000010</PossibleAnswerId>
    <AnswerValue>
      <Tekst>No</Tekst>
    </AnswerValue>
  </Answer>
  </QuestionaryAnswerList>';
 
  SELECT CAST(@xml.query('/QuestionaryAnswerList/Answer/PossibleAnswerId') AS VARCHAR(max))

CodePudding user response：

Quite simply : respect the XML namespace on your XML root node, and use the XQuery .value() function to get at the data:

-- establish the default XML namespace
WITH XMLNAMESPACES(DEFAULT 'schemas/src/system/antagelse/x20160401')
SELECT 
    @xml.value('(/QuestionaryAnswerList/Answer/QuestionId/text())[1]', 'varchar(20)')