Help with XQuery select

• Senthil T

  Hall of Fame

  Points: 3367

  More actions

  April 21, 2009 at 11:54 pm

  #130207

  Hi,

  In this below xml, how can I select the list of employee ids (as int) where location id is 3? I can get all the list of empids using this query

  SELECT tbl.rows.value('.', 'int')

  FROM @company.nodes('/Company/Locations/Location/Department/Employees/EmpId') AS tbl(rows)

  How do i add an where/exist condition (LocId=3) to this above query?

  DECLARE @company XML

  1

  105

  100

  101

  102

  3

  106

  200

  121

  202

• Senthil T

  Hall of Fame

  Points: 3367

  More actions

  April 22, 2009 at 12:06 am

  #981959

  Nevermind. This one worked.

  SELECT tbl2.rows2.value('.', 'int')

  FROM @company.nodes('/Company/Locations/Location/Department') AS tbl(rows)

  CROSS APPLY tbl.rows.nodes('./Employees/EmpId') AS tbl2(rows2)

  WHERE tbl.rows.exist('../LocId[text()="3"]') = 1