Sql-server – Insert multiple times XML data (SQL SERVER)

sql serversql-server-2012t-sql

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)

Best Answer

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