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)