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}')} 

Sample 2:

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

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}')

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">