Generate single XML file from SQL Server with multiple results per customer

Question

I have a need to send an XML file to a vendor, containing specific data from our SQL Server database. I know that SQL Server does have integration with XML, but I'm unfamiliar with it, so what I decided to do was simply select the XML tags as concatenated text, with the fields I need in between. That works, but I then realized that solution won't suit our needs.

The reason is that, for a given customer, it is possible to have multiple results. And if there are multiple results, since SQL Server returns a separate row for each result, it would generate multiple files per customer as well. We need a single file for each customer.

This is the piece of the SQL in particular that would generate multiple results per customer:

-- 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>'

What we need to accomplish is to have the resulting XML output be something like this if there are multiples of the Term.Code field:

<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>`

Three separate <activity> tags, all within the same file, but with the rest of the XML having only one tag per customer. So, the full results would be like this:

<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>

Is this possible? Here is the SQL Query we'll use to generate all the raw data set:

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

Answer 1

There are two major flaws:

• Never create an XML via string concatenation. Just imagine, one of your text columns contains data like 'Do & Co'. The & would break your XML's validity!
• Never use culture depending date formats! I did not know, whether your date values are the second of January or the first of February. Always use a secure and independant format such as ISO8601 (especially within XML)!

Try it like this:

There's a mockup scenario which you'll have to adapt to your needs. The basic idea is to use FOR XML PATH() for the billing data and another sub-select - again with FOR XML PATH(),TYPE for the related (nested) activities.

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');

The result

<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>