CODEWITHSUNDEEP
sqlsql-serverxml
jedgard
jedgard

Reputation: 868

Handling XML using SQL to Create or Update multiple tables

I am receiving an xml into my stored procedure as follows:

<records>
    <record>Name="Charles" Number="8" CustomerId ="3" Date="12/17/2013 12:00 AM"/>
</records>

So I read the data as follows: in my procedure and insert

    INSERT INTO CustomerNumbers (Name, Number, CustomerId)  
    SELECT xtable.item.value('@Name[1]', 'NVARCHAR(100)') AS Name, 
           xtable.item.value('@Number[1]', 'INT') AS Number,
           xtable.item.value('@CustomerId[1]', 'INT') AS CustomerId
    FROM @p_XmlPassed.nodes('//records/record') AS xtable(item)

Now this, works, but i have two problems

1) If the record already exists i should not insert it but updated (need to check per row)

2) I need to also update another table [Notifications] based on the CustomerId and Date. (need to check per row)

What i have above inserts fine and reads the xml fine.. but this part is a but confusing to me not sure about the best way to handle it.

How should I go about this?, i need to grab the CustomerId and Date values to update [Notifications] while this is happening?

Upvotes: 1

Views: 314

Answers (3)

granadaCoder
granadaCoder

Reputation: 27852

My rule of thumb is that if I hit "one table", I push directly into that table.

If I hit 2 or more, I shred the xml into a #temp (or @variable) table, and then do Insert/Update/Upsert(Merge) from that #temp table.

If I have more than 1 destination table, then I do my shredding outside of the BEGIN TRAN/COMMIT TRAN. Then do the Upsert stuff inside the TRAN.

Here is a "typical" setup for me.

Also note the "where not exists" if you are inserting (only). (an option, not necessarily your scenario)

/*

EXEC dbo.uspMyEntityUpsertByXml ' '

*/

IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspMyEntityUpsertByXml'
) BEGIN DROP PROCEDURE [dbo].[uspMyEntityUpsertByXml] END

GO

CREATE Procedure dbo.uspMyEntityUpsertByXml ( @parametersXML XML ) AS

BEGIN

SET NOCOUNT ON




IF OBJECT_ID('tempdb..#Holder') IS NOT NULL
begin
        drop table #Holder
end


CREATE TABLE #Holder
( 
SurrogateKeyIDENTITY int not null IDENTITY (1,1) , 
NameOf NVARCHAR(100) , 
Number int , 
CustomerId int
)






/* Start XML usage */  
/* Only incur the penalty of XML parsing, if XML was specified */
if (@parametersXML IS NOT NULL) AND (Datalength(@parametersXML) > 10 ) 
    /* Only process the xml If the xml exists, and it has at least 10 chars. 10 is just a somewhat */ 
    /* arbritrary number, saying, that an xml doc with <10 chars doesn't have a whole lot going for it */ /* || DataLength is used for Text datatype  */

BEGIN


    INSERT INTO #Holder ( NameOf , Number , CustomerId )
        SELECT x.item.value('@Name[1]', 'NVARCHAR(100)') AS Name, 
               x.item.value('@Number[1]', 'INT') AS Number,
               x.item.value('@CustomerId[1]', 'INT') AS CustomerId
        FROM @parametersXML.nodes('//rows/row') AS x(item)


END 
/* End XML usage */



/*
    INSERT INTO dbo.CustomerNumbers (Name, Number, CustomerId)  
*/
Select NameOf , Number, CustomerId from #Holder h
/* 
    Where not exists ( select null from dbo.CustomerNumbers innerRealTable
                            where innerRealTable.Name = h.NameOf
                                and innerRealTable.Number = h.Number
                                and innerRealTable.CustomerId = h.CustomerId
                    )
*/





IF OBJECT_ID('tempdb..#Holder') IS NOT NULL
begin
        drop table #Holder
end

END

Upvotes: 0

user2810910
user2810910

Reputation: 287

Another consideration is to add a where clause in your SQL to prevent updating an existing record. Rough example (I'll let you work out the details):
update ----- where not (column1= value1 and column2 =value2)

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

DECLARE @xml XML;

SET @xml = '<rows>
                <row Name="Charles" Number="8" CustomerId ="3" Date="12/17/2013 12:00 AM"/>
                <row Name="Mary" Number="7" CustomerId ="6" Date="12/19/2013 12:00 AM"/>
                <row Name="Miriam" Number="10" CustomerId ="10" Date="12/18/2013 12:00 AM"/>
            </rows>'

    --INSERT INTO CustomerNumbers (Name, Number, CustomerId)  
    SELECT x.item.value('@Name[1]', 'NVARCHAR(100)') AS Name, 
           x.item.value('@Number[1]', 'INT') AS Number,
           x.item.value('@CustomerId[1]', 'INT') AS CustomerId,
           x.item.value('@Date[1]', 'DATETIME') AS [Date] 

    INTO #TempTable       --<-- Data into Temp Table      

    FROM @xml.nodes('//rows/row') AS x(item)

Merge Statement

MERGE CustomerNumbers AS trg
USING (SELECT Name,Number,CustomerId,[Date] FROM #TempTable) AS src
ON trg.CustomerId = src.CustomerId

WHEN MATCHED THEN UPDATE 
   SET trg.Name   = src.Name,
       trg.Number = src.Number,
       trg.[Date] = src.[Date]

WHEN NOT MATCHED THEN
    INSERT(Name,Number,CustomerId,[Date])
    VALUES(src.Name, src.Number, src.CustomerId, src.[Date]);
GO

/*Another Merge Statement for your second table here then drop the temp table*/

DROP TABLE #TempTable
GO

Upvotes: 2

Related Questions