Difference between Dataadapter.update and openxml

Hot Topic

Web Synchronisation Using RMO
Need Advise on SQL Express with Advanced Services
SP Permissions
SSIS Tutorial Lesson 1: Debug Error
Cannot reach SQL 2005 E_Learning
Transaction-Log Reader Subsystem errors on: The process could not execute 'sp_replcmds'
Problem creating diagrams
Reporting Services vs Crystal Report
Creating Unique Indexes on Partition table..
SQL Memo datatype

SQL Server

SQL Server 2005 CTP Release Candidate Sept - unexpected failure during setup wizard
Microsoft Data Link Error
Ending a conversation in a CLR activated stored procedure
Database on webserver accessible through excel
Problem conecting to the same DB from two applications
Excel Source: not to import blank rows
Management Studio Express CTP update now available
CREATE LOGIN problem
Newbie questions with mySQL
SQL memo datatype (SECOND TIME)

Difference between Dataadapter.update and openxml

what is the scenario proccessing in the bulk updation operation

dataadapter.update or by using open xml

which one is best

please guide me




Answer this question

Difference between Dataadapter.update and openxml

  • DOTNETFantasy

    If you tell me more about your scenario, I can try to give you a better recommendation.

    If you are trying to get Xml into the SQL Server there are a few options:

    1) openxml/xquery nodes() method. This has the most flexibility of the options. You can use xpath or xquery to specify the transformation from xml to relational. This helps you expose the XML as a table, and then you can iterate over it to get it into your existing tables. This is best suited for inserting moderate sized xml messages into the database.

    2) dataset. This option has the least flexibility if you are trying to map from an existing xml structure to an existing relational structure, but it also requires the least configuration. In addition it can support applying updates as well as inserts using the dataset diffgram xml format.

    3) SQLXML Bulkload: This requires that you author an XSD schema and annotated it with mappings to your database. It is optimized for streaming nested xml files in the GB range.

    4) SQL Server Integration Services: SISS also supports XML sources in its transformation pipeline. This is optimized for large data transformation operations that require a number of data transformations along the way.

    If you are already using the dataset then going this route is probably going to be the easiest. If you need lots of flexibility in your mapping, and your xml files are moderately sized, then nodes/openxml might be the way to go. If you are planning on uploading multi-GB files, then you should look into Bulkload or SSIS.



  • Suman_g

    I have the scenario of selecting values from more than one tables, also while updating the values to DB from Datagrid some changes may or may not be made in grod

    Then check the values alreay existing table to the grid values now i had written in OPENXML inside a Stored procedure,the SP have if statements, INSERT,UPDATE for one table or more than one table

    which one is best openxml or the adapter.update is best,

    Also how can achieve more than one t-sql operation in adapter.update

    the SP is below

    /*

    To UPDATE And INSERT the Asset Values and Asset Status, and to INSERT Asset Change History

    For the Assets which are Value and Status Changed

    */

    Create procedure AssetValueStatusAdjustment_InsertUpdate

    @XMLString text
    ,@LastModifiedUserID varchar(10)
    ,@LastModifiedDate varchar(10)
    ,@CultureID varchar(5)
    As
    BEGIN
    SET NOCOUNT ON
    Declare @InventoryHistoryID bigint
    Declare @handle int
    Declare @XMLStatusName nvarchar(50)
    Declare @TableStatusName nvarchar(50)
    Declare @Error int

    EXEC sp_xml_preparedocument @handle OUTPUT, @XMLString

    -- This is For INSERT the Details of the Value Adjustment Assets into the Table
    -- Inventory_ValueAdjustment

    INSERT INTO Inventory_ValueAdjustment
    (InventoryID
    ,PostDate
    ,PreAdjustmentOriginalCost
    ,PreAdjustmentNetValue
    ,AdjustmentAmount
    ,Comment
    ,GLTemplateID
    ,LastModifiedUserID
    ,LastModifiedDate)

    SELECT
    XMLAdjustmentColumns.InventoryID
    ,XMLAdjustmentColumns.PostDate
    ,XMLAdjustmentColumns.PreAdjustmentOriginalCost
    ,XMLAdjustmentColumns.PreAdjustmentNetValue
    ,XMLAdjustmentColumns.AdjustmentAmount
    ,XMLAdjustmentColumns.Comment
    ,XMLAdjustmentColumns.GLTemplateID
    ,@LastModifiedUserID as LastModifiedUserID
    ,@LastModifiedDate as LastModifiedDate


    From OPENXML(@handle,'Root/Asset',1)

    --This Fields From Inventory_ValueAdjustment Table

    With(InventoryID bigint,
    PostDate datetime,
    PreAdjustmentOriginalCost decimal(18,2),
    PreAdjustmentNetValue decimal(18,2),
    AdjustmentAmount decimal(18,2),
    Comment nvarchar(200),
    GLTemplateID int)XMLAdjustmentColumns

    SET @Error=@@Error
    IF(@Error<>0)
    GOTO ErrorCatch


    -- UPDATE the ValueAdjustment to Inventory_Profile

    UPDATE
    Inventory_Profile
    SET
    NetValue=XMLAdjustmentColumns.NetValue
    ,LastModifiedUserID= @LastModifiedUserID
    ,LastModifiedDate=@LastModifiedDate


    From OPENXML(@handle,'Root/Asset',1)

    --This Fields From Inventory_ValueAdjustment Table

    With(InventoryID bigint,
    NetValue decimal(19,2))XMLAdjustmentColumns

    WHERE
    Inventory_Profile.InventoryID=XMLAdjustmentColumns.InventoryID


    SET @Error=@@Error
    IF(@Error<>0)
    GOTO ErrorCatch


    --INSERT a ValueAdjustment Change History Record

    INSERT INTO Inventory_History
    (InventoryHistoryID
    ,InventoryID
    ,AsOfDate--
    ,ANum--
    ,LNum--
    ,HisoryGenerationReasonID
    ,Alias
    ,InventoryStatusID
    ,InventorySubStatusID
    ,InventorySubTypeID
    ,InventoryTypeID
    ,AcquisitionDate
    ,OriginalCost
    ,NetValue
    ,UnitNumber
    ,YearOfManufacture
    ,Manufacturer
    ,Make
    ,Model
    ,SerialNumber
    ,FundingSourceID--
    ,OwnerSinceDate--
    ,UsageConditionID
    ,Description1
    ,Description2
    ,InventoryLocationID
    ,LocationEffectiveFromDate
    ,IsFlaggedForSale
    ,IsDepreciationAllowed--
    ,PortfolioID
    ,RentalPurchaseOrderNumber
    ,AquisitionPurchaseOrderNumber
    ,CostCenterID
    ,OwnerTypeID
    ,IsReplacementUnitOnOrder
    ,CustomerBookValue
    ,GLTemplateID
    ,FlaggedForCofAAlias
    ,FlaggedForLeaseAlias
    ,CustomerID
    ,PreviousLeaseID
    ,SortOrder
    ,IsSaleLeaseBack
    ,ParentInventoryID
    ,Quantity
    ,OldSerialNumber
    ,InventoryGroupID
    ,InvoiceGroupID
    ,FactorCategoryID
    ,IsEligibleForPropertyTaxManagement
    ,InterimRentReceivableUpfrontTaxModeID
    ,LeaseRentalReceivableUpfrontTaxModeID
    ,OverTermReceivableUpfrontTaxModeID
    ,LastModifiedUserID
    ,LastModifiedDate)

    SELECT
    XMLAdjustmentColumns.InventoryHistoryID as InventoryHistoryID
    ,Inventory_Profile.InventoryID
    ,XMLAdjustmentColumns.AsOfDate as AsOfDate
    ,XMLAdjustmentColumns.ANum as ANum
    ,XMLAdjustmentColumns.LNum as LNum
    ,XMLAdjustmentColumns.HisoryGenerationReasonID as HisoryGenerationReasonID
    ,Inventory_Profile.Alias
    ,Inventory_Profile.InventoryStatusID
    ,Inventory_Profile.InventorySubStatusID
    ,Inventory_Profile.InventorySubTypeID
    ,Inventory_Profile.InventoryTypeID
    ,Inventory_Profile.AcquisitionDate
    ,Inventory_Profile.OriginalCost
    ,Inventory_Profile.NetValue
    ,Inventory_Profile.UnitNumber
    ,Inventory_Profile.YearOfManufacture
    ,Inventory_Profile.Manufacturer
    ,Inventory_Profile.Make
    ,Inventory_Profile.Model
    ,Inventory_Profile.SerialNumber
    ,XMLAdjustmentColumns.FundingSourceID as FundingSourceID
    ,XMLAdjustmentColumns.OwnerSinceDate as OwnerSinceDate
    ,Inventory_Profile.UsageConditionID
    ,Inventory_Profile.Description1
    ,Inventory_Profile.Description2
    ,Inventory_Profile.InventoryLocationID
    ,Inventory_Profile.LocationEffectiveFromDate
    ,Inventory_Profile.IsFlaggedForSale
    ,XMLAdjustmentColumns.IsDepreciationAllowed as IsDepreciationAllowed
    ,Inventory_Profile.PortfolioID
    ,Inventory_Profile.RentalPurchaseOrderNumber
    ,Inventory_Profile.AquisitionPurchaseOrderNumber
    ,Inventory_Profile.CostCenterID
    ,Inventory_Profile.OwnerTypeID
    ,Inventory_Profile.IsReplacementUnitOnOrder
    ,Inventory_Profile.CustomerBookValue
    ,Inventory_Profile.GLTemplateID
    ,Inventory_Profile.FlaggedForCofAAlias
    ,Inventory_Profile.FlaggedForLeaseAlias
    ,Inventory_Profile.CustomerID
    ,Inventory_Profile.PreviousLeaseID
    ,Inventory_Profile.SortOrder
    ,Inventory_Profile.IsSaleLeaseBack
    ,Inventory_Profile.ParentInventoryID
    ,Inventory_Profile.Quantity
    ,Inventory_Profile.OldSerialNumber
    ,Inventory_Profile.InventoryGroupID
    ,Inventory_Profile.InvoiceGroupID
    ,Inventory_Profile.FactorCategoryID
    ,Inventory_Profile.IsEligibleForPropertyTaxManagement
    ,Inventory_Profile.InterimRentReceivableUpfrontTaxModeID
    ,Inventory_Profile.LeaseRentalReceivableUpfrontTaxModeID
    ,Inventory_Profile.OverTermReceivableUpfrontTaxModeID
    ,@LastModifiedUserID as LastModifiedUserID
    ,@LastModifiedDate as LastModifiedDate

    FROM
    Inventory_Profile INNER JOIN OPENXML(@handle,'Root/Asset',1)

    --This Field From Inventory_ValueAdjustment Table

    WITH(InventoryHistoryID bigint,
    InventoryID bigint,
    --This Fields From Inventory_History Table
    AsOfDate datetime,
    ANum nvarchar(50),
    LNum int,
    HisoryGenerationReasonID tinyint,
    FundingSourceID int,
    OwnerSinceDate datetime,
    IsDepreciationAllowed tinyint) XMLAdjustmentColumns

    ON
    Inventory_Profile.InventoryID=XMLAdjustmentColumns.InventoryID


    SET @Error=@@Error
    IF(@Error<>0)
    GOTO ErrorCatch


    -- Check Wheather the Status has been Changed or not
    SELECT
    @XMLStatusName = XMLAdjustmentColumns.InventoryStatusName
    ,@TableStatusName = Inventory_Status_CnfgLocale.InventoryStatusName

    FROM
    Inventory_Status_Cnfg

    INNER JOIN
    Inventory_Status_CnfgLocale ON Inventory_Status_Cnfg.InventoryStatusID=Inventory_Status_CnfgLocale.InventoryStatusID

    INNER JOIN
    OPENXML(@handle,'Root/Asset',1)

    --This Fields From Inventory_Profile Table

    WITH(InventoryStatusName nvarchar(100)
    ,InventoryStatusID tinyint
    ,CultureID varchar(5))XMLAdjustmentColumns

    ON
    Inventory_Status_CnfgLocale.InventoryStatusID=XMLAdjustmentColumns.InventoryStatusID

    AND
    Inventory_Status_CnfgLocale.CultureID=@CultureID

    SET @Error=@@Error
    IF(@Error<>0)
    GOTO ErrorCatch

    -- If New Status is not matched with the existing Status Name then

    IF(@XMLStatusName <> @TableStatusName)

    BEGIN --To Insert Status Change Record

    --UPDATE the New Status Changed Value to Inventory_Profile

    UPDATE
    Inventory_Profile
    SET
    InventoryStatusID = XMLAdjustmentColumns.InventoryStatusId
    ,LastModifiedUserID= @LastModifiedUserID
    ,LastModifiedDate=@LastModifiedDate

    FROM
    OPENXML(@handle,'Root/Asset',1)

    --This Fields From Inventory_Profile Tavle

    WITH(InventoryStatusID tinyint
    ,InventoryID bigint)XMLAdjustmentColumns

    WHERE
    Inventory_Profile.InventoryID=XMLAdjustmentColumns.InventoryID


    SET @Error=@@Error
    IF(@Error<>0)
    GOTO ErrorCatch


    --Select the Maximum available InventoryHistoryID to avoid primary key violation
    SELECT
    @InventoryHistoryID=COUNT(InventoryHistoryID)
    FROM
    Inventory_History

    SET @InventoryHistoryID = @InventoryHistoryID+1

    SET @Error=@@Error
    IF(@Error<>0)
    GOTO ErrorCatch


    -- INSERT a status change history record

    INSERT INTO Inventory_History
    (InventoryHistoryID
    ,InventoryID
    ,AsOfDate--
    ,ANum--
    ,LNum--
    ,HisoryGenerationReasonID
    ,Alias
    ,InventoryStatusID
    ,InventorySubStatusID
    ,InventorySubTypeID
    ,InventoryTypeID
    ,AcquisitionDate
    ,OriginalCost
    ,NetValue
    ,UnitNumber
    ,YearOfManufacture
    ,Manufacturer
    ,Make
    ,Model
    ,SerialNumber
    ,FundingSourceID--
    ,OwnerSinceDate--
    ,UsageConditionID
    ,Description1
    ,Description2
    ,InventoryLocationID
    ,LocationEffectiveFromDate
    ,IsFlaggedForSale
    ,IsDepreciationAllowed--
    ,PortfolioID
    ,RentalPurchaseOrderNumber
    ,AquisitionPurchaseOrderNumber
    ,CostCenterID
    ,OwnerTypeID
    ,IsReplacementUnitOnOrder
    ,CustomerBookValue
    ,GLTemplateID
    ,FlaggedForCofAAlias
    ,FlaggedForLeaseAlias
    ,CustomerID
    ,PreviousLeaseID
    ,SortOrder
    ,IsSaleLeaseBack
    ,ParentInventoryID
    ,Quantity
    ,OldSerialNumber
    ,InventoryGroupID
    ,InvoiceGroupID
    ,FactorCategoryID
    ,IsEligibleForPropertyTaxManagement
    ,InterimRentReceivableUpfrontTaxModeID
    ,LeaseRentalReceivableUpfrontTaxModeID
    ,OverTermReceivableUpfrontTaxModeID
    ,LastModifiedUserID
    ,LastModifiedDate)

    SELECT
    @InventoryHistoryID as InventoryHistoryID
    ,Inventory_Profile.InventoryID
    ,XMLAdjustmentColumns.AsOfDate as AsOfDate
    ,XMLAdjustmentColumns.ANum as ANum
    ,XMLAdjustmentColumns.LNum as LNum
    ,XMLAdjustmentColumns.HisoryGenerationReasonID as HisoryGenerationReasonID
    ,Inventory_Profile.Alias
    ,Inventory_Profile.InventoryStatusID
    ,Inventory_Profile.InventorySubStatusID
    ,Inventory_Profile.InventorySubTypeID
    ,Inventory_Profile.InventoryTypeID
    ,Inventory_Profile.AcquisitionDate
    ,Inventory_Profile.OriginalCost
    ,Inventory_Profile.NetValue
    ,Inventory_Profile.UnitNumber
    ,Inventory_Profile.YearOfManufacture
    ,Inventory_Profile.Manufacturer
    ,Inventory_Profile.Make
    ,Inventory_Profile.Model
    ,Inventory_Profile.SerialNumber
    ,XMLAdjustmentColumns.FundingSourceID as FundingSourceID
    ,XMLAdjustmentColumns.OwnerSinceDate as OwnerSinceDate
    ,Inventory_Profile.UsageConditionID
    ,Inventory_Profile.Description1
    ,Inventory_Profile.Description2
    ,Inventory_Profile.InventoryLocationID
    ,Inventory_Profile.LocationEffectiveFromDate
    ,Inventory_Profile.IsFlaggedForSale
    ,XMLAdjustmentColumns.IsDepreciationAllowed as IsDepreciationAllowed
    ,Inventory_Profile.PortfolioID
    ,Inventory_Profile.RentalPurchaseOrderNumber
    ,Inventory_Profile.AquisitionPurchaseOrderNumber
    ,Inventory_Profile.CostCenterID
    ,Inventory_Profile.OwnerTypeID
    ,Inventory_Profile.IsReplacementUnitOnOrder
    ,Inventory_Profile.CustomerBookValue
    ,Inventory_Profile.GLTemplateID
    ,Inventory_Profile.FlaggedForCofAAlias
    ,Inventory_Profile.FlaggedForLeaseAlias
    ,Inventory_Profile.CustomerID
    ,Inventory_Profile.PreviousLeaseID
    ,Inventory_Profile.SortOrder
    ,Inventory_Profile.IsSaleLeaseBack
    ,Inventory_Profile.ParentInventoryID
    ,Inventory_Profile.Quantity
    ,Inventory_Profile.OldSerialNumber
    ,Inventory_Profile.InventoryGroupID
    ,Inventory_Profile.InvoiceGroupID
    ,Inventory_Profile.FactorCategoryID
    ,Inventory_Profile.IsEligibleForPropertyTaxManagement
    ,Inventory_Profile.InterimRentReceivableUpfrontTaxModeID
    ,Inventory_Profile.LeaseRentalReceivableUpfrontTaxModeID
    ,Inventory_Profile.OverTermReceivableUpfrontTaxModeID
    ,@LastModifiedUserID as LastModifiedUserID
    ,@LastModifiedDate as LastModifiedDate

    FROM
    Inventory_Profile INNER JOIN OPENXML(@handle,'Root/Asset',1)

    --This Field From Inventory_ValueAdjustment Table

    WITH(InventoryHistoryID bigint,
    InventoryID bigint,
    --This Fields From Inventory_History Table

    AsOfDate datetime,
    ANum nvarchar(50),
    LNum int,
    HisoryGenerationReasonID tinyint,
    FundingSourceID int,
    OwnerSinceDate datetime,
    IsDepreciationAllowed tinyint) XMLAdjustmentColumns

    ON
    Inventory_Profile.InventoryID=XMLAdjustmentColumns.InventoryID

    SET @Error=@@Error
    IF(@Error<>0)
    GOTO ErrorCatch


    END

    GOTO NextStatement --dont want to Update the Status GOTO Next expression


    NextStatement:


    ErrorCatch:
    RETURN @Error


    EXEC sp_xml_removedocument @handle
    END
    Go

    /*

    This Stored Procedure Will Insert two tables as Inventory_Profile, Inventory_History and

    Update TaxDepreciation_Profile by OPENXML BULK UPDATE CONCEPT

    -------------
    The @xmlString Parameter is used to get the value as XML Nodes From the Non Transaction

    Class

    ----------

    */

    guideme please



  • Difference between Dataadapter.update and openxml
Answer this question