Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

T- SQL Query Expand / Collapse
Author
Message
Posted Monday, May 5, 2014 5:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 4:37 AM
Points: 36, Visits: 122
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.
Post #1567475
Posted Monday, May 5, 2014 6:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 338, Visits: 747
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)

Post #1567487
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse