----------------------------------------------------------- New Message on BDOTNET
----------------------------------------------------------- From: RGKIRAN Message 3 in Discussion hi thank u sir.thanks for ur reply regards gopikiran.r >From: "BDOTNET" <[email protected]> >Reply-To: "BDOTNET" <[email protected]> >To: "BDOTNET" <[email protected]> >Subject: Re: sp_xml_preparedocument and .NET >Date: Fri, 2 Jun 2006 05:27:31 -0700 > > > > > > > > > > > > > New Message on BDOTNET > > > > > > > > sp_xml_preparedocument and .NET > > > > > Reply > > > > > Recommend > > Message 2 in Discussion > > > > > > > > From: > LovedJohnySmith > > > > > Sreejith:- You could use a stored procedure with an >OPENXML statement for the inserts >(see example below). Updates would be trickier through. Use Northwind >GO >-- Create Temporary Tables to simulate the Northwind tables >DROP TABLE #Orders >DROP TABLE #OrderDetails >CREATE TABLE #Orders >( OrderID INTEGER IDENTITY (1, 1) NOT NULL , > CustomerID nCHAR (5) NULL , > EmployeeID INTEGER NULL , > OrderDate DATETIME NULL , > RequiredDate DATETIME NULL , > ShippedDate DATETIME NULL , > ShipVia INTEGER NULL , > Freight MONEY NULL , > ShipName nVARCHAR (40) NULL , > ShipAddress nVARCHAR (60) NULL , > ShipCity nVARCHAR (15) NULL , > ShipRegion nVARCHAR (15) NULL , > ShipPostalCode nVARCHAR (10) NULL , > ShipCountry nVARCHAR (15) NULL ) >CREATE TABLE #OrderDetails >( OrderID INTEGER NOT NULL, > ProductID INTEGER NOT NULL, > UnitPrice MONEY NOT NULL, > Quantity SMALLINT NOT NULL, > Discount REAL NOT NULL ) >GO >DECLARE @iTree INTEGER >DECLARE @xmlOrder VARCHAR(2000) >-- Simulate an order >SET @xmlOrder = ' ><?xml version="1.0"?> ><Order OrderID="1001" CustomerID="ALFKI" EmployeeID="1" >OrderDate="01/01/2001"> > <Items> > <Item ProductID="11" Qty="1" UnitPrice="12.99"> > <Discount>0</Discount> > </Item> > <Item ProductID="17" Qty="2" UnitPrice="4.99"> > <Discount>0.5</Discount> > </Item> > <Item ProductID="21" Qty="1" UnitPrice="11.99"> > <Discount>0</Discount> > </Item> > </Items> ></Order> >' >EXEC sp_xml_preparedocument @iTree OUTPUT, @xmlOrder >--Insert Order Record >SET IDENTITY_INSERT #Orders ON >INSERT #Orders (OrderID, CustomerID, EmployeeID, OrderDate, > RequiredDate, ShippedDate, ShipVia, Freight, > ShipName, ShipAddress, ShipCity, ShipRegion, > ShipPostalCode, ShipCountry) >SELECT OrderID, CustomerID, EmployeeID, OrderDate, > NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL >FROM >OPENXML(@iTree, 'Order', 1) >WITH ( OrderID INTEGER, > EmployeeID INTEGER, > OrderDate DATETIME, > CustomerID nCHAR(5)) >SET IDENTITY_INSERT #Orders OFF >-- Insert Order Details Record >INSERT #OrderDetails >SELECT * FROM >OPENXML(@iTree, 'Order/Items/Item', 1) >WITH ( OrderID INTEGER '../../@OrderID', > ProductID INTEGER, > Qty INTEGER, > UnitPrice MONEY, > Discount REAL 'Discount') >EXEC sp_xml_removedocument @iTree >GO >SELECT * FROM #Orders >SELECT * FROM #OrderDetails >Thanks, Smith http://spaces.msn.com/johnysmith > > > > > > > View other groups in this category. > > > > > > > > > > > > > >Also on MSN: >Start Chatting | Listen to Music | House & Home | Try Online Dating | Daily >Horoscopes > > > > > > > > > > > To stop getting this e-mail, or change how often it >arrives, go to your E-mail Settings. > > > Need help? If you've forgotten your password, > please go >to Passport Member Services. > > For other questions or feedback, go to our > Contact Us >page. > > > If you do not want to receive future e-mail > from this >MSN group, or if you received this message by mistake, please click the >"Remove" link below. On the pre-addressed e-mail message that opens, simply >click "Send". Your e-mail address will be deleted from this group's mailing >list. > > > Remove my e-mail address from BDOTNET. > > > > > > > _________________________________________________________________ Fall in Love... Get married! Join FREE! http://www.shaadi.com/ptnr.php?ptnr=msnhottag ----------------------------------------------------------- To stop getting this e-mail, or change how often it arrives, go to your E-mail Settings. http://groups.msn.com/bdotnet/_emailsettings.msnw Need help? If you've forgotten your password, please go to Passport Member Services. http://groups.msn.com/_passportredir.msnw?ppmprop=help For other questions or feedback, go to our Contact Us page. http://groups.msn.com/contact If you do not want to receive future e-mail from this MSN group, or if you received this message by mistake, please click the "Remove" link below. On the pre-addressed e-mail message that opens, simply click "Send". Your e-mail address will be deleted from this group's mailing list. mailto:[EMAIL PROTECTED]
