xml - 使用一个表 (sql) 选择 XML 循环节点

Question

我只是有以下情况:我需要从 SQL 表创建一些 XML 文档，但经过多次尝试后，我只有一个不符合我需要的结果。
这是我要咨询的一张表的示例:

CREATE TABLE [dbo].[CROMULTITEST02]( [idCli] [numeric](18, 0) NULL, [Name] [varchar](50) NULL, [Apepat] [varchar](50) NULL, [Apemat] [varchar](50) NULL, [CtaCtble] [numeric](18, 0) NULL, [SdoCtble] [numeric](18, 0) NULL, [Date] [datetime] NULL, [Texto] [varchar](50) NULL ) ON [PRIMARY]

INSERT [dbo].[CROMULTITEST02] ([idCli], [Name], [Apepat], [Apemat], [CtaCtble], [SdoCtble], [Date], [Texto]) VALUES (CAST(1 AS Numeric(18, 0)), N'Fernando', N'Fernandez', N'Ferguson', CAST(1234 AS Numeric(18, 0)), CAST(1000 AS Numeric(18, 0)), CAST(0x00009DA000000000 AS DateTime), N'Hola')
INSERT [dbo].[CROMULTITEST02] ([idCli], [Name], [Apepat], [Apemat], [CtaCtble], [SdoCtble], [Date], [Texto]) VALUES (CAST(2 AS Numeric(18, 0)), N'Gonzalo', N'Gonzales', N'Gonzaga', CAST(2345 AS Numeric(18, 0)), CAST(1344 AS Numeric(18, 0)), CAST(0x00009DDB00000000 AS DateTime), N'Hi')
INSERT [dbo].[CROMULTITEST02] ([idCli], [Name], [Apepat], [Apemat], [CtaCtble], [SdoCtble], [Date], [Texto]) VALUES (CAST(3 AS Numeric(18, 0)), N'Ramiro', N'Ramirez', N'Ramsom', CAST(3456 AS Numeric(18, 0)), CAST(2333 AS Numeric(18, 0)), CAST(0x00009DEB00000000 AS DateTime), N'Hallo')
INSERT [dbo].[CROMULTITEST02] ([idCli], [Name], [Apepat], [Apemat], [CtaCtble], [SdoCtble], [Date], [Texto]) VALUES (CAST(4 AS Numeric(18, 0)), N'Rosa', N'Rosales', N'Del Prado', CAST(4567 AS Numeric(18, 0)), CAST(5555 AS Numeric(18, 0)), CAST(0x00009FB100000000 AS DateTime), N'Hello')
INSERT [dbo].[CROMULTITEST02] ([idCli], [Name], [Apepat], [Apemat], [CtaCtble], [SdoCtble], [Date], [Texto]) VALUES (CAST(5 AS Numeric(18, 0)), N'Sancho', N'Sanchez', N'S.', CAST(5678 AS Numeric(18, 0)), CAST(6788 AS Numeric(18, 0)), CAST(0x00009ECA00000000 AS DateTime), N'Bye')
INSERT [dbo].[CROMULTITEST02] ([idCli], [Name], [Apepat], [Apemat], [CtaCtble], [SdoCtble], [Date], [Texto]) VALUES (CAST(6 AS Numeric(18, 0)), N'Martín', N'Martinez', N'Mortensen', CAST(6789 AS Numeric(18, 0)), CAST(4400 AS Numeric(18, 0)), CAST(0x00009F0600000000 AS DateTime), N'Goodbye')
INSERT [dbo].[CROMULTITEST02] ([idCli], [Name], [Apepat], [Apemat], [CtaCtble], [SdoCtble], [Date], [Texto]) VALUES (CAST(7 AS Numeric(18, 0)), N'Armando', N'Armas', N'Gunn', CAST(7890 AS Numeric(18, 0)), CAST(1200 AS Numeric(18, 0)), CAST(0x00009F7700000000 AS DateTime), N'Au Revoir')
INSERT [dbo].[CROMULTITEST02] ([idCli], [Name], [Apepat], [Apemat], [CtaCtble], [SdoCtble], [Date], [Texto]) VALUES (CAST(1 AS Numeric(18, 0)), N'Fernando', N'Fernandez', N'Ferguson', CAST(4321 AS Numeric(18, 0)), CAST(3000 AS Numeric(18, 0)), CAST(0x00009EC400000000 AS DateTime), N'Danke')
INSERT [dbo].[CROMULTITEST02] ([idCli], [Name], [Apepat], [Apemat], [CtaCtble], [SdoCtble], [Date], [Texto]) VALUES (CAST(4 AS Numeric(18, 0)), N'Rosa', N'Rosales', N'Del Prado', CAST(7654 AS Numeric(18, 0)), CAST(1560 AS Numeric(18, 0)), CAST(0x00009F3000000000 AS DateTime), NULL)

我的 sql for xml 查询是这样的:

SELECT CROM1.[idCli] AS '@IDCli'
,CROM1.[Name] AS 'Name'
,CROM1.[Apepat] AS 'ApePat'
,CROM1.[Apemat] AS 'ApeMat'
,(SELECT CtaCtble AS '@CtaCtable'
    ,(SELECT SdoCtble as 'SdoCtble'
        FROM [CROMULTITEST02] AS CROM3 
        WHERE CROM3.CtaCtble=CROM2.CtaCtble AND CROM3.IDCLI=CROM1.IDCLI
        FOR XML path(''), TYPE
    )FROM [CROMULTITEST02] AS CROM2
    WHERE CROM2.IDCLI=CROM1.IDCLI
    GROUP BY CROM2.CtaCtble
    FOR XML PATH('InfoCtble'), TYPE
)
FROM [CROMULTITEST02] AS CROM1
GROUP BY [idCli],[Name],[Apepat],[Apemat]
FOR XML PATH('OpCli'), ROOT('OperIndiv'), TYPE

但输出是:

<OperIndiv>
  <OpCli IDCli="1">
    <Name>Fernando</Name>
    <ApePat>Fernandez</ApePat>
    <ApeMat>Ferguson</ApeMat>
    <InfoCtble CtaCtable="1234">
      <SdoCtble>1000</SdoCtble>
    </InfoCtble>
    <InfoCtble CtaCtable="4321">
      <SdoCtble>3000</SdoCtble>
    </InfoCtble>
  </OpCli>
  <OpCli IDCli="2">
    <Name>Gonzalo</Name>
    <ApePat>Gonzales</ApePat>
    <ApeMat>Gonzaga</ApeMat>
    <InfoCtble CtaCtable="2345">
      <SdoCtble>1344</SdoCtble>
    </InfoCtble>
  </OpCli>
  <OpCli IDCli="3">
    <Name>Ramiro</Name>
    <ApePat>Ramirez</ApePat>
    <ApeMat>Ramsom</ApeMat>
    <InfoCtble CtaCtable="3456">
      <SdoCtble>2333</SdoCtble>
    </InfoCtble>
  </OpCli>
  <OpCli IDCli="4">
    <Name>Rosa</Name>
    <ApePat>Rosales</ApePat>
    <ApeMat>Del Prado</ApeMat>
    <InfoCtble CtaCtable="4567">
      <SdoCtble>5555</SdoCtble>
    </InfoCtble>
    <InfoCtble CtaCtable="7654">
      <SdoCtble>1560</SdoCtble>
    </InfoCtble>
  </OpCli>
  <OpCli IDCli="5">
    <Name>Sancho</Name>
    <ApePat>Sanchez</ApePat>
    <ApeMat>S.</ApeMat>
    <InfoCtble CtaCtable="5678">
      <SdoCtble>6788</SdoCtble>
    </InfoCtble>
  </OpCli>
  <OpCli IDCli="6">
    <Name>Martín</Name>
    <ApePat>Martinez</ApePat>
    <ApeMat>Mortensen</ApeMat>
    <InfoCtble CtaCtable="6789">
      <SdoCtble>4400</SdoCtble>
    </InfoCtble>
  </OpCli>
  <OpCli IDCli="7">
    <Name>Armando</Name>
    <ApePat>Armas</ApePat>
    <ApeMat>Gunn</ApeMat>
    <InfoCtble CtaCtable="7890">
      <SdoCtble>1200</SdoCtble>
    </InfoCtble>
  </OpCli>
</OperIndiv>

我真正想要的是这样的:

<OperIndiv>
  <OpCli IDCli="1">
    <Name>Fernando</Name>
    <ApePat>Fernandez</ApePat>
    <ApeMat>Ferguson</ApeMat>
    <InfoCtble CtaCtable="1234">
      <SdoCtble CtaCtable="1234">1000</SdoCtble>
      <SdoCtble CtaCtable="4321">3000</SdoCtble>
    </InfoCtble>
  </OpCli>
  <OpCli IDCli="2">
    <Name>Gonzalo</Name>
    <ApePat>Gonzales</ApePat>
    <ApeMat>Gonzaga</ApeMat>
    <InfoCtble CtaCtable="2345">
      <SdoCtble>1344</SdoCtble>
    </InfoCtble>
  </OpCli>
  <OpCli IDCli="3">
    <Name>Ramiro</Name>
    <ApePat>Ramirez</ApePat>
    <ApeMat>Ramsom</ApeMat>
    <InfoCtble CtaCtable="3456">
      <SdoCtble>2333</SdoCtble>
    </InfoCtble>
  </OpCli>
  <OpCli IDCli="4">
    <Name>Rosa</Name>
    <ApePat>Rosales</ApePat>
    <ApeMat>Del Prado</ApeMat>
    <InfoCtble>
      <SdoCtble CtaCtable="4567">5555</SdoCtble>
      <SdoCtble CtaCtable="7654">1560</SdoCtble>
    </InfoCtble>
  </OpCli>
  <OpCli IDCli="5">
    <Name>Sancho</Name>
    <ApePat>Sanchez</ApePat>
    <ApeMat>S.</ApeMat>
    <InfoCtble CtaCtable="5678">
      <SdoCtble>6788</SdoCtble>
    </InfoCtble>
  </OpCli>
  <OpCli IDCli="6">
    <Name>Martín</Name>
    <ApePat>Martinez</ApePat>
    <ApeMat>Mortensen</ApeMat>
    <InfoCtble CtaCtable="6789">
      <SdoCtble>4400</SdoCtble>
    </InfoCtble>
  </OpCli>
  <OpCli IDCli="7">
    <Name>Armando</Name>
    <ApePat>Armas</ApePat>
    <ApeMat>Gunn</ApeMat>
    <InfoCtble CtaCtable="7890">
      <SdoCtble>1200</SdoCtble>
    </InfoCtble>
  </OpCli>
</OperIndiv>

我需要在 <InfoCtble> 中进行不同的节点分组。我希望你能帮助我。
提前致谢。

Answer 1

;with C as 
(
  select *,
         row_number() over(partition by idCli order by [Date]) as rn
  from CROMULTITEST02
)
select C1.idCli as '@IDCli',
       C1.Name,
       C1.Apepat,
       C1.Apemat,
       (select C1.CtaCtble as '@CtaCtable', 
               (select C2.CtaCtble as 'SdoCtble/@CtaCtable',
                       C2.SdoCtble as 'SdoCtble'
                from C as C2
                where C1.idCli = C2.idCli
                order by C2.rn
                for xml path(''), type)
        for xml path('InfoCtble'), type)        
from C as C1
where C1.rn = 1    
for xml path('OpCli'), root('OperIndiv')