Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 =
'<Attachment MessageId="2">
  <item FileboxId="FB1"  FileName="notes.doc" />
  <item FileboxId="FB2"  FileName="W2.jpeg" />
  <item FileboxId="FB3"  FileName="2011 Taxes.pdf" />
</Attachment>'

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:

<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>
share|improve this question
    
have you tried anything yet? What happens to the Attachment XML? –  gbn May 21 '11 at 7:53
    
Have you seen this question: Read xml file in sql server 2008? It should help a bit :-). –  Marian May 25 '11 at 14:30
add comment

2 Answers

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

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)
share|improve this answer
    
performance is a key part of the solution. that is the idea to move to XML and have multiple record inserts with one DB call. –  kacalapy May 24 '11 at 15:29
    
The above code works well within normal executions but you need to test it out yourself to check if it scales well for concurrent users and several executions. We don't have that info and that is the part you need to DIY. :-) –  Sankar Reddy May 24 '11 at 15:39
add comment

@Sankar Reddy's solution should work for parsing the XML but for performance I'd parse the XML outside of the database. I've seen problems before with high load parsing complex XML in the databases although I do use XML parsing but not for high loads.

I'd pass the shredded data in as a table, using a table valued parameters for SQL Server 2008+ or a temp table.

At least, separate the XML parsing into one stored proc that calls another that stores into the database. This way you can see any INSERTs with the final INSERT that would be masked by the XML parsing that tends to take 98% of a query in the graphical execution plans

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.