/*
 * 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 InsertOrdersFromXML(
    @sXML NTEXT
)
AS
begin transaction
    DECLARE @iDoc INT
    EXEC sp_xml_preparedocument @iDoc OUTPUT, @sXML
                       
INSERT INTO dbo.ORDERS (ORDERFEE,COMPLETIONDATE,ORDERTYPE,ORDERSTATUS,PRICE,QUANTITY,OPENDATE,ORDERID,ACCOUNT_ACCOUNTID,QUOTE_SYMBOL,HOLDING_HOLDINGID)
 
 SELECT ORDERFEE,COMPLETIONDATE,ORDERTYPE,ORDERSTATUS,PRICE,QUANTITY,OPENDATE,ORDERID,ACCOUNT_ACCOUNTID,QUOTE_SYMBOL,HOLDING_HOLDINGID
    FROM OPENXML(@iDoc,'/Orders/dbo.Orders',2)
    WITH (	ORDERFEE decimal(12,2) 'ORDERFEE', 
			COMPLETIONDATE DateTime 'COMPLETIONDATE', 
			ORDERTYPE varchar(250) 'ORDERTYPE', 
			ORDERSTATUS varchar(250) 'ORDERSTATUS',
			PRICE decimal(12,2) 'PRICE',
			QUANTITY float 'QUANTITY',
			OPENDATE DateTime 'OPENDATE',
			ORDERID int 'ORDERID',
			ACCOUNT_ACCOUNTID int 'ACCOUNT_ACCOUNTID',
			QUOTE_SYMBOL varchar(250) 'QUOTE_SYMBOL',
			HOLDING_HOLDINGID int 'HOLDING_HOLDINGID');
	
	DBCC CHECKIDENT ( 'ORDERS', RESEED, 100000000 );  
	commit;
    EXEC sp_xml_removedocument @iDoc;
RETURN