OPENXML strips leading 0 from char type

  • Hi,

    I have a problem with OPENXML - loading data into a table, a character field that sometimes contains leading 0s loses the 0s!

    The XML is passed as a parameter to the stored proc and code is:

    EXEC sp_xml_preparedocument @hdoc OUTPUT, @bstrXMLStream

    INSERT INTO @tCat

    SELECT *

    FROM OPENXML(@hDoc,'/ROOT/FUN',1)

    WITH (CC INTEGER, C CHAR(16) ) -- C contains data like '00012345'

    EXEC sp_xml_removedocument @hdoc

    Returns:

    1 12345 --should be 00012345

    2 23456 -- should be 00023456

    3 S92800012 -- is OK

    I've tried a variety of data types and all the same. I can't force leading zeros onto the string, as they are a variable length and mixed alphanumeric. Any ideas?

    Richard

  • Can you post some XML document and I'll take a look.

    Steve Jones

    steve@dkranch.net

  • Not sure but have you tried with data in which the first row has alphanumeric element. Although not related there are many utilities and imports in which the character element start getting recognized only after it has encountered its first alphanumeric value otherwise it is treated as numeric. XML documents being not that data specific can be the case. Just a vague guess but it can help us debug the problem.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply