4

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>[email protected]</EmailAddress>
        <FriendlyName>dig s</FriendlyName>
        <UFID>dig123</UFID>
</Recipient> 

<Recipient>
    <RecipientTypeId>3</RecipientTypeId>
    <EmailAddress>[email protected]</EmailAddress>
    <FriendlyName>ash k</FriendlyName><UFID>ashish</UFID>
</Recipient> 

<Recipient>
    <RecipientTypeId>4</RecipientTypeId>
    <EmailAddress>[email protected]</EmailAddress>
    <FriendlyName>dee v</FriendlyName>
    <UFID>dee123</UFID>
</Recipient> 
</MessageRecipient>
2
  • have you tried anything yet? What happens to the Attachment XML?
    – gbn
    Commented May 21, 2011 at 7:53
  • Have you seen this question: Read xml file in sql server 2008? It should help a bit :-).
    – Marian
    Commented May 25, 2011 at 14:30

2 Answers 2

3
+25

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>[email protected]</EmailAddress>
        <FriendlyName>dig s</FriendlyName>
        <UFID>dig123</UFID>
</Recipient> 

<Recipient>
    <RecipientTypeId>3</RecipientTypeId>
    <EmailAddress>[email protected]</EmailAddress>
    <FriendlyName>ash k</FriendlyName><UFID>ashish</UFID>
</Recipient> 

<Recipient>
    <RecipientTypeId>4</RecipientTypeId>
    <EmailAddress>[email protected]</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)
2
  • 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
    Commented May 24, 2011 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. :-) Commented May 24, 2011 at 15:39
2

@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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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