How do I query an XML variable in SQL?

Question

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))

You need to define your namespaces; specifically your default one (schemas/src/system/antagelse/x20160401). The fact the value is in a variable is irrelevant. — Thom A
– Thom A ♦, Commented Feb 14, 2022 at 11:49

marc_s · Accepted Answer · 2022-02-14 11:50:51Z

2

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)')

answered Feb 14, 2022 at 11:50

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

givnv Over a year ago

Hello @marc_s, that worked. Thanks a ton for your help. Can you please explain to me, why I have to reference only the third namespace and not the first two as well? Thanks for your time :)

marc_s Over a year ago

@givnv: the first two are "standard" namespaces - plus they are defined with a prefix (xsd and xsi) which is never used in the XML document; the third is defined without a prefix, and thus is the default namespace that applies to all XML nodes (unless they're specifically marked with a prefix of another namespace).

Collectives™ on Stack Overflow

How do I query an XML variable in SQL?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related