Error while Wrapping up Parent Node

• ItzMe

  Ten Centuries

  Points: 1233

  More actions

  December 16, 2008 at 10:12 am

  #94289

  Hi Guys

  I have a code just as below

  declare @a xml

  declare @b-2 xml

  declare @C xml

  set @a = (select * from emp10

  for xml path('sublevel'))

  set @b-2 = (select * from emp20

  for xml path('sublevel'))

  set @C = (select * from emp30

  for xml path('sublevel'))

  select @a as [node()]

  union all

  select @b-2 as [node()]

  union all

  select @C as [node()]

  FOR XML PATH(''),TYPE, ELEMENTS, ROOT('MainLevel')

  --FOR XML PATH(''), ROOT('RootLevel')

  When executed the above code the get the output as below

  (MainLevel)

  (sublevel)

  (EMPNO)102(/EMPNO)

  (ENAME)abc(/ENAME)

  (JOB)def(/JOB)

  (MGR)102(/MGR)

  (HIREDATE)2008-10-12T00:00:00(/HIREDATE)

  (SAL)102.00(/SAL)

  (COMM)103.00(/COMM)

  (DEPTNO)30(/DEPTNO)

  (/sublevel)

  (sublevel)

  (EMPNO)7369(/EMPNO)

  (ENAME)SMITH(/ENAME)

  (JOB)CLERK(/JOB)

  (MGR)7902(/MGR)

  (HIREDATE)1980-12-17T00:00:00(/HIREDATE)

  (SAL)800.00(/SAL)

  (DEPTNO)20(/DEPTNO)

  (/sublevel)

  (/MainLevel)

  (Note: nodes are enclosed using () because the less than, greater than symbol was not supported by the browser)

  Now my task is to wrap the (MainLevel) node with a parent node (RootLevel)

  The desired output should be like .......

  (RootLevel)

  (MainLevel)

  (sublevel)

  (EMPNO)102(/EMPNO)

  (ENAME)abc(/ENAME)

  (JOB)def(/JOB)

  (MGR)102(/MGR)

  (HIREDATE)2008-10-12T00:00:00(/HIREDATE)

  (SAL)102.00(/SAL)

  (COMM)103.00(/COMM)

  (DEPTNO)30(/DEPTNO)

  (/sublevel)

  (sublevel)

  (EMPNO)7369(/EMPNO)

  (ENAME)SMITH(/ENAME)

  (JOB)CLERK(/JOB)

  (MGR)7902(/MGR)

  (HIREDATE)1980-12-17T00:00:00(/HIREDATE)

  (SAL)800.00(/SAL)

  (DEPTNO)20(/DEPTNO)

  (/sublevel)

  (/MainLevel)

  (/RootLevel)

  when executed by uncommented the last line in the code on the top of the page, thows an error saying

  " wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it."

  How to wrap the parent node? Do we have any alternate method for wrapping the parent node?

  Please Help!!!

  Regards

  ItzSam

• ItzMe

  Ten Centuries

  Points: 1233

  More actions

  December 17, 2008 at 12:20 pm

  #914095

  Hi Friends

  I got the solution for the topic i posted.

  Here is the way i did.............

  declare @a xml

  declare @b-2 xml

  declare @C xml

  set @a = (select * from emp10

  for xml path('sublevel'))

  set @b-2 = (select * from emp20

  for xml path('sublevel'))

  set @C = (select * from emp30

  for xml path('sublevel'))

  select

  (select "node()"

  from

  (select @a as "node()"

  union all

  select @b-2 as "node()"

  union all

  select @C as "node()") T

  FOR XML PATH(''),TYPE, ELEMENTS, ROOT('MainLevel'))

  FOR XML PATH(''), ROOT('RootLevel')

  Hope this will help others who are in need...... 🙂

  Regards

  ItzSam