XML resultset using Query

• SqlStarter

  Old Hand

  Points: 387

  More actions

  October 29, 2016 at 2:15 am

  #331685

  Hi ,

  I am trying to form XML (as mentioned below) from the below data using query. Can someone please suggest/advice me how can i make it possible to derive the mentioned XML format using Query?. it is bit complex since it is spanning across two tables. thanks in Advance....

  CREATE TABLE #EmpMaster (EMP_ID INT,Ename VARCHAR(50))

  CREATE TABLE #EmpDetail (EMP_ID INT, SUBJECT VARCHAR(50),MARK VARCHAR(50))

  INSERT INTO #EmpMaster VALUES (100,'Marconi')

  INSERT INTO #EmpMaster VALUES (200,'Pascal')

  INSERT INTO #EmpDetail VALUES (100,'English','90')

  INSERT INTO #EmpDetail VALUES (100,'Maths','80')

  INSERT INTO #EmpDetail VALUES (100,'Science','70')

  INSERT INTO #EmpDetail VALUES (200,'English','55')

  INSERT INTO #EmpDetail VALUES (200,'Maths','66')

  INSERT INTO #EmpDetail VALUES (200,'Science','95')

  Expected Output from Query:

  <XML>

  <Student>

  <EMP_ID>100</EMP_ID>

  <Ename>Marconi</Ename>

  <Marks_Details>

  <MARK_DET>

  <SUBJECT>English</Subject>

  <MARK>90</MARK>

  </MARK_DET>

  <MARK_DET>

  <SUBJECT>Maths</Subject>

  <MARK>80</MARK>

  </MARK_DET>

  <MARK_DET>

  <SUBJECT>Science</Subject>

  <MARK>70</MARK>

  </MARK_DET>

  </Marks_Details>

  </Student>

  <Student>

  <EMP_ID>200</EMP_ID>

  <Ename>Pascal</Ename>

  <Marks_Details>

  <MARK_DET>

  <SUBJECT>English</Subject>

  <MARK>55</MARK>

  </MARK_DET>

  <MARK_DET>

  <SUBJECT>Maths</Subject>

  <MARK>66</MARK>

  </MARK_DET>

  <MARK_DET>

  <SUBJECT>Science</Subject>

  <MARK>95</MARK>

  </MARK_DET>

  </Marks_Details>

  </Student>

  </XML>

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  October 29, 2016 at 2:33 am

  #1909467

  This is quite easy when using FOR XML PATH, here is a quick example

  😎

  USE TEEST;

  GO

  SET NOCOUNT ON;

  IF OBJECT_ID(N'tempdb..#EmpMaster') IS NOT NULL DROP TABLE #EmpMaster;

  CREATE TABLE #EmpMaster (EMP_ID INT,Ename VARCHAR(50))

  IF OBJECT_ID(N'tempdb..#EmpDetail') IS NOT NULL DROP TABLE #EmpDetail;

  CREATE TABLE #EmpDetail (EMP_ID INT, SUBJECT VARCHAR(50),MARK VARCHAR(50))

  INSERT INTO #EmpMaster (EMP_ID,Ename)

  VALUES (100,'Marconi')

  ,(200,'Pascal' );

  INSERT INTO #EmpDetail (EMP_ID,SUBJECT,MARK)

  VALUES (100,'English' ,'90')

  ,(100,'Maths' ,'80')

  ,(100,'Science' ,'70')

  ,(200,'English' ,'55')

  ,(200,'Maths' ,'66')

  ,(200,'Science' ,'95');

  SELECT

  EM.EMP_ID AS EMP_ID

  ,EM.Ename AS ENAME

  ,(

  SELECT

  ED.SUBJECT

  ,ED.MARK

  FROM #EmpDetail ED

  WHERE EM.EMP_ID = ED.EMP_ID

  FOR XML PATH('MARK_DET'),TYPE,ROOT('MARKS_DETAILS')

  )

  FROM #EmpMaster EM

  FOR XML PATH('STUDENT'), ROOT('XML');

  Output

  <XML>

  <STUDENT>

  <EMP_ID>100</EMP_ID>

  <ENAME>Marconi</ENAME>

  <MARKS_DETAILS>

  <MARK_DET>

  <SUBJECT>English</SUBJECT>

  <MARK>90</MARK>

  </MARK_DET>

  <MARK_DET>

  <SUBJECT>Maths</SUBJECT>

  <MARK>80</MARK>

  </MARK_DET>

  <MARK_DET>

  <SUBJECT>Science</SUBJECT>

  <MARK>70</MARK>

  </MARK_DET>

  </MARKS_DETAILS>

  </STUDENT>

  <STUDENT>

  <EMP_ID>200</EMP_ID>

  <ENAME>Pascal</ENAME>

  <MARKS_DETAILS>

  <MARK_DET>

  <SUBJECT>English</SUBJECT>

  <MARK>55</MARK>

  </MARK_DET>

  <MARK_DET>

  <SUBJECT>Maths</SUBJECT>

  <MARK>66</MARK>

  </MARK_DET>

  <MARK_DET>

  <SUBJECT>Science</SUBJECT>

  <MARK>95</MARK>

  </MARK_DET>

  </MARKS_DETAILS>

  </STUDENT>

  </XML>

• SqlStarter

  Old Hand

  Points: 387

  More actions

  October 29, 2016 at 6:08 am

  #1909473

  Thanks a lot Eirikur Eiriksson

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  October 29, 2016 at 6:24 am

  #1909474

  SqlStarter (10/29/2016)

  ---

  Thanks a lot Eirikur Eiriksson

  You are very welcome.

  😎

• SqlStarter

  Old Hand

  Points: 387

  More actions

  November 3, 2016 at 9:04 pm

  #1910513

  Please ignore... the solution provided itself works for this.. thanks

  HI , Above code is like inner join... my scenario is more like left outer join... in below mentioned data, how can i get empid 300?... any suggestion please?

  CREATE TABLE #EmpMaster (EMP_ID INT,Ename VARCHAR(50))

  CREATE TABLE #EmpDetail (EMP_ID INT, SUBJECT VARCHAR(50),MARK VARCHAR(50))

  INSERT INTO #EmpMaster VALUES (100,'Marconi')

  INSERT INTO #EmpMaster VALUES (200,'Pascal')

  INSERT INTO #EmpMaster VALUES (300,'Newton')

  INSERT INTO #EmpDetail VALUES (100,'English','90')

  INSERT INTO #EmpDetail VALUES (100,'Maths','80')

  INSERT INTO #EmpDetail VALUES (100,'Science','70')

  INSERT INTO #EmpDetail VALUES (200,'English','55')

  INSERT INTO #EmpDetail VALUES (200,'Maths','66')

  INSERT INTO #EmpDetail VALUES (200,'Science','95')

  Expected output

  <XML>

  <Student>

  <EMP_ID>100</EMP_ID>

  <Ename>Marconi</Ename>

  <Marks_Details>

  <MARK_DET>

  <SUBJECT>English</Subject>

  <MARK>90</MARK>

  </MARK_DET>

  <MARK_DET>

  <SUBJECT>Maths</Subject>

  <MARK>80</MARK>

  </MARK_DET>

  <MARK_DET>

  <SUBJECT>Science</Subject>

  <MARK>70</MARK>

  </MARK_DET>

  </Marks_Details>

  </Student>

  <Student>

  <EMP_ID>200</EMP_ID>

  <Ename>Pascal</Ename>

  <Marks_Details>

  <MARK_DET>

  <SUBJECT>English</Subject>

  <MARK>55</MARK>

  </MARK_DET>

  <MARK_DET>

  <SUBJECT>Maths</Subject>

  <MARK>66</MARK>

  </MARK_DET>

  <MARK_DET>

  <SUBJECT>Science</Subject>

  <MARK>95</MARK>

  </MARK_DET>

  </Marks_Details>

  <EMP_ID>300</EMP_ID>

  <Ename>Newton</Ename>

  </Student>

  </XML>