June 5, 2012 at 4:22 am
I am new to SQL Server and struggling more than I was hoping to.
My problem. We have to fetch values from an xml field. Data for up to 10000 items can be stored in each XML field, and we have to create a seperate record for each new item.
We make use of Xquery that is nested inside a loop to achieve this, but the whole process is extremely slow. Some assistance with tweaking the Stored procedure, or some alternative solution will be appreciated.
Herewith the stored procedure in question:
ALTER PROCEDURE [dbo].[sp_S2_DECLARATION_ITEMS] as
DECLARE @MaxDeclCreatedtimestamp varchar(100)
DECLARE @VMaxCounter varchar(6)
DECLARE @MaxCounter int
DECLARE @iCounter int
SET @MaxDeclCreatedtimestamp = (select MaxDeclCreatedtimestamp from BISTG.DeltaExtractionHistory)
SET @VMaxCounter = (select max(substring(GoodsItemQuantity, 1,charindex('.', GoodsItemQuantity) - 1)) from BISTG.S2_DECLARATIONS)
SET @MaxCounter = cast(@VMaxCounter as int)
SET @iCounter = 1
WHILE (@iCounter <= @MaxCounter)
BEGIN
INSERT INTO BISTG.S2_DECLARATION_ITEMS
SELECT
CONVERSATIONTOKEN,
MESSAGEID,
CREATEDTIMESTAMP,
DECLXML.value('(/GoodsDeclaration/ID)[1]', 'varchar(100)') AS LRN,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/ClassificationList/Classification/ID)[1]', 'varchar(12)') AS COMMODITYID,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/Origin/CountryCode)[1]','char(2)') AS OriginCountryCode,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/SequenceNumeric)[1]','varchar(12)') AS SequenceNumeric,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/TypeCode)[1]','varchar(12)') AS Dutytype1,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/PaymentList/Payment/TaxAssessedAmount)[1]','varchar(12)') AS Dutyamount1,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/TypeCode)[2]','varchar(12)') AS Dutytype2,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/PaymentList/Payment/TaxAssessedAmount)[2]','varchar(12)') AS Dutyamount2,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/TypeCode)[3]','varchar(12)') AS Dutytype3,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/PaymentList/Payment/TaxAssessedAmount)[3]','varchar(12)') AS Dutyamount3,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/TypeCode)[4]','varchar(12)') AS Dutytype4,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/PaymentList/Payment/TaxAssessedAmount)[4]','varchar(12)') AS Dutyamount4,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/TypeCode)[5]','varchar(12)') AS Dutytype5,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/PaymentList/Payment/TaxAssessedAmount)[5]','varchar(12)') AS Dutyamount5,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/TypeCode)[6]','varchar(12)') AS Dutytype6,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/PaymentList/Payment/TaxAssessedAmount)[6]','varchar(12)') AS Dutyamount6,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/TypeCode)[7]','varchar(12)') AS Dutytype7,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/PaymentList/Payment/TaxAssessedAmount)[7]','varchar(12)') AS Dutyamount7,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/TypeCode)[8]','varchar(12)') AS Dutytype8,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/PaymentList/Payment/TaxAssessedAmount)[8]','varchar(12)') AS Dutyamount8,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/TypeCode)[9]','varchar(12)') AS Dutytype9,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/PaymentList/Payment/TaxAssessedAmount)[9]','varchar(12)') AS Dutyamount9,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/TypeCode)[10]','varchar(12)') AS Dutytype10,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/PaymentList/Payment/TaxAssessedAmount)[10]','varchar(12)') AS Dutyamount10,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/TypeCode)[11]','varchar(12)') AS Dutytype11,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/PaymentList/Payment/TaxAssessedAmount)[11]','varchar(12)') AS Dutyamount11,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/TypeCode)[12]','varchar(12)') AS Dutytype12,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/PaymentList/Payment/TaxAssessedAmount)[12]','varchar(12)') AS Dutyamount12,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/TypeCode)[13]','varchar(12)') AS Dutytype13,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/PaymentList/Payment/TaxAssessedAmount)[13]','varchar(12)') AS Dutyamount13,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/TypeCode)[14]','varchar(12)') AS Dutytype14,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/PaymentList/Payment/TaxAssessedAmount)[14]','varchar(12)') AS Dutyamount14,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/TypeCode)[15]','varchar(12)') AS Dutytype15,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CommodityList/Commodity/DutyTaxFeeList/DutyTaxFee/PaymentList/Payment/TaxAssessedAmount)[15]','varchar(12)') AS Dutyamount15,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/CustomsValueAmount)[1]','varchar(12)') AS CustomsValueAmount,
DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/StatisticalValueAmount)[1]','varchar(12)') AS StatisticalValueAmount
FROM BISTG.S1_DECLARATIONS DECITEM
WHERE DECITEM.CREATEDTIMESTAMP >= @MaxDeclCreatedtimestamp AND DECLXML.value('(/GoodsDeclaration/GoodsShipmentList/GoodsShipment/GovernmentAgencyGoodsItemList/GovernmentAgencyGoodsItem[sql:variable(''@iCounter'')]/SequenceNumeric)[1]','varchar(12)')IS NOT NULL;
SET @iCounter = @iCounter + 1
END
June 5, 2012 at 7:37 am
Thanks for the assistance. I will play around and give you feedback on the results.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply