May 5, 2014 at 5:15 am
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.
May 5, 2014 at 6:46 am
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)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy