Extracting Attribute Keys from XML into Table Column

• Pagan DBA

  Right there with Babe

  Points: 794

  More actions

  September 28, 2018 at 6:17 pm

  #377833

  My problem seems really simple and I expected to find solution on the forums already. Apologies if I am not searching properly and hence not locating the answer.

  DECLARE @input XML =
'<Data>
  <Attribute Name="e1" Value="404"/>
  <Attribute Name="e2" Value="Dontcare"/>
</Data>'
SELECT @input.value('(/Data/Attribute/@Name)[1]', 'VARCHAR (64)') as

  Above yields table with 1 column and 1 row with value 'e2'. Basically I want all the rows. So with above example I want table with 1 column and 2 rows one with 'e1' and one with 'e2'. The way I've written the above query, I can select only either 'e1' or 'e2' but I want a collection depending on how many <Attribute/> elements I got in my XML.

  Thanks in advance

• DesNorton

  SSC-Insane

  Points: 24557

  More actions

  September 29, 2018 at 2:41 am

  #2007419

  Pagan DBA - Friday, September 28, 2018 6:17 PM

  My problem seems really simple and I expected to find solution on the forums already. Apologies if I am not searching properly and hence not locating the answer.

  DECLARE @input XML =
'<Data>
  <Attribute Name="e1" Value="404"/>
  <Attribute Name="e2" Value="Dontcare"/>
</Data>'
SELECT @input.value('(/Data/Attribute/@Name)[1]', 'VARCHAR (64)') as

  Above yields table with 1 column and 1 row with value 'e2'. Basically I want all the rows. So with above example I want table with 1 column and 2 rows one with 'e1' and one with 'e2'. The way I've written the above query, I can select only either 'e1' or 'e2' but I want a collection depending on how many <Attribute/> elements I got in my XML.

  Thanks in advance

  Adjust the datatypes as need
  DECLARE @input XML =
'<Data>
 <Attribute Name="e1" Value="404"/>
<Attribute Name="e2" Value="Dontcare"/>
</Data>'

  SELECT
  [Name] = r.c.value('@Name', 'VARCHAR (64)')
, [Value] = r.c.value('@Value', 'VARCHAR (100)')
FROM @input.nodes('Data/Attribute') as R(c)

  ---

  How to post data/code on a forum to get the best help.