INNER JOIN a database table with XML data

Question

INNER JOIN a database table with XML data

pramodbr2002

Old Hand

Points: 314
More actions
January 25, 2011 at 4:42 pm

#230812

I have a XML data passed on to the stored proc in the following format, and within the stored proc I am accessing the data of xml using the nodes() method
Here is an example of what i am doing
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
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
My desired output after the join would be
EmployeeID EmployeeName Email Home Address
--------------- ----------- --------------- -----------
1 abcd abcd@abcd.com New York
2 efgh efgh@efgh.com Austin
January 26, 2011 at 3:36 am

This was removed by the editor as SPAM
October 8, 2015 at 7:11 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sriram-288748 SSCrazy Points: 2874 More actions · Answer 1

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

pramodbr2002 Old Hand Points: 314 More actions · Answer 2

@sriram-2 and @stewart, Thanks for the solution, both works perfectly.

@sriram-2, I am actually populating a table variable through this query, hence wanted to get the join working.

my xml dataset is quite small, so having a join directly should be ok i guess.

tfifield SSCrazy Eights Points: 9655 More actions · Answer 3

pramodbr2002 26-Jan-2011 @ 7:09:25 AM
@sriram-2, I am actually populating a table variable through this query, hence wanted to get the join working.
my xml dataset is quite small, so having a join directly should be ok i guess.

If your XML dataset does get larger, you might want to consider inserting it into a temp table and using that to join to the main table.

Todd Fifield

Jared SSC Guru Points: 61793 More actions · Answer 4

Hi All,

I have a similar issue, but I am now trying to also return the xml data AS xml with the returned set.

So the expected results would be:

EmployeeID EmployeeName EmployeeAddress XML_Tags

1 abcd hyderabad <Employees EmpID="1" EmpName="abcd" />

2 efgh pune <Employees EmpID="2" EmpName="efgh" />

How can I do this?

-Jared

Jared
CE - Microsoft

ivan 26383 SSC Enthusiast Points: 128 More actions · Answer 5

If the xml data was stores in a table instead of a variable, how would the query look like?

ivan 26383 SSC Enthusiast Points: 128 More actions · Answer 6

ivan 26383

SSC Enthusiast

Points: 128

October 8, 2015 at 7:15 am

#1832277

Ok, thanks 🙂