Syntax for xquery with namespace in the node

Question

We are trying to get IdValue from sql query. We are using sql server 2005.

DECLARE @MyXML XML
SET @MyXML = '<Candidate xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://ns.hr-xml.org/2007-04-15">
  <CandidateProfile>
    <ProfileId>
      <IdValue>9499063</IdValue>
    </ProfileId>
  </CandidateProfile>
  </Candidate>'

SELECT @MyXML.value('Candidate[1]/CandidateProfile[1]/ProfileId[1]','varchar(10)') AS Id

This is not working because of the name space in the Candidate tag.

Please let me know how to xquery with the namespace.

rene · Accepted Answer · 2011-02-24 17:28:53Z

4

    SELECT @MyXML.value(
    'declare namespace hr="http://ns.hr-xml.org/2007-04-15";
    hr:Candidate[1]/hr:CandidateProfile[1]/hr:ProfileId[1]','varchar(10)'
    ) 
    AS Id

http://msdn.microsoft.com/en-US/library/ms189075(v=SQL.90).aspx

answered Feb 24, 2011 at 17:28

rene

42.6k78 gold badges122 silver badges166 bronze badges

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

Collectives™ on Stack Overflow

Syntax for xquery with namespace in the node

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related