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

Development Workflow Applications
1

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 XMLTODATABASE application.

  • You simply have to declare the type of the command in the transaction file: 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">