> -----Original Message-----
> Subject: [IB-Conversions] Convert ms-sql to firebird openxml problem
>
> Hi,
>
> After a long search on google I stil have no clue how to convert this code.
>
> [code]
> ALTER PROCEDURE [dbo].[PhonePerson_TR]
> (
> @PersoonNr int
> , @XmlPhone nvarchar(2000) = ''
>
> , @user nvarchar(50)
> , @returnValue int output
> )
>
> AS
> BEGIN
> if len(@XmlPhone) > 0
> BEGIN
> BEGIN TRY
>
> /* ********** ********** BOF process xml ********** ********** */
>
> -- Create a temp table to store xml info DECLARE @TempTable table (
> PersoonNr int default -1 , TelefoonNr int default -1 , TelTypeNr smallint ,
> LandNr smallint , NetNummer nvarchar(10) default -1 , AbonneeNr
> nvarchar(80) default -1 , used int default -1
> )
>
> DECLARE @XMLDoc INT
> EXEC sp_xml_preparedocument @XMLDoc OUTPUT, @XmlPhone;
>
> -- insert the xml in the temp table
> INSERT INTO @TempTable (TelefoonNr, TelTypeNr, LandNr, NetNummer,
> AbonneeNr)
> SELECT TelefoonNr, TelTypeNr, LandNr, NetNummer, AbonneeNr FROM
> OPENXML(@XMLDoc, '/DocumentElement/Phone',2) WITH ( TelefoonNr INT
> , TelTypeNR INT , LandNr NVARCHAR(5) , Netnummer nvarchar(10) ,
> AbonneeNr nvarchar(80) );
>
> -- Update the columns that are not used yet.
> update @TempTable
> set LandNr = null
> , Netnummer = null;
>
> /* ********** ********** EOF process xml ********** ********** */
> -- more code using transactions
> [/code]
>
> Can you tel me how to get started, I have not much knowledge of fb yet.
>
> any help is welcome
>
> Edwin

Hi Edwin,

You might want to try some of the following to get you started:

- parameters that have defaults : all params following the first param that has a default need to have a default value as well
- don't mix DML and DDL (you can if you use dynamic sql, but it's not advised and bad practice).
- create the temp tables as part of your metadata scripts outside of the stored procedure
- temp tables are explained quite well in the firebird help (http://www.firebirdsql.org/refdocs/langrefupd21-ddl-table.html)

Good luck!

Paul.