stored proc to handle XML as input param for multiple record inserts i have updated my proc from taking typical data types to take a single XML param so that i can call it once instead of multiple times for inserting multiple records.

here is a sample of my proc that works well...

ALTER PROCEDURE [dbo].[ins_Attachment_xml] 
    @XML XML
AS
BEGIN

        DECLARE @messageId INT
        SET @messageId = (SELECT n.c.value('@MessageId','int') FROM @XML.nodes('/Attachment') n(c))    

        SELECT  
            rr.item.value('@FileboxId','varchar(MAX)') AS FileboxId,  
            rr.item.value('@FileName','varchar(MAX)') AS FileName
        INTO #tmp  
        FROM @XML.nodes('/Attachment/item') AS rr(item)   

        INSERT INTO Attachment (MessageId,FileboxId,FileName)
        SELECT @messageId, T.FileboxId, T.FileName
        FROM #tmp T 
END


exec ins_Attachment_xml
@XML =
'&lt;Attachment MessageId="2"&gt;
  &lt;item FileboxId="FB1"  FileName="notes.doc" /&gt;
  &lt;item FileboxId="FB2"  FileName="W2.jpeg" /&gt;
  &lt;item FileboxId="FB3"  FileName="2011 Taxes.pdf" /&gt;
&lt;/Attachment&gt;'


now i need to change the XML param being passed in to this but dont know how to update the proc to make it work with the new XML structure. this is the new xml:

&lt;MessageRecipient&gt; 
&lt;MessageId&gt;52&lt;/MessageId&gt; 

&lt;Recipient&gt;
        &lt;RecipientTypeId&gt;2&lt;/RecipientTypeId&gt;
        &lt;EmailAddress&gt;dig.s@mail.com&lt;/EmailAddress&gt;
        &lt;FriendlyName&gt;dig s&lt;/FriendlyName&gt;
        &lt;UFID&gt;dig123&lt;/UFID&gt;
&lt;/Recipient&gt; 

&lt;Recipient&gt;
    &lt;RecipientTypeId&gt;3&lt;/RecipientTypeId&gt;
    &lt;EmailAddress&gt;ash.k@mail.com&lt;/EmailAddress&gt;
    &lt;FriendlyName&gt;ash k&lt;/FriendlyName&gt;&lt;UFID&gt;ashish&lt;/UFID&gt;
&lt;/Recipient&gt; 

&lt;Recipient&gt;
    &lt;RecipientTypeId&gt;4&lt;/RecipientTypeId&gt;
    &lt;EmailAddress&gt;dee.v@mail.com&lt;/EmailAddress&gt;
    &lt;FriendlyName&gt;dee v&lt;/FriendlyName&gt;
    &lt;UFID&gt;dee123&lt;/UFID&gt;
&lt;/Recipient&gt; 
&lt;/MessageRecipient&gt;
I c an't speak for performance but this is one way of doing things.

declare @x xml

select @x = '&lt;MessageRecipient&gt; 
&lt;MessageId&gt;52&lt;/MessageId&gt; 

&lt;Recipient&gt;
        &lt;RecipientTypeId&gt;2&lt;/RecipientTypeId&gt;
        &lt;EmailAddress&gt;dig.s@mail.com&lt;/EmailAddress&gt;
        &lt;FriendlyName&gt;dig s&lt;/FriendlyName&gt;
        &lt;UFID&gt;dig123&lt;/UFID&gt;
&lt;/Recipient&gt; 

&lt;Recipient&gt;
    &lt;RecipientTypeId&gt;3&lt;/RecipientTypeId&gt;
    &lt;EmailAddress&gt;ash.k@mail.com&lt;/EmailAddress&gt;
    &lt;FriendlyName&gt;ash k&lt;/FriendlyName&gt;&lt;UFID&gt;ashish&lt;/UFID&gt;
&lt;/Recipient&gt; 

&lt;Recipient&gt;
    &lt;RecipientTypeId&gt;4&lt;/RecipientTypeId&gt;
    &lt;EmailAddress&gt;dee.v@mail.com&lt;/EmailAddress&gt;
    &lt;FriendlyName&gt;dee v&lt;/FriendlyName&gt;
    &lt;UFID&gt;dee123&lt;/UFID&gt;
&lt;/Recipient&gt; 
&lt;/MessageRecipient&gt;'


select 
    Finaldata.R.value ('../MessageId[1]', 'NVARCHAR(10)') AS MessageID
    , Finaldata.R.value ('RecipientTypeId[1]', 'INT') AS RecipientTypeId
    , Finaldata.R.value ('EmailAddress[1]', 'NVARCHAR(200)') AS EmailAddress
    , Finaldata.R.value ('FriendlyName[1]', 'NVARCHAR(100)') AS FriendlyName
    , Finaldata.R.value ('UFID[1]', 'NVARCHAR(10)') AS UFID
from @x.nodes ('//MessageRecipient/Recipient') as Finaldata (R)