Get attributes from each Xml nodes

Question

I'm struggling to get queries of the type @message.value('(/Items/Item/@Name)['+CAST(@Index as nvarchar(50))+']','nvarchar(50)') to work. I need to know some better way to pass Attribute (Name) from each Item to Stored procedure. 

Declare @message xml
Set @message = '<Items>
<Item Name="A"/>

<Item Name="B"/>

<Items>'

DECLARE @Name nvarchar(200)

DECLARE @Count int
DECLARE @Index int = 1
DECLARE @tmpCount varchar(200)
SET @tmpCount = cast(@message.query('count(/Items/Item/*)')as varchar(50))
SET @Count = CAST(@tmpStatus as int)

WHILE @Index<=@Count
BEGIN

SET @Name = (select @message.value('(/Items/Item/@Name)['+CAST(@Index as nvarchar(50))+']','nvarchar(50)'))
EXEC some_StoredProcedurt @Name
SET @Index = @Index + 1
END

If anyone could point me in the right direction I would be very grateful.

Regards,

Ondrej.

Answer 1

Just use this to parse your XML and loop over the result:

DECLARE @message XML = N'<Items><Item Name="A" /><Item Name="B" /></Items>';

DECLARE @Result TABLE ( ItemName NVARCHAR(255) );

INSERT  INTO @Result
        SELECT  n.value('@Name', 'NVARCHAR(255)') AS ItemName
        FROM    @message.nodes('/Items/Item') m ( n );

SELECT  *
FROM    @Result;

Answer 2

Perfect, thanks.