DECLARE @Participants XML
SET @Participants = '<ArrayOfEmployees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Employees EmpID="1" EmpName="abcd" />
<Employees EmpID="2" EmpName="efgh" />
</ArrayOfEmployees >'
SELECT Participants.Node.value('@EmpID', 'INT') AS EmployeeID,
Participants.Node.value('@EmpName', 'VARCHAR(50)') AS EmployeeName
FROM
@Participants.nodes('/ArrayOfEmployees /Employees ') Participants (Node)
the above query produces the following result
EmployeeID EmployeeName
--------------- -----------
1 abcd
2 efgh
So. thats where till you have reached, one thing that you have to make sure over here is your XML if fixed, and its structure does not change.
How do I join the data coming out from the above query with another table in the database, for example Employee in this case
i.e. somewhat like this (note the following query does not perform the join correctly)
SELECT Participants.Node.value('@EmpID', 'INT') AS EmployeeID,
Participants.Node.value('@EmpName', 'VARCHAR(50)') AS EmployeeName
FROM
@Participants.nodes('/ArrayOfEmployees /Employees ') Participants (Node)
INNER JOIN
Employee EMP ON EmployeeID = EMP .EmployeeID
over here you cannot make a join like that, you need to join on the particular index of the xml , I am just creating a table for example.
create table employee(empid int identity(1,1) primary key, empname varchar(50), empaddress varchar(50))
insert into employee(empname,empaddress) values('abcd','hyderabad')
insert into employee(empname,empaddress) values('efgh','pune')
insert into employee(empname,empaddress) values('ijkl','banglore')
insert into employee(empname,empaddress) values('mnop','gurgaon')
insert into employee(empname,empaddress) values('qrst','mumbai')
the below sql would work
SELECT Participants.Node.value('@EmpID', 'INT') AS EmployeeID,
Participants.Node.value('@EmpName', 'VARCHAR(50)') AS EmployeeName,
emp.empaddress as EmployeeAddress
FROM
@Participants.nodes('/ArrayOfEmployees /Employees ') Participants (Node)
INNER JOIN employee emp on emp.empid = Participants.Node.value('@EmpID[1]', 'INT')
and the desired output would be
EmployeeIDEmployeeNameEmployeeAddress
1abcdhyderabad
2efghpune
EditWould defintely suggesto you to create a table variable/ temp tabe and join using it.
Sriram