Hi, I want to do insert and update using single set of xml,xsl and xsd files. I am using Order and Order Details talbles of NorthWind Database. I am successful in inserting the records in Order (Parent table) and Order Details (Child table) using an xsd, xsl and an xml file for data. This is my xsd file. --------------------------------------------------------------------------------------- <?xml version="1.0" encoding="utf-8" ?> <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema" > <xs:element name="Order" type="Order_T" sql:relation="Orders" sql:key-fields="OrderID" /> <xs:annotation> <xs:appinfo> <sql:relationship name="OrderDetails" parent="Orders" parent-key="OrderID" child="[Order Details]" child-key="OrderID" /> </xs:appinfo> </xs:annotation> <xs:complexType name="Order_T"> <xs:sequence> <xs:element name="OrderID" type="xs:string" sql:datatype="nvarchar(4000)" sql:identity="ignore" /> <xs:element name="CustomerID" type="xs:string" minOccurs="0" /> <xs:element name="EmployeeID" type="xs:int" minOccurs="0" /> <xs:element name="OrderDate" type="xs:dateTime" minOccurs="0" /> <xs:element name="RequiredDate" type="xs:dateTime" minOccurs="0" /> <xs:element name="ShippedDate" type="xs:dateTime" minOccurs="0" /> <xs:element name="ShipVia" type="xs:int" minOccurs="0" /> <xs:element name="Freight" type="xs:decimal" minOccurs="0" /> <xs:element name="ShipName" type="xs:string" minOccurs="0" /> <xs:element name="ShipAddress" type="xs:string" minOccurs="0" /> <xs:element name="ShipCity" type="xs:string" minOccurs="0" /> <xs:element name="ShipRegion" type="xs:string" minOccurs="0" /> <xs:element name="ShipPostalCode" type="xs:string" minOccurs="0" /> <xs:element name="ShipCountry" type="xs:string" minOccurs="0" /> <!--<xs:element name="OrderItems" type="OrderItems_T" sql:is-constant="1" minOccurs="0" /> --> <xs:element name="OrderItem" type="OrderItem_T" sql:relation="[Order Details]" sql:relationship="OrderDetails" maxOccurs="unbounded" sql:key-fields="OrderID ProductID"/> </xs:sequence> </xs:complexType> <xs:complexType name="OrderItems_T"> <xs:sequence minOccurs="0" maxOccurs="unbounded"> <xs:element name="OrderItem" minOccurs="0" maxOccurs="unbounded" sql:relation="[Order Details]" sql:relationship="OrderDetails" type="OrderItem_T" /> </xs:sequence> </xs:complexType> <xs:complexType name="OrderItem_T"> <xs:sequence minOccurs="1" maxOccurs="1"> <xs:element name="OrderID" type="xs:string" sql:datatype="nvarchar(4000)" /> <xs:element name="ProductID" type="xs:int" /> <xs:element name="UnitPrice" type="xs:decimal" /> <xs:element name="Quantity" type="xs:int" /> <xs:element name="Discount" type="xs:float" /> </xs:sequence> </xs:complexType> </xs:schema> ------------------------------------------------------------------------------------------ This is XSL file ----------------------------------------------------------------------------------------------- <?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="xml"/> <xsl:variable name="idPrefix">x</xsl:variable> <xsl:template match="/"> <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram"> <updg:sync> <updg:before> </updg:before> <updg:after> <!-- <xsl:attribute name="updg:returnid"></xsl:attribute> --> <xsl:apply-templates /> </updg:after> </updg:sync> </ROOT> </xsl:template> <xsl:template match="Order"> <xsl:variable name="orderId"> <xsl:value-of select="concat($idPrefix, position())" /> </xsl:variable> <Order xmlns:updg="urn:schemas-microsoft-com:xml-updategram"> <xsl:attribute name="updg:at-identity"> <xsl:value-of select="$orderId" /> </xsl:attribute> <OrderID><xsl:value-of select="$orderId" /></OrderID> <xsl:copy-of select="*" /> </Order> </xsl:template> </xsl:stylesheet><!-- Stylus Studio meta-information - (c)1998-2002 eXcelon Corp. <metaInformation> <scenarios ><scenario default="yes" name="OrderUpdateGram" userelativepaths="yes" externalpreview="no" url="Order.xml" htmlbaseurl="" processortype="msxmldotnet" commandline="" additionalpath="" additionalclasspath="" postprocessortype="none" postprocesscommandline="" postprocessadditionalpath="" postprocessgeneratedext=""/></scenarios><MapperInfo srcSchemaPath="" srcSchemaRoot="" srcSchemaPathIsRelative="yes" srcSchemaInterpretAsXML="no" destSchemaPath="" destSchemaRoot="" destSchemaPathIsRelative="yes" destSchemaInterpretAsXML="no"/> </metaInformation> --> ----------------------------------------------------------------------------------------------- and, This one is xml file that contains Data for insert ----------------------------------------------------------------------------------------------- <Root> <Order> <CustomerID>ALFKI</CustomerID> <EmployeeID>1</EmployeeID> <OrderDate>2006-03-14T11:40:57</OrderDate> <RequiredDate>2006-03-15T11:40:57</RequiredDate> <ShippedDate>2006-03-16T11:40:57</ShippedDate> <ShipVia>1</ShipVia> <Freight>100.50</Freight> <ShipName>B1</ShipName> <ShipAddress>One Microsoft Way</ShipAddress> <ShipCity>Redmond</ShipCity> <ShipRegion>WA</ShipRegion> <ShipPostalCode>12345</ShipPostalCode> <ShipCountry>India</ShipCountry> <OrderItem> <ProductID>25</ProductID> <UnitPrice>99</UnitPrice> <Quantity>99</Quantity> <Discount>1</Discount> </OrderItem> <OrderItem> <ProductID>29</ProductID> <UnitPrice>123.79</UnitPrice> <Quantity>100</Quantity> <Discount>1</Discount> </OrderItem> <OrderItem> <ProductID>64</ProductID> <UnitPrice>33.25</UnitPrice> <Quantity>300</Quantity> <Discount>1</Discount> </OrderItem> </Order> </Root> ----------------------------------------------------------------------------------------------- Here the Identity value is created by sql server. so we are not passing the OrderID. This is working fine for Insert. now, I want to Modify the same set of files so that I can do both Insert and UPDATE using same set of files. Any Intelligent mind over there ? Plase help. Thanks, GopalKrishna
