XML Convertion

Database
Teradata Employee

XML Convertion

Hi

 

Any one who can help in converting below SQL server code to compatible Teradata code. This is way beyond my XML knowledge. I do not know how to proceed on this.

 

update dsf
            set 
                dsf.TrailerRowDate  = TrailerRow.TrailerRowDate,
                dsf.TrailerRowSales = TrailerRow.TrailerRowSales
        from 
            Log.DailySalesReportFiles dsf
        inner join 
            (
            select
               FileId,
               t.value('.', 'varchar(max)') as FileContentRow,
               case isdate(dbo.FromProgressArray(t.value('.', 'varchar(50)'), ',', 1))
                 when 0 then null
                 else convert(date, dbo.FromProgressArray(t.value('.', 'varchar(50)'), ',', 1), 104)
               end as TrailerRowDate,
               case isnumeric(dbo.FromProgressArray(t.value('.', 'varchar(50)'), ',', 3))
                   when 0 then null
                   else convert(money, dbo.FromProgressArray(t.value('.', 'varchar(50)'), ',', 3)) 
               end as TrailerRowSales
            from
               Log.DailySalesReportFiles t1
            cross apply                (
                select 
                    cast ('<root><r>' + replace(cast(FileContent as varchar(max)), char(10),'</r><r>') + '</r></root>' as xml)
                from 
                    Log.DailySalesReportFiles t2 
                where 
                    t2.HashstampedFileName = t1.HashstampedFileName                ) as t(x)
            cross apply
                x.nodes('//root/r') as a(t)
            where
                /* get only trailer row: DeptNo equals 0 indicates trailer row */
                convert(int, dbo.FromProgressArray(t.value('.','varchar(max)'), ',', 2)) = 0 
            ) as TrailerRow
        on  dsf.FileId = TrailerRow.FileId
        where
            dsf.TrailerRowDate is null
        or  dsf.TrailerRowSales is null

 

Someone, Please help me in converting the code to Teradata compatible code.

 

Thanks

Akshat

1 REPLY
Teradata Employee

Re: XML Convertion

Take a look at the SQL Functions, Operators, ... manual at info.teradata.com.  Teradata does not have a generic convert function like SQL Server.  Read the chapters on "Data Type Conversions" and "Data Type Conversion Functions."  Specifically, you will need to use CAST() and/or To_Date() and To_Number().  You might have to think of a different way to validate the data - most of these functions return errors if a value cannot be converted; the Regexp_* functions might help with this.