count instances using sql:variable

• owen barnes

  SSC Rookie

  Points: 47

  More actions

  September 25, 2007 at 12:50 am

  #95400

  I'm trying to pass the xpath to an element in a variable to the count function. But the execution does not return the expected results.

  If I hardcode the path then this returns 2 as expected; however when passing the xpath in a sql:variable, the operation returns 1 - guess it counts the variable rather than interpreting the variable as a string (the xpath). Have used sql:variable in predicate okay.

  Shorten code below. Any help, pointers to where I've gone wrong, explanation, work around gratefully received....

  DECLARE @r xml

  SET @r = '

  Borrower

  Borrower

  '

  declare @index int

  declare @xpath varchar(max)

  set @xpath = '/Root/TableMap/Row'

  set @index = @r.value('count(sql:variable("@xpath"))', 'int')

  select @index

  set @index = @r.value('count(/Root/TableMap/Row)', 'int')

  select @index

  System Info

  Microsoft SQL Server Management Studio9.00.3042.00

  Microsoft Analysis Services Client Tools2005.090.3042.00

  Microsoft Data Access Components (MDAC)2000.086.3959.00 (srv03_sp2_rtm.070216-1710)

  Microsoft MSXML2.6 3.0 4.0 6.0

  Microsoft Internet Explorer6.0.3790.3959

  Microsoft .NET Framework2.0.50727.42

  Operating System5.2.3790

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  April 9, 2008 at 5:48 am

  #800279

  I think my timing may be a little off, but anyway...

  declare @t table (x xml)

  insert @t

  select '<a link="x" />'

  union all select '<a><b link="y" /></a>'

  union all select '<a><b /></a>'

  select * from @t where x.exist('//@link') = 1

  /* results

  x

  --------------------

  <a link="x" />

  <a><b link="y" /></a>

  */

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.