Post reply

CHARINDEX problem

  • February 10, 2012 at 2:55 am

    #264361

    I have a 'text' column that contains a string representation of some XML (Im using a third party logging product that logs as text, so no control over the datatype)

    I prepend some descriptive text at the start of the text, I then want to extract the xml 9after the description) and retrieve it as xml

    Im having a weird issue with the CHARINDEX function im using it in conjunction with substring to exactract the xml string

    SELECT top 2 [Id]

    ,[Date]

    ,[Thread]

    ,[Level]

    ,[Logger]

    ,[Message]

    ,[Exception]

    ,DATALENGTH([Message]) as dLength

    ,CHARINDEX('</PolData>', [Message]) as thepos

    --,CAST(SUBSTRING([Message],CHARINDEX('<', [Message]), DATALENGTH([Message]) - CHARINDEX('</PolData>', [Message]) + 11 + CHARINDEX('<', [Message])) As xmlValue

    ,SUBSTRING([Message],CHARINDEX('<', [Message]), DATALENGTH([Message]) - CHARINDEX('<', [Message]) +1) AS XML) As xmlValue

    FROM [Protean_BandC_QA].[dbo].[Log]

    where [Message] LIKE '%output XML For quote 12345%'

    order by Date desc

    go

    the xml in the string starts with <PolData Type="Output">

    and ends with </PolData>

    the contents of 'thepos' are returning as 0 !, yet on another query i get an actual value, but the strings in both records are of the same format, the bot start and end with the same sequence of characters

    this is an example of the string

    "output XML For quote 12345 : Scheme : 7310 : <PolData Type="Output"> <Product> ..........

    ...... MORE XML.......... </PolData>"

    so I basically want to return as xml, everything from the first occurance of the '<' character, up to the end of the string. Can anyone see what im doing wrong here ? why does CHARINDEX return zero ?

  • February 10, 2012 at 7:22 am

    #1445542

    I suspect your problem is with DATALENGTH.

    Try using varchar(MAX) instead of text and simplifying your expressions.

    (You do not need the length of the substring, just any number greater than its length.)

    DECLARE @test-2 varchar(MAX) = 'output XML For quote 12345 : Scheme : 7310 : <PolData Type="Output"> <Product> ................ MORE XML.......... </PolData>'

    SELECT SUBSTRING(@test, CHARINDEX('<PolData', @test-2), 2147483645)

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

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