InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 05:03:25 GMT20http://www.sqlservercentral.com/Forums/Topic1053593-391-1.aspxHi 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_Tags1 abcd hyderabad <Employees EmpID="1" EmpName="abcd" /> 2 efgh pune <Employees EmpID="2" EmpName="efgh" />How can I do this?-JaredMon, 14 Mar 2011 11:39:56 GMTSQLKnowItAllhttp://www.sqlservercentral.com/Forums/Topic1053593-391-1.aspx[quote]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.[/quote]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 FifieldThu, 27 Jan 2011 15:25:37 GMTtfifieldhttp://www.sqlservercentral.com/Forums/Topic1053593-391-1.aspx@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.Wed, 26 Jan 2011 08:09:25 GMTpramodbr2002http://www.sqlservercentral.com/Forums/Topic1053593-391-1.aspxYou could also use a CTE, e.g.[code="sql"]WITH EmpId (EmployeeId, EmployeeName)AS(SELECT Participants.Node.value('@EmpID', 'INT') AS EmployeeID,Participants.Node.value('@EmpName', 'VARCHAR(50)') AS EmployeeNameFROM @Participants.nodes('/ArrayOfEmployees /Employees ') Participants (Node) )SELECT EmpId.EmployeeId, EmpId.EmployeeName, Email, [Home Address]FROM EmpIdINNER JOIN Employee EMP ON EmpId.EmployeeID = EMP .EmployeeID[/code]Wed, 26 Jan 2011 03:36:36 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic1053593-391-1.aspx[quote]DECLARE @Participants XMLSET @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 resultEmployeeID EmployeeName--------------- -----------1 abcd2 efgh[/quote]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.[quote]How do I join the data coming out from the above query with another table in the database, for example Employee in this casei.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[/quote]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.[code="sql"]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')[/code]the below sql would work[code="sql"]SELECT Participants.Node.value('@EmpID', 'INT') AS EmployeeID,Participants.Node.value('@EmpName', 'VARCHAR(50)') AS EmployeeName,emp.empaddress as EmployeeAddressFROM @Participants.nodes('/ArrayOfEmployees /Employees ') Participants (Node) INNER JOIN employee emp on emp.empid = Participants.Node.value('@EmpID[1]', 'INT')[/code]and the desired output would beEmployeeID EmployeeName EmployeeAddress1 abcd hyderabad2 efgh pune[b]Edit[/b]Would defintely suggesto you to create a table variable/ temp tabe and join using it.Wed, 26 Jan 2011 02:16:13 GMTSriram-288748http://www.sqlservercentral.com/Forums/Topic1053593-391-1.aspxI 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() methodHere is an example of what i am doingDECLARE @Participants XMLSET @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 resultEmployeeID EmployeeName--------------- -----------1 abcd2 efghHow do I join the data coming out from the above query with another table in the database, for example Employee in this casei.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 .EmployeeIDMy desired output after the join would beEmployeeID EmployeeName Email Home Address--------------- ----------- --------------- -----------1 abcd abcd@abcd.com New York2 efgh efgh@efgh.com AustinTue, 25 Jan 2011 16:42:46 GMTpramodbr2002