1

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)
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
  • 1
    Perfect ! works like a charm ! – Matthieu RGX Feb 20 at 10:15
  • Super! I'm glad that is working. – Sabin Bio Feb 20 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.