How can i read this XML

• vineet.bhargava

  Valued Member

  Points: 50

  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

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  September 9, 2011 at 4:54 am

  #1379114

  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

  Valued Member

  Points: 50

  More actions

  September 9, 2011 at 4:58 am

  #1379117

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

  <AttributeValue>Low</AttributeValue>

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

  September 9, 2011 at 5:40 am

  #1379130

  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]