sql - Select multiple xml nodes using crossapply -

i'm trying select multiple nodes xml variable passed stored procedure. 1 node it's xml. how can list of item names , string values?

test code:

declare @t table (allxml ntext)  insert @t values('<error>         <item name="item 1">         <value string="string 1" />         </item>         <item name="item 2">         <value string="string 2" />         </item>         <item name="item 3">         <value string="string 3" />         </item>         </error>')  select      cast(allxml xml).value('(/error/item/@name)[1]', 'varchar(100)' ),     cast(allxml xml).value('(/error/item/value/@string)[1]', 'varchar(max)' )         @t 

desired result:

item 1     string 1 item 2     string 2 item 3     string 3 

you can achieve using cross apply. , sub-select.

select      m.c.value('(@name)[1]', 'varchar(100)') name,     m.c.value('(value/@string)[1]', 'varchar(max)') value       (         select cast(allxml xml) allxml          @t      ) data     cross apply allxml.nodes('/error/item') m(c) 

or can use 1 more cross apply below,

   select          m.c.value('(@name)[1]', 'varchar(100)') name,         m.c.value('(value/@string)[1]', 'varchar(max)') value     @t         cross apply (select cast(allxml xml)) d(d)         cross apply d.d.nodes('/error/item') m(c)