STRIPING WHITE SPACES

General

STRIPING WHITE SPACES

Hi,

I am using XMLTABLE to parse the XML data and get into table ,I am able to parse the data but getting trailing WHITE SPACES ,To remove WHITE SPACES I have tried oreplace(COL,X'00',''),But when am using oreplace for all column it gives me 9804 Response Row size or Constant Row size overflow though data is too small .Where as if am use it for fewer column it works fine .Same is for oTranslate 

Below is the code , Do suggest how to remove WHITE SPACES 

  SELECT

oreplace(X.CHANNEL,X'00','')

,oreplace(X.METHODX,X'00',''),

oreplace(X.ErrorCodeX,X'00',''),

oreplace(X.ERROR_TYPE,X'00',''),

oreplace(X.ERROR_MESSAGE,X'00',''),

oreplace(X.LST_UPD_UID,X'00',''),

oreplace(X.SOURCE_DATA,X'00',''),

oreplace(X.BRAND_ID,X'00','') ,

oreplace(X.SITE_ID,X'00',''),

X.MEMBER_NUM ,

X.FOLIO_NUM ,

X.ROOM_AMT  ,

X.FOLIO_AMT  ,

X.ARRIVAL_DT,

X.DEPARTURE_DT,

X.EVT_KEY  , 

X.QUALIFYING_REASON_CODE  ,

oreplace(X.TRANSACTION_ID,X'00','') 

FROM(select *  from TBL_XML ) AS C,

XMLTable (

'/ROWS/ROW'

PASSING (new xml(C.dummyxml))

COLUMNS

"CHANNEL" VARCHAR(25) PATH 'Channel',

"error_code" VARCHAR(5)  PATH 'ErrorCode',

"METHODX" VARCHAR(25) PATH 'Method',

"ERROR_TYPE" VARCHAR(5) PATH 'ErrorType',

"ERROR_MESSAGE" VARCHAR(400) PATH 'ErrorMsg',

"LST_UPD_UID" VARCHAR(8)  PATH 'LstUpdUID' ,

"SOURCE_DATA" VARCHAR(30)  PATH 'SourceData',

"BRAND_ID" VARCHAR(2)  PATH 'Brand' ,

"SITE_ID" VARCHAR(8)  PATH 'SiteID',

"MEMBER_NUM"   VARCHAR(10)  PATH 'MemberNum',

"FOLIO_NUM" VARCHAR(25)  PATH 'FolioNum',

"ROOM_AMT" DECIMAL(10,2)  PATH 'RoomAmt',

"FOLIO_AMT" DECIMAL(10,2) PATH 'FolioAmt',

"ARRIVAL_DT" varchar(50) PATH 'ArrivalDate',

"DEPARTURE_DT" varchar(50)  PATH ' DepartureDate',

     "EVT_KEY" BIGINT  PATH 'EvtKey',

     "QUALIFYING_REASON_CODE" VARCHAR(30) PATH 'QualifyReasonCode',

     "TRANSACTION_ID" VARCHAR(30)  PATH 'TransId'

     ) AS X ( "CHANNEL" ,"ErrorCodeX" ,"METHODX" ,

"ERROR_TYPE" ,"ERROR_MESSAGE", "LST_UPD_UID" , "SOURCE_DATA",

"BRAND_ID","SITE_ID" ,"MEMBER_NUM"  ,"FOLIO_NUM", "ROOM_AMT","FOLIO_AMT",

"ARRIVAL_DT" ,"DEPARTURE_DT" , "EVT_KEY","QUALIFYING_REASON_CODE","TRANSACTION_ID"  );

Thanks

Tags (2)
4 REPLIES
Enthusiast

Re: STRIPING WHITE SPACES

If you need to remove only WHITE SPACES, you can use TRIM

Re: STRIPING WHITE SPACES

Hi sachin ,

Thanks for your response  but Trim is not removing X'00' (Hex value of non printable charachter )

Senior Apprentice

Re: STRIPING WHITE SPACES

Hex '00' is not a white space, TRIM by default removes hex '20'.

TRIM(TRAILING '00'xc FROM col)

Re: STRIPING WHITE SPACES


Hi Dieter,

Thanks for your response .I am sorry its is NULL characters that gets appended . When I am extracting data from XML using XMLTABLE ,Its appending X'00'(NUL) for rest of places for example data type of for below is COL VARCHAR(30) in the XML I have string 'SourceData'   after extracting from XML when I do  CHAR2HEXINT(COL) it gives me 536F75726365446174610000000000000000000000000000000000000000 and LENGTH(COL) it give me 30. If I use oreplace(COL,X'00','')   it gives me proper data stripping trailing (X'00')NUL and LENGHT(oreplace(COL,X'00','')) gives 10.Now my problem is when I apply oreplace() function to all column it gives me 9804 Response Row size or Constant Row size overflow though data is too small,Where as if am use it for fewer column it works fine.

My row sixe is too small < 64K