Generate xml

• S-322532

  SSCommitted

  Points: 1613

  More actions

  July 11, 2011 at 2:24 am

  #244122

  Hi ,

  I need to generate an xml output as shown below

  - <root>

  - <sales emp_no="1" ename="emp1" manager="mngr1">

  <row element="item" sid="1" emp_no="1" salemonth="jan" saleamount="5000.00" />

  <row element="item" sid="8" emp_no="1" salemonth="mar" saleamount="5400.00" />

  </sales>

  - <sales emp_no="2" ename="emp2" manager="mngr2">

  <row element="item" sid="4" emp_no="4" salemonth="jan" saleamount="5400.00" />

  <row element="item" sid="5" emp_no="4" salemonth="mar" saleamount="5000.00" />

  </sales>

  - <sales emp_no="3" ename="emp3" manager="mngr1">

  <row element="item" sid="6" emp_no="3" salemonth="mar" saleamount="5200.00" />

  <row element="item" sid="3" emp_no="3" salemonth="jan" saleamount="5300.00" />

  </sales>

  - <sales emp_no="4" ename="emp4" manager="mngr2">

  <row element="item" sid="7" emp_no="2" salemonth="mar" saleamount="5300.00" />

  <row element="item" sid="2" emp_no="2" salemonth="jan" saleamount="5200.00" />

  </sales>

  </root>

  , based on the below test data below

  create table e2

  (

  emp_no int identity(1,1),

  ename varchar(10),

  manager varchar(10)

  )

  create table s2

  (

  sid int identity(1,1),

  emp_no int,

  salemonth varchar(10),

  saleamount decimal(10,2)

  )

  insert into e2

  select 'emp1','mngr1' union all

  select 'emp2','mngr2' union all

  select 'emp3','mngr1' union all

  select 'emp4','mngr2'

  select * from e2

  insert into s2

  select 1, 'jan',5000 union all

  select 2, 'jan',5200 union all

  select 3, 'jan',5300 union all

  select 4, 'jan',5400 union all

  select 4, 'mar',5000 union all

  select 3, 'mar',5200 union all

  select 2, 'mar',5300 union all

  select 1, 'mar',5400

  select * from s2

  Can anyone one guide me on this ? 🙂

  Thanks

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  July 11, 2011 at 2:57 am

  #1350935

  SELECT e.emp_no AS "@emp_no",

  e.ename AS "@ename",

  e.manager AS "@manager",

  (SELECT 'item' AS "@element",

  s.sid AS "@sid",

  s.emp_no AS "@emp_no",

  s.salemonth AS "@salemonth",

  s.saleamount AS "@saleamount"

  FROM s2 s

  WHERE s.emp_no=e.emp_no

  ORDER BY s.sid

  FOR XML PATH('row'),TYPE)

  FROM e2 e

  ORDER BY e.emp_no

  FOR XML PATH('sales'),ROOT('root');

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• S-322532

  SSCommitted

  Points: 1613

  More actions

  July 11, 2011 at 8:59 pm

  #1351405

  Thank you so much Mark 🙂

  I have made use of your code 🙂