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

INNER JOIN a database table with XML data Expand / Collapse
Author
Message
Posted Tuesday, January 25, 2011 4:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 28, 2011 1:15 PM
Points: 46, Visits: 11
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
Post #1053593
Posted Wednesday, January 26, 2011 2:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 20, 2013 2:04 AM
Points: 222, Visits: 331

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

EmployeeID EmployeeName EmployeeAddress
1 abcd hyderabad
2 efgh pune

EditWould defintely suggesto you to create a table variable/ temp tabe and join using it.


Sriram

Post #1053709
Posted Wednesday, January 26, 2011 3:36 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:19 AM
Points: 3,917, Visits: 5,109
You could also use a CTE, e.g.
WITH EmpId (EmployeeId, EmployeeName)
AS
(SELECT Participants.Node.value('@EmpID', 'INT') AS EmployeeID,
Participants.Node.value('@EmpName', 'VARCHAR(50)') AS EmployeeName
FROM
@Participants.nodes('/ArrayOfEmployees /Employees ') Participants (Node) )

SELECT EmpId.EmployeeId, EmpId.EmployeeName, Email, [Home Address]
FROM EmpId
INNER JOIN Employee EMP ON EmpId.EmployeeID = EMP .EmployeeID



____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1053747
Posted Wednesday, January 26, 2011 8:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 28, 2011 1:15 PM
Points: 46, Visits: 11
@sriram and @stewart, Thanks for the solution, both works perfectly.

@sriram, 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.
Post #1053887
Posted Thursday, January 27, 2011 3:25 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:44 PM
Points: 959, Visits: 2,884

pramodbr2002 26-Jan-2011 @ 7:09:25 AM

@sriram, 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
Post #1054986
Posted Monday, March 14, 2011 11:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 8:40 AM
Points: 2,692, Visits: 3,383
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



Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1077890
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse