OK, So I'm Getting some XML Like this<PifToMepData Mode=\"3\"><MEP MEPName=\"Test Combining PIFs\" MEPType=\"Close\" PIFRecId=\"12\" IsPrimaryPIF=\"1\"><AssignedTo>X000525</AssignedTo></MEP><MEP MEPName=\"Test Combining PIFs\" MEPType=\"Close\" PIFRecId=\"13\" IsPrimaryPIF=\"0\"><AssignedTo>X000525</AssignedTo></MEP></PifToMepData>
I then use INSERT INTO #myTemp99 ( Mode , MEPName , MEPType , PIFRecId , IsPrimaryPIF , AssignedTo) SELECT * FROM OPENXML (@idoc, './/AssignedTo',3) WITH( Mode varchar(20) '../../@Mode' ,MEPName varchar(2000) '../@MEPName' ,MEPType varchar(500) '../@MEPType' ,PIFRecId int '../@PIFRecId' ,IsPrimaryPIF varchar(20) '../@IsPrimaryPIF' ,AssignedTo varchar(20) '.' )
To Parse it out. This then has to be inserted into another table with an identity column (damn Identity column), and I need to grab the generated id for each row, because then there are other children tables that need to be populated.Question: Is there any set way to grab multiple generated id's?Or do I need to loop or use a cursor?Is the 1% of the time that they are needed?Any ideas?Here's the temp table DDLCREATE TABLE #myTemp99 ( Mode varchar(20) , MEPName varchar(2000) , MEPType varchar(500) , PIFRecId int , IsPrimaryPIF varchar(20) , AssignedTo varchar(20))
And the document prepDECALRE @idoc varchar(8000)--Just assign the sampel dataEXEC sp_xml_preparedocument @idoc OUTPUT, @docEXEC sp_xml_removedocument @idocBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam
XML and scope identity
Posted - 2007-11-26 : 16:45:34
works pretty ok_______________________________________________Causing trouble since 1980blog: