XML query fetch attributes as records/rows

There are many ways to shred xml but I believe this may help you out giving some more concepts to the mix:

declare @xml xml = 
'<category ccode="ct8">
  <columns>
    <col colcode="cl_prodn" displaytext="Prodname" responsetype="textbox" tooltip="testts" isrequired="" displayorder="1" />
    <col colcode="cl_descs" displaytext="Descs" responsetype="textarea" tooltip="atser" isrequired="on" displayorder="2" />
  </columns>
</category>'

-- get them one at a time by hunting for specific identifier
select @xml.query('(category/columns/col[@displaytext = "Prodname"])')  -- queries for node
select @xml.value('(category/columns/col[@displaytext = "Prodname"]/@colcode)[1]', 'varchar(max)')  -- gives value of node by description
select @xml.value('(category/columns/col[@displaytext = "Descs"]/@colcode)[1]', 'varchar(max)')


-- get them all at once with the (reference).(column).nodes method applied to an xml value in a table.
declare @X table ( x xml);

insert into @X values (@xml)

select 
    t.query('.')
,   t.value('(@colcode)[1]', 'varchar(max)')
from @X a
 cross apply a.x.nodes('//category/columns/col') as n(t)