Querying XML : Type conversion in expression (CONVERT_IMPLICIT

  • Hi folk,

    I've mocked a small piece of code that reproduces my problem.

    I want to get rid of the Type conversion message I get in the query plan but have exhausted all ideas (mostly trying to use CONVERT).

    So,  the error I get is

    Type conversion in expression (CONVERT_IMPLICIT(nvarchar(128),[Expr1012],0)) may affect "CardinalityEstimate" in query plan choice, Type conversion in expression (CONVERT_IMPLICIT(nvarchar(128),[Expr1011],0)) may affect "CardinalityEstimate" in query plan choice

    My attempts at extracting a value from XML are here >

      
    declare @xmldata xml
    , @eventtype1 varchar(50)
    , @eventtype2 varchar(50)


    declare @xmltext varchar(1000)
    set @xmltext = '<EVENT_INSTANCE>
    <EventType>ALTER_AUTHORIZATION_DATABASE</EventType>
    <PostTime>2022-06-20T19:00:00.930</PostTime>
    <SPID>202</SPID>
    <ServerName>SUNSHINE</ServerName>
    <LoginName>Domain\SQLService</LoginName>
    <UserName>dbo</UserName>
    <DatabaseName>sysTools</DatabaseName>
    <SchemaName />
    <ObjectName>object1</ObjectName>
    <ObjectType>DATABASE</ObjectType>
    <OwnerName>sa</OwnerName>
    <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>ALTER AUTHORIZATION ON DATABASE::[x] to sa;</CommandText>
    </TSQLCommand>
    </EVENT_INSTANCE>'



    set @xmldata = convert(xml,@xmltext)

    set @eventtype1 = CONVERT(VARCHAR(50),@xmldata.value('data(/EVENT_INSTANCE/EventType)[1]', 'SYSNAME') )
    print @eventtype1

    set @eventtype2 = CONVERT(varchar(50),@xmldata.query('data(/EVENT_INSTANCE/EventType)[1]'))
    print @eventtype2

    Any help, much appreciated.

    R

  • I've tried everything I can think of and I can't figure it out, either!

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thank you, it's very frustrating isn't it!

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

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