Re: Using stored procedure properly

Hello there,

1. You shouldn't put the stored procedure name for Request Root Element. When you are generating the schema using the wizard, the first screen asks for NameSpace and Request Root element name. You can define whatever you want here(Example:Root Element = MySqlSchemaRoot, Target Namespace = http://www.mycompany.com/SQL/Schema), its not related to any of your database object (like stored procedure). As you mentioned, in the next screen you'll choose the stored procedure.

After generating the schema compiling and deploying it, when you are configuring the receive location. In the dialogue box you will provide

Document Root Element Name: MySqlSchemaRoot <<Name you provided while generating the schema>>
Document Target Namespace: http://www.mycompany.com/SQL/Schema <<Namespace you provided while generating the schema>>
SQL Command: exec [GetLoadTestBrowsers] <<Your stored procedure name>>.

2. The document root element name you define (from previous step) will eventually become the MessageType, Alan was trying to explain to you, you normally don't regenerate the schema (XSD) second time using the wizard, instead you'll handcraft it. Say for example inside your stored procedure if you have added additional column for your "SELECT" statement, you will then open the SQLService.xsd( or whatever name you had given) and manually add the column as ELEMENT/Attribute with appropriate datatype.

Hope it cleared your doubts now. visit this link for a walk through
http://msdn2.microsoft.com/en-us/library/aa560708.aspx

NOTE the importance of "xmldata" parameter in the SQL query, you must remove it later (applies only if you are using SQL 2005).

Regards,
Saravana Kumar
http://www.biztalk247.com/v1/
http://www.digitaldeposit.net/blog

"golddog" <golddog@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:7510A8FE-92F9-46BB-B531-3F817FC6B55F@xxxxxxxxxxxxxxxx
Thanks for your replies. Pardon me for asking what might be too simple of
follow-up questions, but I'm pretty new to all these technologies, and want
to make sure I really understand.

1) You're saying that when I add the generated item for the stored
procedure, and it asks me for the request root element name, I should put in
the stored procedure name? If so, any insight into why they bother asking
you for that root, since we're going to select the procedure in the next step
or two.

2) I see what you mean about SP changes, how it would be easier to edit the
xsd. But let's say we re-generate the SP xsd; after deleting the new
orchestration, it's the message type when you configure the (send) port that
determines which xsd to use, right? [Although I think what you're saying is
to move the newly-generated xsd to the old one's name (i.e., SQLService_1.xsd
to SQLService.xsd) and the port won't have any choice about which one to
read, correct?]

Thanks a million, Alan.

.

Relevant Pages

  • RE: schema namespace question
    ... When a document comes in we use the root element name and the ... namespace to identify what schema it goes to. ... | Content-Type: text/plain; ...
    (microsoft.public.biztalk.general)
  • RE: Update/Insert into SQL
    ... Just write an SQL Stored Procedure to handle the problem. ... You can then map your data to this schema. ... The Request root element name will wrap the stored ...
    (microsoft.public.biztalk.general)
  • Re: Question about validating schema with xml file
    ... Schema Error: System.Xml.Schema.XmlSchemaException: Expected schema ... Make sure the root element is and the namespace is 'http:// ... That is what I told you, the schemaLocation is a list of pairs of the namespace URI and the schema URL. ...
    (comp.text.xml)
  • Re: Can be multiple instances of element used as the root element?
    ... so elements is used not only as a root element but inside the ... have a DTD or a schema against which to validate and your example does not show any schema. ...
    (comp.text.xml)
  • Re: SQL Send Adapter
    ... I am trying to use this suggestion without success so far. ... Set the field for the XML part of the SQL insert message to distinguished. ... I am clear on how to create the schema and the ports; ... on how to pass the input XML message to the stored procedure. ...
    (microsoft.public.biztalk.general)