T- SQL Query

• greeshatu

  Right there with Babe

  Points: 791

  More actions

  May 5, 2014 at 5:15 am

  #292950

  Hi

  I have a Table 'A' where column X is of type XML.

  Following is Column X value

  <qsds:Details Xmlns:qsds="http://www.abc.com/Details">

  <Category>

  <Employee EmployeeID="01">

  <Name>

  <Title>Mr</Title>

  <Forenames>XXX</Forenames>

  <Surname>YYY</Surname>

  </Name>

  </Employee>

  <Employee EmployeeID="02">

  <Name>

  <Title>Mr</Title>

  <Forenames>MMM</Forenames>

  <Surname>ZZZ</Surname>

  </Name>

  </Employee>

  <Employee EmployeeID="03">

  <Name>

  <Title>Mr</Title>

  <Forenames>Caron</Forenames>

  <Surname>Cobb</Surname>

  </Name>

  </Employee>

  </Category>

  </qsds:Details>

  I need to get number of employees of above XML data Using T-SQL.

  This is sample XML for a row of column X . Employee count changes per row.

  Please help me how to write a T-sql Query to get no of employees in sample XML provided.

• gbritton1

  SSCertifiable

  Points: 6522

  More actions

  May 5, 2014 at 6:46 am

  #1710686

  This worked for me:

  declare @xml as xml = '

  <Category>

  <Employee EmployeeID="01">

  <Name>

  <Title>Mr</Title>

  <Forenames>XXX</Forenames>

  <Surname>YYY</Surname>

  </Name>

  </Employee>

  <Employee EmployeeID="02">

  <Name>

  <Title>Mr</Title>

  <Forenames>MMM</Forenames>

  <Surname>ZZZ</Surname>

  </Name>

  </Employee>

  <Employee EmployeeID="03">

  <Name>

  <Title>Mr</Title>

  <Forenames>Caron</Forenames>

  <Surname>Cobb</Surname>

  </Name>

  </Employee>

  </Category>

  '

  select sum(cast(employee.exist('@EmployeeID') as int))

  from @xml.nodes('/Category/Employee') xmlnode(employee)