Querying XML : Type conversion in expression (CONVERT_IMPLICIT

• r5d4

  SSCrazy

  Points: 2523

  More actions

  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

• Phil Parkin

  SSC Guru

  Points: 247155

  More actions

  June 22, 2022 at 8:44 pm

  #4053583

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

  ---

• r5d4

  SSCrazy

  Points: 2523

  More actions

  June 22, 2022 at 10:06 pm

  #4053606

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