get sql query result in xml format?

Question

Hello i am trying the getting result in xml format in the sql server but i don't get propare xml format many data it's repeat in xml format. i have write this query in the sql server but not a getting propare data. any one know where is my mistake then please let me know how can do that.

Here i have write this query :

select * from (

    SELECT SSCF.SubSubCategoryId AS InterestId,c.FeedId,c.Description,u.UserId,u.Email,u.UserName,u.ProfileImage,u.Name,
           ISNULL(SSCL.SubSubCategory,SSC.SubSubCategory) AS Interest,
           1 AS [Type]
    FROM SubSubCategoryFollowers SSCF
    LEFT JOIN SubSubCategories SSC ON SSCF.SubSubCategoryId = SSC.SubSubCategoryId              
    INNER JOIN Feed c on c.FeedId = SSC.FeedId
    inner join Users u on u.UserId = SSCF.UserId
    WHERE u.Email is not null

    UNION ALL

    SELECT  SCF.SubCategoryId AS InterestId,c.FeedId,c.Description,u.UserId,u.Email,u.UserName,u.ProfileImage,u.Name,
           ISNULL(SCL.SubCategory,SC.SubCategory) AS Interest,
           2 AS [Type]
    FROM SubCategoryFollowers SCF
    LEFT JOIN SubCategories SC ON SCF.SubCategoryId = SC.SubCategoryId              
    INNER JOIN Feed c on c.FeedId = SC.FeedId
    inner join Users u on u.UserId = SCF.UserId
    WHERE u.Email is not null 

 )as res 

group by res.UserId,res.InterestId,res.FeedId,res.Description,res.Email,res.Interest,res.Type,res.UserName,res.ProfileImage,res.Name
order by res.FeedId             
OFFSET 1   ROWS
FETCH NEXT  50000   ROWS ONLY
FOR XML PATH('User'), ROOT ('Users')

this is my current op =>

 <Users>
  <User>
    <UserId>1660</UserId>
    <Email>xyz.com</Email>
    <UserName>xyz</UserName>
    <ProfileImage>20160717035320958.jpeg</ProfileImage>     
    <InterestId>15</InterestId>
    <FeedId>4689</FeedId>
    <Description>Test</Description>         
    <Interest>Event</Interest>
    <Type>2</Type>
</User>
<User>  
     <UserId>1660</UserId>
    <Email>xyz.com</Email>
    <UserName>xyz</UserName>
    <ProfileImage>20160717035320958.jpeg</ProfileImage> 
    <InterestId>16</InterestId>
    <FeedId>4689</FeedId>
    <Description>Test</Description>        
    <Interest>Party</Interest>
    <Type>2</Type>
</User>
<User>
    <UserId>1660</UserId>
    <Email>xyz.com</Email>
    <UserName>xyz</UserName>
    <ProfileImage>20160717035320958.jpeg</ProfileImage> 
    <InterestId>21</InterestId>
    <FeedId>4689</FeedId>
    <Description>Test</Description>      
    <Interest>Club</Interest>
    <Type>2</Type>
</User>
<User>
<UserId>1661</UserId>
<Email>abc.com</Email>
<UserName>abc</UserName>
<ProfileImage>20160717035320959.jpeg</ProfileImage>     
<InterestId>15</InterestId>
<FeedId>4690</FeedId>
<Description>Test1</Description>         
<Interest>Cricket</Interest>
<Type>1</Type>

My expected o/p =>

<Users>
 <User>
 <UserId>1660</UserId>
<Email>xyz.com</Email>
<UserName>xyz</UserName>
 <ProfileImage>20160717035320958.jpeg</ProfileImage>  

<InterestId>15</InterestId>
<FeedId>4689</FeedId>
<Description>Test</Description>         
<Interest>Event</Interest>
<Type>2</Type>

<InterestId>16</InterestId>
<FeedId>4689</FeedId>
<Description>Test</Description>        
<Interest>Party</Interest>
<Type>2</Type>

<InterestId>21</InterestId>
<FeedId>4689</FeedId>
<Description>Test</Description>      
<Interest>Club</Interest>
<Type>2</Type>
</User>
<User>
<UserId>1661</UserId>
<Email>abc.com</Email>
<UserName>abc</UserName>
<ProfileImage>20160717035320959.jpeg</ProfileImage>  

<InterestId>15</InterestId>
<FeedId>4690</FeedId>
<Description>Test1</Description>         
<Interest>Cricket</Interest>
<Type>1</Type>

i want like this data in xml format any one know please let me know.

What is the result of your query as is and how is it different to your desired output? — iamdave
– iamdave, Commented Jun 5, 2017 at 8:32
right now in i have repeat this data as well like UserId is repeat all the data is repeat so i need the stop this repeat data — coderwill
– coderwill, Commented Jun 5, 2017 at 8:33
@iamdave can i post my current op so you can easy to understand? — coderwill
– coderwill, Commented Jun 5, 2017 at 8:35
@iamdave i have edit my question you can check this and please help me on that. — coderwill
– coderwill, Commented Jun 5, 2017 at 8:38

TriV · Accepted Answer · 2017-06-05 09:47:58Z

2

I have an example for your case. You could use TYPE to get nested xml

DECLARE @SampleData AS TABLE
(
    UserId INT,
    Email varchar(200),
    UserName varchar(200),
    InterestId int,
    Description varchar(200)
)

INSERT INTO @SampleData
(
    UserId,
    Email,
    UserName,
    InterestId,
    Description
)
VALUES
(1,'[email protected]','User1', 100, 'Description 100'),
(1,'[email protected]','User1', 101, 'Description 101'),
(1,'[email protected]','User1', 102, 'Description 102'),
(1,'[email protected]','User1', 103, 'Description 103')


SELECT  sd.UserId,
       sd.Email,
       sd.UserName,
       (
          SELECT sd2.InterestId,
                sd2.Description 
          FROM @SampleData sd2
          WHERE sd2.UserId = sd.UserId
          FOR XML PATH (''), TYPE
       ) 
FROM 
(   select DISTINCT sd.UserId, sd.Email, sd.UserName
    FROM @SampleData sd
) sd
FOR XML PATH('User') ,ROOT('Users')

Returns

<Users>
  <User>
    <UserId>1</UserId>
    <Email>[email protected]</Email>
    <UserName>User1</UserName>
    <InterestId>100</InterestId>
    <Description>Description 100</Description>
    <InterestId>101</InterestId>
    <Description>Description 101</Description>
    <InterestId>102</InterestId>
    <Description>Description 102</Description>
    <InterestId>103</InterestId>
    <Description>Description 103</Description>
  </User>
</Users>

Demo link: http://rextester.com/KKEHCR86171

answered Jun 5, 2017 at 9:47

TriV

5,1582 gold badges12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

coderwill Over a year ago

Triv with my query how can do that in query i can used union also so

coderwill Over a year ago

can you please give me with my query your answer so it's very use full for me this solution it's also 100% good but i can't modify with my query so please can you more one help

TriV Over a year ago

You should try yourself. I think it's not so hard to map my answer with your code.... :)

coderwill Over a year ago

i am getting this error The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.

coderwill Over a year ago

Hello triv i am trying to add your solution with my query and i am get result also but pagination is not working so any idea?

Jayvee · Accepted Answer · 2017-06-05 09:32:38Z

2

The idea is to give the xml the format you want by labelling the path tags. Unfortunately I don't have time to work through your sqls but this is an example of grouping by labelling:

select UserId as "User" , InterestId as "User/InterestId" from
(select '1' as 'UserId', 'I1' as 'InterestId'
union all 
select '1' as 'UserId', 'I2' as 'InterestId') x
for xml path(''), root('Users')

answered Jun 5, 2017 at 9:32

Jayvee

10.9k3 gold badges35 silver badges43 bronze badges

Collectives™ on Stack Overflow

get sql query result in xml format?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related