what is the scenario proccessing in the bulk updation operation
dataadapter.update or by using open xml
which one is best
please guide me
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.
|||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
