patternMinor

stored proc to handle XML as input param for multiple record inserts

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

storedhandleinsertsxmlinputparamprocrecordformultiple

Problem

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 =
'
  
  
  
'

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:

 
52 

        2
        dig.s@mail.com
        dig s
        dig123
 

    3
    ash.k@mail.com
    ash kashish
 

    4
    dee.v@mail.com
    dee v
    dee123

Solution

I c an't speak for performance but this is one way of doing things.

declare @x xml

select @x = ' 
52 

        2
        dig.s@mail.com
        dig s
        dig123
 

    3
    ash.k@mail.com
    ash kashish
 

    4
    dee.v@mail.com
    dee v
    dee123
 
'

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)

Code Snippets

declare @x xml

select @x = '<MessageRecipient> 
<MessageId>52</MessageId> 

<Recipient>
        <RecipientTypeId>2</RecipientTypeId>
        <EmailAddress>dig.s@mail.com</EmailAddress>
        <FriendlyName>dig s</FriendlyName>
        <UFID>dig123</UFID>
</Recipient> 

<Recipient>
    <RecipientTypeId>3</RecipientTypeId>
    <EmailAddress>ash.k@mail.com</EmailAddress>
    <FriendlyName>ash k</FriendlyName><UFID>ashish</UFID>
</Recipient> 

<Recipient>
    <RecipientTypeId>4</RecipientTypeId>
    <EmailAddress>dee.v@mail.com</EmailAddress>
    <FriendlyName>dee v</FriendlyName>
    <UFID>dee123</UFID>
</Recipient> 
</MessageRecipient>'


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)

Context

StackExchange Database Administrators Q#2849, answer score: 3

Revisions (0)

No revisions yet.