返回重复列的嵌套XML

| 我有一个看起来像这样的表，适当地放置了代码（尤其是XML结构和查询） 
Class Subclass   Information  Details
Classx subclassx Info1        otherinfo1 
Classx subclassx Info2
Classx subclassy info3 
Classyy subclassyyy info3
例如，我需要返回用Class = \'classx \'过滤的行，返回： 
<Class Name=\"Classx\">
  <Subclass subclassName =\"subclassx\">
    <Info>Info1</Info>
    <Info>Info2</Info>
  </Subclass>  
 <Subclass subclassName =\"subclassy\">
    <Info>Info3</Info>
  </Subclass>  
</Class>
（要么） 
<Class Name=\"Classx\">
  <Subclass>subclassx
    <Info>Info1</Info>
    <Info>Info2</Info>
  </Subclass>
 <Subclass>subclassy
   <Info>Info3</Info>
 </Subclass>
</Class>
将\'Subclass \'嵌套在\'Class \'元素内，并将\'Info \'嵌套在\'Subclass \'元素内的东西。 尝试了一些查询： 
DECLARE @classFilter nvarchar(15);
SELECT @classFilter = \'Classx\' 

     SELECT @classFilter as \'@Class\',
              (SELECT  r1.subClass as \'SubClass\',
               (SELECT  DISTINCT r1.Info as \'Info\' 
                   FROM @results as r2 
                   WHERE r2.Class=  @classFilter and r1.Subclass = r2.Subclass
                    FOR XML PATH(\'Information\'), TYPE
                    )
               FROM @results r1 
              GROUP BY r1.Class, r1.SubClass, r1.Info
              HAVING r1.Class= @classFilter
                FOR XML PATH(\'Subclasses\'), TYPE)
           FOR XML PATH(\'Class\'), TYPE;
给出： 
<Class Class=\"Classx\">
  <Subclasses>
    <SubClass>subclassx</SubClass>
    <Information>
      <Info>Info1</Info>
    </Information>  
  </Subclasses>
 <Subclasses>
    <SubClass>subclassx</SubClass>
    <Information>
      <Info>Info2</Info>
    </Details>  
  </Subclasses>
<Subclasses>
    <SubClass>subclassy</SubClass>
    <Information>
      <Info>Info3</Info>
    </Information>  
  </Subclasses>
</Class>
我能找到的最接近的是 
 SELECT @classfilter as \'@Name\',    --or r1.Class
        (SELECT r1.Subclass as \'@Subclass\', 
            (SELECT DISTINCT r1.Information as \'Information\' 
               FROM @results as r2 
               WHERE r2.Class =  @classFilter and r1.Subclass = r2.Subclass         
                )
           FROM @results r1           
          GROUP BY r1.Class, r1.Subclass, r1.Information
          HAVING r1.Class= @classFilter 
            FOR XML PATH(\'Subclasses\'), TYPE)
       FOR XML PATH(\'Class\'), TYPE;
给出： 
 <Class Name=\"classx\">
      <Subclasses subclass=\"subclassx\">Info1</Subclasses>
      <Subclasses subclass=\"subclassx\">Info2</Subclasses>
      <Subclasses subclass=\"subclassy\">Info3</Subclasses>
    </Class>
想知道我缺少什么！     
2020-01-24 2 条评论
分享

没有找到相关结果

    已邀请:

    为陡土 

    declare @T table
(
  Class varchar(10),
  Subclass varchar(10),
  Information varchar(10),
  Details varchar(10)
)

insert into @T 
select \'Classx\', \'subclassx\', \'Info1\', \'otherinfo1\' union all 
select \'Classx\', \'subclassx\', \'Info2\', \'\' union all
select \'Classx\', \'subclassy\', \'info3\', \'\'  union all
select \'Classyy\', \'subclassyy\', \'info3\', \'\' 

declare @class varchar(10)
set @class = \'Classx\'

select 
  T.Class as \'@Name\',
  (select
     T2.Subclass as \'@subclassName\',
     (select
        T3.Information as \'Info\'
      from @T as T3
      where T3.Subclass = T2.Subclass and
            T3.Class = T.Class
      for xml path(\'\'), type)
   from @T as T2
   where T.Class = T2.Class
   group by T2.Subclass
   for xml path(\'Subclass\'), type)
from @T as T
where T.Class = @class
group by T.Class
for xml path(\'Class\')
        
    2020-01-24 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.049554109573364