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.
您好我正在尝试在sql server中以xml格式获取结果但我没有得到propare xml格式的许多数据,它以xml格式重复。我已经在sql server中编写了这个查询,但没有得到propare数据。任何人都知道我的错误在哪里,请让我知道怎么做。
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 =>
我的预期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.
我希望像xml格式的这些数据任何人知道请告诉我。
2 个解决方案
#1
2
I have an example for your case. You could use TYPE
to get nested xml
我举个例子。您可以使用TYPE来获取嵌套的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,'1@abc.xyz','User1', 100, 'Description 100'),
(1,'1@abc.xyz','User1', 101, 'Description 101'),
(1,'1@abc.xyz','User1', 102, 'Description 102'),
(1,'1@abc.xyz','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>1@abc.xyz</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
演示链接:http://rextester.com/KKEHCR86171
#2
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:
我们的想法是通过标记路径标记为xml提供所需的格式。不幸的是,我没有时间通过你的sqls工作,但这是一个通过标记分组的例子:
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')
#1
2
I have an example for your case. You could use TYPE
to get nested xml
我举个例子。您可以使用TYPE来获取嵌套的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,'1@abc.xyz','User1', 100, 'Description 100'),
(1,'1@abc.xyz','User1', 101, 'Description 101'),
(1,'1@abc.xyz','User1', 102, 'Description 102'),
(1,'1@abc.xyz','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>1@abc.xyz</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
演示链接:http://rextester.com/KKEHCR86171
#2
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:
我们的想法是通过标记路径标记为xml提供所需的格式。不幸的是,我没有时间通过你的sqls工作,但这是一个通过标记分组的例子:
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')