Post reply

Querying XML : Type conversion in expression (CONVERT_IMPLICIT

  • June 22, 2022 at 6:51 pm

    #4053531

    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

  • June 22, 2022 at 8:44 pm

    #4053583

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

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • June 22, 2022 at 10:06 pm

    #4053606

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

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

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