Insert multiple times XML data (SQL SERVER)

Question

I have some difficulties to solve my problem. I have a stored procedure which take 2 input parameters. The first one is a varchar, but in reality it's going to be a list of SomeID separated by comma like that '1,2,3,4'. The second parameter is XML, something like :

<objectList>
 <object>
 <objectTypeId>1</objectTypeId>
 <objectId>13456</objectId>
 <mode>ADD</mode>
 </object>
 <object>
 <objectTypeId>1</objectTypeId>
 <objectId>654321</objectId>
 <mode>ADD</mode>
 </object>
</objectList>

There is no limit to the number of objects in the XML.

I created a temp table with 4 columns (SomeID,objectTypeId,objectTypeId,mode)

If I have multiple ids in my first parameter (I use a function to split my varchar) and multiple objects in my second parameter, I want the temp table to look like this:

SomeID | objectTypeId | objectTypeId | mode
-------------------------------------------
 1 | 1 | 123456 | ADD
 1 | 1 | 654321 | ADD
 2 | 1 | 123456 | ADD
 2 | 1 | 654321 | ADD
 3 | 1 | 123456 | ADD
 3 | 1 | 654321 | ADD
 4 | 1 | 123456 | ADD
 4 | 1 | 654321 | ADD

I need that all of the someIDs match with each object of my XML parameter. I hope that I'm clear.

My actual code looks like that but it works only with multiple someIDs but only one XML object:

IF OBJECT_ID ('tempdb..#tmpObject') IS NOT NULL DROP TABLE #tmpObject
CREATE TABLE #tmpObject (tmpTagId INT, tmpObjectTypeId INT NULL, tmpObjectId INT NULL, tmpMode VARCHAR(16) NULL)
INSERT INTO #tmpObject (tmpTagId)
SELECT value FROM dbo.f_Split(@TagIdList,',')
UPDATE #tmpObject
SET tmpObjectTypeId = ParamValues.item.value('objectTypeId[1]','INT'),
 tmpObjectId = ParamValues.item.value('objectId[1]','INT'),
 tmpMode = ParamValues.item.value('mode[1]','VARCHAR(16)')
FROM @ObjectList.nodes('objectList/object') AS ParamValues(item)

Answer 1

One way is to insert all at once

INSERT INTO #tmpObject (tmpTagId,tmpObjectTypeId , tmpObjectId , tmpMode )
SELECT
 o.value ,
 ParamValues.item.value('objectTypeId[1]','INT'),
 ParamValues.item.value('objectId[1]','INT'),
 ParamValues.item.value('mode[1]','VARCHAR(16)')
FROM dbo.f_Split(@TagIdList,',')(@TagIdList,',') as o
 cross apply @ObjectList.nodes('objectList/object') AS ParamValues(item)
select * from #tmpObject order by tmpTagId

output:

tmpTagId tmpObjectTypeId tmpObjectId tmpMode
----------- --------------- ----------- ----------------
1 1 654321 ADD
1 1 13456 ADD
2 1 13456 ADD
2 1 654321 ADD
3 1 654321 ADD
3 1 13456 ADD
4 1 13456 ADD
4 1 654321 ADD

• Matthieu RGX: Perfect ! works like a charm !
• Sabin B: Super! I'm glad that is working.