The SQL Add Generated Items requires the stored proc to return a schema. The XMLDATA instruction will return an XDR schema which the adapter converts inyo an XSD schema. You only need the XMLDATA instruction when using Add Generated Items you can remove it once the schema has been created
You may have a problem using FOR XML EXPLICIT, XMLDATA. From memory I don’t think this works that well
However you can create a schema without using the Add Generated Items.
Your messages will look something like this:
<ns0:SQLRequest xmlns:ns0=”urn:your:namespace”>
<SP_888XML_NEWPARTNUMBER>
</ns0:SQLRequest>
<ns0:SQLResponse xmlns:ns0=”urn:your:namespace”>
<Message>
<Head>
<MessageID/>
<Date/>
<MessageType/>
</Head>
<Sender>
<SystemID>
<CompanyID>
<ReplyToQ>
</Sender>
<Recipient>
<SystemID>
<CompanyID>
<ReplyToQ>
</Recipient>
<Body>
<ItemMaster>
<ItemMasterHeader>
<Whse>
<WhseCode>
<PickDate>
</ItemMasterHeader>
</ItemMaster>
<Items>
<ItemDetail>
<UPC_Code>
<ProductID>
<Weight>
<Height>
<Width>
<Length>
<Sku>
<Description>
<ItemType>
<ClassCode>
</ItemDetail>
</Items>
</Body>
<Message>
</ns0:SQLResponse>
You could generate the schema manually, its not that complicated
An easier way to generate the schema is to run the stored proc and use the resultant Xml to generate the schema, then add the root nodes and namespace that the SQL adapter needs. The root node name and namespace can be anything you choose. On Send the SQL adapter ignores the root node and you specify the response root node and name space on the Send Port.
You may need to adjust the data types to match the SQL data types