How can I use a stored procedure with the XMLTODATABASE application?

To use a stored procedure in XmlToDatabase, follow this example which uses the PROCEDURE command:

• This feature is only available with the .NET 2.0 version of the application Advantys.Worflow.Applications.XmlToDatabase. 
•  You simply have to declare in the transaction file the type of the command: type="PROCEDURE"

SQL Server sample stored procedure calls:

Sample 1:

<command loop="yes" type="PROCEDURE" xpath="/NewDataSet/UserNames/">
{CALL ap_InsertUsers(
'{XPATH::/NewDataSet/Table1/REQUEST_NUMBER}',
'{XPATH:UserNames}')}
</command>

Sample 2:

<command loop=”yes” type=”PROCEDURE” xpath=”/NewDataSet/UserNames/”>
ap_InsertUsers
‘{XPATH::/NewDataSet/Table1/REQUEST_NUMBER}’,
‘{XPATH:UserNames}’
</command>

Note the subtle difference in syntax between Sample 1 and Sample 2. When using the "System.Data.OleDb” provider within your connection string, both Sample 1 and Sample 2 are supported. However, when using the “System.Data.SqlClient” provider, only Sample 2 is supported.

Oracle sample stored procedure call:

<command loop=”yes” type=”PROCEDURE” xpath=”/NewDataSet/UserNames/”>

CALL ap_InsertUsers ('{XPATH::/NewDataSet/Table1/REQUEST_NUMBER}','{XPATH:UserNames}')

</command>

Sample database nodes and connection strings:

<database name="xxxx" connectionstring="Data Source=xxxx;Initial Catalog=xxxx;User ID=xxxx;Password=xxx;" provider="System.Data.OleDb">

<database name="xxxx" connectionstring="Data Source=xxxx;Initial Catalog=xxx;User ID=xxxx;Password=xxxx;" provider="System.Data.SqlClient">

<database name="xxxx" connectionstring="Data Source=xxxx;User ID=xxxx;Password=xxxx;" provider="System.Data.OracleClient">