spBulkUpdate_MenuItems

CREATE procedure [dbo].[spBulkUpdate_MenuItems]
(
@ContentId int,
@XML ntext
)
as

declare @idoc int

exec sp_xml_preparedocument @idoc output, @XML

begin transaction

    delete 
    from menuitems
    where ContentID=@ContentID 

    insert into MenuItems
    (
        [id],
        ContentID,
        ParentID,
        Caption,
        Tooltip,
        SitePageID,
        PageTabID,
        URL,
        Target,
        RightImgPad,
        TreeIndex,
        LinkType,
        IsActive,
        PageTitle,
        CssClass
    )
    select Id, @ContentID, ParentID, Caption, Tooltip, SitePageID, PageTabID, URL, Target, RightImgPad, Treeindex, LinkType, IsActive, PageTitle, CssClass
    from openxml (@idoc, '/ArrayOfResultRow/ResultRow',3) 
    with
    (
      Id int,
      ParentId int,
      ContentID int,
      Caption nvarchar(256), 
      Tooltip nvarchar(256), 
      SitePageID int,
      PageTabID int,
      URL nvarchar(2048), 
      Target nvarchar(256),
      RightImgPad int,
      TreeIndex nvarchar(256),
      LinkType tinyint,
      IsActive bit,
      PageTitle nvarchar(256),
      CssClass nvarchar(100)
    ) 


    exec sp_xml_removedocument @idoc

    /* need to convert 0 parent ids to nulls so hierarchy dataset works correctly back in menu.vb code */
    update menuitems set parentID=null where parentID=0 

commit transaction