Tuesday, July 8, 2008

Stored Procedures parameters and empty nodes.

Situation:
An Xml file contains data and needs to be uploaded to a DB2 database (Or other database).
The xsd tells us that one of the field’s isn’t mandatory.



A stored procedure who accepts some IN parameters.




A mapping that converts the data from the xml file to the stored procedure schema.



Problem:
When you use the mapping above, and the name isn’t available in the Import Xml (minoccures = 0), and phone is available, then the stored procedure will throw an error.
The raison is as in the Import Xml file, the mapping can’t find a Name node, the CustomerName node will not be created. This means in our example that we only send 2 parameters instead of 3, and the stored procedure will not find the CustomerName input parameter/

Solution:

Add to the mapping the ‘Value Mapping’, set the first parameter to true, the second the node you want to copy.
This way, every node in the Xml will be created, even if you send a null value.

1 comment:

Mathias said...

I'm using default values in my stored procedures to avoid sql errors. For example
@pParameter nvarchar(80) = '-'

greetings
Mathias