How can i read this XML

Question

Post reply

How can i read this XML

vineet.bhargava

SSC Rookie

Points: 40
More actions
September 9, 2011 at 4:46 am

#142837

Hi

I want to read the below xml and take the attribute value tag value in some parameter.please Help

declare @p_xml xml

set @p_xml = '<p1:Attribute xmlns:p1="urn:oasis:names:tc:SAML:1.0:assertion" AttributeName="Priority" AttributeNamespace="RequestInfo">

<p1:AttributeValue>Low</p1:AttributeValue>

</p1:Attribute>'

My way of handling this which is throwing error.

declare @v_external_key_value varchar(100)

SELECT @v_external_key_value = tab.col.value('p1:AttributeValue[1]','varchar(100)')

FROM @p_xml.nodes('p1:Attribute') as tab(col)

select @v_external_key_value

Thanks

Vineet

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 1

You need a "WITH XMLNAMESPACES" clause

declare @v_external_key_value varchar(100);



WITH XMLNAMESPACES ('urn:oasis:names:tc:SAML:1.0:assertion' as p1)

SELECT @v_external_key_value = tab.col.value('p1:AttributeValue[1]','varchar(100)')

FROM @p_xml.nodes('p1:Attribute') as tab(col)

select @v_external_key_value;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

vineet.bhargava SSC Rookie Points: 40 More actions · Answer 2

I tried to Run this, But its giving me null value,

One more thing actually i want the value of Attribute value tag to use as a filter, so i need some statement which i can use in where clause.Please help me.

declare @v_external_key_value varchar(100)

declare @p_xml xml

set @p_xml = '<Attribute xmlns:p1="urn:oasis:names:tc:SAML:1.0:assertion" AttributeName="Priority" AttributeNamespace="RequestInfo">

</Attribute>'

;WITH XMLNAMESPACES ('urn:oasis:names:tc:SAML:1.0:assertion' as p1)

SELECT @v_external_key_value = tab.col.value('p1:AttributeValue[1]','varchar(100)')

FROM @p_xml.nodes('p1:Attribute') as tab(col)

select @v_external_key_value;

LutzM SSC Guru Points: 107049 More actions · Answer 3

You only need to reference the namespace in your query where it's actually used in the referenced XML element.

Based on your sample data you can query the Attrbute without referencing the p1 namespace since it's not to reference the element.

But you stil have to use the typed XML notation:



;WITH XMLNAMESPACES ('urn:oasis:names:tc:SAML:1.0:assertion' as p1)

SELECT @v_external_key_value = tab.col.value('AttributeValue[1]','varchar(100)')

FROM @p_xml.nodes('Attribute') as tab(col)

select @v_external_key_value;

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]