none
Need help with For XML

    Question

  • Hi All ,

    I need some help with a fox xml.. 

    DDL's are like below.. 

    Declare @Activities table ( Id int ) Declare @Event table ( Id int , Event_Name nvarchar(50) , brand_id smallint ) insert into @Activities select 1 insert into @Event select 1 ,'a' , 16 union all select 1 ,'b' , 16 union all select 1 ,'a' , 15 -- Needed output...

    --- for event_name 'a' I need the record in node O and for event_name 'b' I need it in P ..

    -- <cmp:O b="16"/> -- <cmp:P b="16"/> -- <cmp:O b="15"/>

    /*


    '<cmp:QFU id="1" xmlns:cmp="http://my/schemas/20120701"> <cmp:Clks> <cmp:O b="16"/> <cmp:P b="16"/> <cmp:O b="15"/> </cmp:Clks> </cmp:QFU>' */

    Thanks,
    Saurabh


    http://www.linkedin.com/in/sbhadauria http://www.experts-exchange.com/M_6313078.html

    Friday, August 23, 2013 3:27 PM

Answers

  • Try this:

    declare @Activities table ( Id int  )
    Declare @Event table ( Id int, Event_Name nvarchar(50), brand_id smallint  )
    
    insert into @Activities select 1 
    insert into  @Event
    select  1 , 'a' , 16  union all 
    select  1 , 'b' , 16 union all 
    select  1 , 'a' , 15  
    
    ;with xmlnamespaces ( 'http://my/schemas/20120701' AS cmp )
    select
    	m.Id AS "@id",
    	(
    	select brand_id AS "@b"
    	from @Event
    	where Event_Name = 'a'
    	for xml path('cmp:O'), type
    	) AS "cmp:Clks",
    	(
    	select brand_id AS "@b"
    	from @Event
    	where Event_Name = 'b'
    	for xml path('cmp:P'), type
    	)  AS "cmp:Clks"
    from @Activities m
    for xml path('cmp:QFU'), type

    Saturday, August 24, 2013 5:59 PM
    Answerer

All replies

  • Hi ,

    Try like this ,

     
     Declare  @Event table
     (   
       Id int ,
        Event_Name nvarchar(50) , 
        brand_id smallint 
     )
      
    insert into  @Event
    select  1  ,'a' , 16  union all 
    select  1 ,'b' , 16 union all 
    select  1 ,'a' , 15  
    SELECT CASE WHEN Event_Name = 'b' THEN 'cmp:O' 
           WHEN Event_Name = 'a' THEN 'cmp:P' 
    	   ELSE NULL END  +' '+Event_Name +'='+CONVERT(VARCHAR(5),brand_id)  Events
    FROM @Event FOR XML RAW('Activities')


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Saturday, August 24, 2013 7:41 AM
  • Try this:

    declare @Activities table ( Id int  )
    Declare @Event table ( Id int, Event_Name nvarchar(50), brand_id smallint  )
    
    insert into @Activities select 1 
    insert into  @Event
    select  1 , 'a' , 16  union all 
    select  1 , 'b' , 16 union all 
    select  1 , 'a' , 15  
    
    ;with xmlnamespaces ( 'http://my/schemas/20120701' AS cmp )
    select
    	m.Id AS "@id",
    	(
    	select brand_id AS "@b"
    	from @Event
    	where Event_Name = 'a'
    	for xml path('cmp:O'), type
    	) AS "cmp:Clks",
    	(
    	select brand_id AS "@b"
    	from @Event
    	where Event_Name = 'b'
    	for xml path('cmp:P'), type
    	)  AS "cmp:Clks"
    from @Activities m
    for xml path('cmp:QFU'), type

    Saturday, August 24, 2013 5:59 PM
    Answerer
  • That's the answer...  

    Thanks a lot wBob.. 

    -Saurabh


    http://www.linkedin.com/in/sbhadauria http://www.experts-exchange.com/M_6313078.html

    Sunday, August 25, 2013 3:48 AM