我需要向供应商发送一个 XML 文件，其中包含来 self 们 SQL Server 数据库的特定数据。我知道 SQL Server 确实与 XML 集成，但我不熟悉它，所以我决定做的只是选择 XML 标记作为连接文本，中间有我需要的字段。这行得通，但我随后意识到该解决方案不适合我们的需求。

原因是，对于给定的客户，可能有多个结果。如果有多个结果，由于 SQL Server 为每个结果返回一个单独的行，它也会为每个客户生成多个文件。我们需要为每个客户提供一个文件。

这是专门为每个客户生成多个结果的 SQL 片段:

-- Carriage return in variable for repeated use
declare @cr varchar(2) = char(13) + char(10)`

'<activity>' + @cr +
'<activity_date>' + CONVERT(VARCHAR(10), GETDATE(), 101) + '</activity_date>' + @cr +
'<activity_desc>' + Term.Code + ' Remaining Balance' + '</activity_desc>' + @cr +
'<charge>' + bal.Balance + '</charge>' + @cr + 
'</activity>'

我们需要完成的是，如果有多个 Term.Code 字段，则生成的 XML 输出如下所示:

<activity> 
  <activity_date>02/01/2018</activity_date>
  <activity_desc>WINTER17D     Remaining Balance</activity_desc> 
  <charge>50.00</charge> 
</activity> 
<activity> 
  <activity_date>02/01/2018</activity_date> 
  <activity_desc>SPRING18A    Remaining Balance</activity_desc> 
  <charge>15.00</charge> 
</activity>
<activity> 
  <activity_date>02/01/2018</activity_date> 
  <activity_desc>SPRING18B   Remaining Balance</activity_desc> 
  <charge>20.00</charge> 
</activity>`

三个独立的<activity>标签，都在同一个文件中，但 XML 的其余部分每个客户只有一个标签。所以，完整的结果是这样的:

<Billing> 
<stmt_date>02/01/2018</stmt_date> 
<id>987654</id> 
<total_due>85.00</total_due> 
<name>Test Person</name> 
<message>You have an outstanding balance.</message>
<activity> 
  <activity_date>02/01/2018</activity_date>
  <activity_desc>WINTER17D     Remaining Balance</activity_desc> 
  <charge>50.00</charge> 
</activity>
<activity> 
  <activity_date>02/01/2018</activity_date> 
  <activity_desc>SPRING18A    Remaining Balance</activity_desc> 
  <charge>15.00</charge> 
</activity>
<activity> 
  <activity_date>02/01/2018</activity_date> 
  <activity_desc>SPRING18B   Remaining Balance</activity_desc> 
  <charge>20.00</charge> 
</activity>
</Billing>

这可能吗？这是我们将用于生成所有原始数据集的 SQL 查询:

SELECT  RTRIM(Person.PersonID) AS PersonID,
   CONVERT(VARCHAR(10), GETDATE(), 101) AS StatementDate,    
  bal.Balance,
  RTRIM(Person.StuNum) AS PersonNum,
  RTRIM(Person.Email) AS Email,
  Person.Addr1,
  Term.Code AS Term
FROM   Person(nolock)
  JOIN Enrollment(nolock)
    ON Person.PersonID = Enrollment.PersonID
  JOIN AcctStatus(nolock)
    ON Enrollment.EnrollmentID = AcctStatus.EnrollmentID
       AND AcctStatus.SaAcctStatusID NOT IN(5, 16)
  JOIN
(
   SELECT EnrollmentID,
        TermID,
        CAST(SUM(CASE Type
                   WHEN 'I'
                   THEN Amount
                   WHEN 'D'
                   THEN Amount
                   ELSE Amount * -1
                END) AS VARCHAR(10)) AS Balance
   FROM   Ledger
   GROUP BY EnrollmentID,
          TermID
) AS bal
    ON bal.EnrollmentID = Enrollment.EnrollmentID
       AND bal.Balance <> '0.00'
  JOIN Term(nolock)
    ON Term.TermID = bal.TermID
       AND Term.TermID NOT IN(105, 34, 60, 194, 193, 97, 74)
ORDER BY Person.PersonID,
    bal.TermID

最佳答案

主要有两个缺陷:

• 切勿通过字符串连接创建 XML。试想一下，您的其中一个文本列包含诸如 'Do & Co' 之类的数据。 & 会破坏您的 XML 的有效性!
• 切勿使用依赖于文化的日期格式!我不知道您的日期值是一月二日还是二月一日。始终使用安全和独立格式，例如ISO8601(尤其是在 XML 中)!

像这样尝试:

有一个模型场景，您必须根据自己的需要进行调整。基本思想是使用 FOR XML PATH() 用于计费数据和另一个子选择 - 再次使用 FOR XML PATH(),TYPE 用于相关(嵌套)事件。

DECLARE @mockupBilling TABLE(StatementDate DATE, ID INT, Balance DECIMAL(10,4),Name VARCHAR(100),Msg VARCHAR(MAX));
INSERT INTO @mockupBilling VALUES(GETDATE(),987654,85.0,'Test Person','You have an outstanding balance.');

DECLARE @mockupActivity TABLE(BillingID INT, ActivityDate DATE, Activity VARCHAR(100), Charge DECIMAL(10,4));
INSERT INTO @mockupActivity VALUES
 (987654,{d'2018-01-02'},'WINTER17D Remaining Balance',50.0)
,(987654,{d'2018-01-03'},'SPRING18A Remaining Balance',15.0);

SELECT b.StatementDate AS [stmt_date]
      ,b.ID AS [id]
      ,b.Balance AS [total_due]
      ,b.Name AS [name]
      ,b.Msg AS [messge]
      ,(
        SELECT a.ActivityDate AS [activity_date]
              ,a.Activity AS [activity_desc]
              ,a.Charge AS [charge]
        FROM @mockupActivity AS a
        WHERE a.BillingID=b.ID
        ORDER BY a.ActivityDate
        FOR XML PATH('activity'),TYPE
       ) 
FROM @mockupBilling AS b
FOR XML PATH('Billing');

结果

<Billing>
  <stmt_date>2018-02-02</stmt_date>
  <id>987654</id>
  <total_due>85.0000</total_due>
  <name>Test Person</name>
  <messge>You have an outstanding balance.</messge>
  <activity>
    <activity_date>2018-01-02</activity_date>
    <activity_desc>WINTER17D Remaining Balance</activity_desc>
    <charge>50.0000</charge>
  </activity>
  <activity>
    <activity_date>2018-01-03</activity_date>
    <activity_desc>SPRING18A Remaining Balance</activity_desc>
    <charge>15.0000</charge>
  </activity>
</Billing>

关于sql-server - 从 SQL Server 为每个客户生成多个结果的单个 XML 文件，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/48572649/