Sql-server – stored proc to handle XML as input param for multiple record inserts

sql-server-2005xml

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>

Best Answer

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)

Related Solutions

Sql-server – Most efficient way to return multiple aggregates in a single stored proc

COUNT..CASE is the usual way. For "Sent" though it's bit trickier because it's a different uid filter which requires 2 queries on the Message table

I've used this construct to allow for zero row for to/from. It avoids an OR and adding a uid filter to each CASE.

Each derived table (can write as a CTE) will return only one row and I've used a MAX on uid to avoid GROUP BY too in case anyone wonders

SELECT
    ISNULL(M.Inbox, 0) AS Inbox,
    ISNULL(M.Unread, 0) AS Unread,
    ISNULL(M.Flagged, 0) AS Flagged,
    ISNULL(M.Drafts, 0) AS Drafts,
    ISNULL(S.Sent, 0) AS Sent,
    ISNULL(M.Archived, 0) AS Archived
FROM
    (
    SELECT
        COUNT(*) AS InBox,
        COUNT(CASE WHEN isread = 0 THEN 1 ELSE NULL END) Unread,
        COUNT(CASE WHEN isFlagged = 1 THEN 1 ELSE NULL END) Flagged,
        COUNT(CASE WHEN isDraft = 1 THEN 1 ELSE NULL END) Drafts,
        S.Sent,
        COUNT(CASE WHEN isArchived = 0 THEN 1 ELSE NULL END) Archived
    FROM
        Message
    WHERE
        [to] = [uid] 
    ) M
    FULL OUTER JOIN
    (
    SELECT COUNT(*) AS Sent
    FROM Message
    WHERE [from] = [uid]
    ) S ON 1=1

Edit:

An aggregate without GROUP BY will always return a result (see SO here). I forgot that.

I've updated my SQL

Sql-server – high ‘total_worker_time’ for stored proc using OPENQUERY in SQL Server 2005

In sys.dm_exec_query_stats total_worker_time is:

Total amount of CPU time, in microseconds, that was consumed by executions of this plan since it was compiled.

So it is the total CPU time for all executions of the cached plan. The execution_count column gives the number of times the complete plan has executed, and there is also information about the lowest and highest values recorded. The figures from the DMV are in microseconds, whereas STATISTICS TIME output shows milliseconds. Finally, the MAXDOP hint only applies to the portion of the query that runs locally.

I noticed you are running SQL Server 2005 SP2. There were all sorts of timing issues with that (now unsupported) release, for some detail, see:

SQL Server 2005 SP2 will introduce new messages to the error log related to timing activities.

Consider moving to 2005 SP4.

Best Answer

Related Solutions

Sql-server – Most efficient way to return multiple aggregates in a single stored proc

Sql-server – high ‘total_worker_time’ for stored proc using OPENQUERY in SQL Server 2005

Related Question