Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: XML and Scope Identity

    OK, So I'm Getting some XML Like this

    Code:
    <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

    Code:
    	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 DDL

    Code:
    CREATE TABLE #myTemp99 (
    		  Mode varchar(20)
    		, MEPName varchar(2000)
    		, MEPType varchar(500)
    		, PIFRecId int
    		, IsPrimaryPIF varchar(20)
    		, AssignedTo varchar(20))
    And the document prep
    DECALRE @idoc varchar(8000)
    --Just assign the sampel data
    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

    EXEC sp_xml_removedocument @idoc

    Brett

    8-)

    [red]Hint: Want your questions answered fast? Follow the direction in this link[/red]
    http://weblogs.sqlteam.com/brettk/ar...5/25/5276.aspx

    Add yourself!
    http://www.frappr.com/sqlteam
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Nov 2005
    Posts
    122
    Use the OUTPUT clause with the INSERT statement.

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Brett,

    i am not saying this is the best solution but I have put code together for data loading operations that does the following. I am aware that this is not very appropriate for production code that may be executed by several simultaneous users.
    Code:
    declare @maxid int
    
    set @maxid = (SELECT MAX(ID) FROM MyTable)
    
    SELECT @maxid as id,
    IDENTITY(int,1,1) as incrementor,
    someotherfield
    INTO #MyBucketTable
    FROM someothertable
    
    UPDATE  #MyBucketTable SET id = id + incrementor
    
    SET IDENTITY_INSERT ThisOtherTable ON
    
    INSERT INTO ThisOtherTable(id,MyOtherField)
    SELECT id,someotherfield
    FROM #MyBucketTable
    
    SET IDENTITY_INSERT ThisOtherTable OFF
    
    Print 'Use the ids from #MyBucketTable Later'
    
    DROP TABLE #MyBucketTable
    Identity is not evil. XML is.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    This sql 2k

    And it begs the question, what happens when you ident on, if you try and put a value that already exists...dup key?

    Simple enough to test

    Still, odds are that it can be a problem

    holes are holes...I don't like that
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I guess the answer is no

    I really have to get on 2k5
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Don't ya go gettin' above your raisins wit' all that XML...
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r123456
    Don't ya go gettin' above your raisins wit' all that XML...

    Huh?

    I know you're trying to communicate, but I just don't understand you
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Damn, I'm gonna have to do this
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by Brett Kaiser
    This sql 2k

    And it begs the question, what happens when you ident on, if you try and put a value that already exists...dup key?

    Simple enough to test

    Still, odds are that it can be a problem

    holes are holes...I don't like that
    lo siento. the code works but like i said it is not suitable for production application code. IDENTITY INSERT is bad news anywho cuz it requires a level of security i would not give the application user. i know it's kudgy. good luck.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •