/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
CREATE PROCEDURE InsertHoldingFromXML(
    @sXML NTEXT
)
AS
begin transaction
    DECLARE @iDoc INT
    EXEC sp_xml_preparedocument @iDoc OUTPUT, @sXML
                       
INSERT INTO dbo.HOLDING (PURCHASEPRICE,HOLDINGID,QUANTITY,PURCHASEDATE,ACCOUNT_ACCOUNTID,QUOTE_SYMBOL)
 
 SELECT PURCHASEPRICE,HOLDINGID,QUANTITY,PURCHASEDATE,ACCOUNT_ACCOUNTID,QUOTE_SYMBOL
    FROM OPENXML(@iDoc,'/Holding/dbo.Holding',2)
    WITH (	PURCHASEPRICE decimal(12,2) 'PURCHASEPRICE', 
			HOLDINGID int 'HOLDINGID', 
			QUANTITY float 'QUANTITY', 
			PURCHASEDATE DateTime 'PURCHASEDATE',
			ACCOUNT_ACCOUNTID int 'ACCOUNT_ACCOUNTID',
			QUOTE_SYMBOL varchar(250) 'QUOTE_SYMBOL');
	
	DBCC CHECKIDENT ( 'HOLDING', RESEED, 100000000 );  
	commit;
    EXEC sp_xml_removedocument @iDoc;
RETURN