How to get value from XML attribute.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to get value from XML attribute.

Author

Topic

sadiqmodan
Starting Member

16 Posts

Posted - 2012-07-27 : 00:07:02

Hello,

I want to get attribute value from XML using Xquery.

MY XML is


<Answers>
  <AnswerSet>
     <Answer questionId="NodeID">155</Answer>
     <Answer questionId="ParentNode" selectedValue="12">Product</Answer>
   </AnswerSet>
</Answers>

Below is my query.


DECLARE @Field Varchar(100)
DECLARE @Attribute VARCHAR(100)
SET @Field='ParentNode'
SET @Attribute = 'selectedValue'
SELECT 
ISNULL(PropertyXML.value('(/Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")])[1]','varchar(max)'),'') ,
ISNULL(PropertyXML.value('(/Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")]/sql:variable(@Attribute) )[1]','varchar(max)'),'') FROM node WHERE id=155

below line is working fine with sql:variable

ISNULL(PropertyXML.value('(/Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")])[1]','varchar(max)'),'')

but I am getting error in below line..

ISNULL(PropertyXML.value('(/Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")]/sql:variable(@Attribute) )[1]','varchar(max)'),'')

I want to get provided attribute(@Attribute) value in result.

Please help me to resolve this issue.

Thanks in advance.

Regards,
Mohmedsadiq

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-27 : 06:17:14

Sql XQuery syntax does not let you specify the XPATH node as a variable as you have done. You have to use a filter just like you used for @questionId attribute, but this time looking for the node/attribute name rather than the value. So your XQuery syntax would be this:

PropertyXML.value('(/Answers/AnswerSet/Answer[@questionId=sql:variable("@Field")]/@*[fn:local-name()=sql:variable("@Attribute")] )[1]','varchar(max)')

The function local-name gets you the node or attribute name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources