Search code examples
sql-serverxmlt-sqlxquery

Generate SQL query results in specific XML format

I'm looking to generate an XML output in the following example format:

<?xml version="1.0" encoding="utf-8"?>
<PSD006-ShortTermLoans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:fsa-gov-uk:MER:PSD006:1 PSD006-Schema.xsd" xmlns="urn:fsa-gov-uk:MER:PSD006:1">
<PSDFeedHeader>
  <Submitter>
   <SubmittingFirm>713984</SubmittingFirm>
  </Submitter>
  <ReportDetails>
   <ReportCreationDate>2020-04-23</ReportCreationDate>
   <ReportIdentifier>PSD006_713984__50</ReportIdentifier>
  </ReportDetails>
</PSDFeedHeader>
<PSD006FeedMsg>
  <CoreItems>
   <FirmReferenceNumber>713984</FirmReferenceNumber>
   <TransRef>713984_D000147827</TransRef>
   <Cancellation>false</Cancellation>
  </CoreItems>
  <ShortTermLoans>
   <TransactionDate>2020-03-21</TransactionDate>
   <LoanAmount>1000</LoanAmount>
   <LoanType>HCST</LoanType>
   <APR>399.41</APR>
   <ArrangementFee>0</ArrangementFee>
   <TotalAmountPayable>1834</TotalAmountPayable>
   <Rollover>N</Rollover>
   <OrderOfRollover>0</OrderOfRollover>
   <LengthOfTerm>272</LengthOfTerm>
   <ReasonForLoan>P</ReasonForLoan>
   <DOBOfBorrower>1979-10-07</DOBOfBorrower>
   <PostCode>N14 6HE</PostCode>
   <MonthlyIncomeOfBorrower>3166</MonthlyIncomeOfBorrower>
   <MaritalStatusOfBorrower>O</MaritalStatusOfBorrower>
   <ResidentialStatusOfBorrower>X</ResidentialStatusOfBorrower>
   <EmploymentStatusOfBorrower>EF</EmploymentStatusOfBorrower>
  </ShortTermLoans>
</PSD006FeedMsg>
</PSD006-ShortTermLoans>

This is sample data which is required in the XML format above.

    CREATE TABLE #Loans ( [Transref] varchar(16), [TransactionDate] date, [OriginalAdvance] money, [LoanType] varchar(4), [ContractualAPR] float(8), [OriginalProjectedTotalRepayable] money, [OriginalTerm] int, [ReasonforLoan] varchar(4), [DOB] date, [Postcode] varchar(8), [Salary] int, [SalaryFrequency] int, [ResidentialStatus] int, [EmploymentStatus] int )
INSERT INTO #Loans
VALUES
( '11', N'2020-06-22T00:00:00', 300.0000, 'HCST', 790, 1000.0000, 6, 'HCST', N'1999-01-31T00:00:00', 'HG2 9DX', 20000, 1508, 1486, 470 ), 
( '12', N'2020-06-22T00:00:00', 1000.0000, 'HCST', 99.9, 1000.0000, 12, 'HCST', N'1999-01-31T00:00:00', 'HG2 9DX', 20000, 1508, 1486, 470 ), 
( '13', N'2020-06-22T00:00:00', 1000.0000, 'HCST', 99.9, 1000.0000, 12, 'HCST', N'1999-01-31T00:00:00', 'HG2 9DX', 20000, 1508, 1486, 470 ), 
( '14', N'2020-06-22T00:00:00', 1000.0000, 'HCST', 99.9, 1000.0000, 12, 'HCST', N'1999-01-31T00:00:00', 'HG2 9DX', 20000, 1508, 1486, 470 ), 
( '17', N'2020-08-15T00:00:00', 750.0000, 'HCST', 392.71, 1000.0000, 9, 'HCST', N'1995-01-01T00:00:00', 'SE21 8NZ', 15000, 1508, 1472, 449 ), 
( '17', N'2020-08-15T00:00:00', 750.0000, 'HCST', 392.71, 1000.0000, 9, 'HCST', N'1995-01-01T00:00:00', 'SE21 8NZ', 15000, 1508, 1472, 449 ), 
( '20', N'2020-07-30T00:00:00', 800.0000, 'HCST', 399.4, 1000.0000, 9, 'HCST', N'1985-03-12T00:00:00', 'EH21 6UH', 21000, 1499, 1477, 500 ), 
( NULL, N'2020-08-05T00:00:00', 1000.0000, 'HCST', 790, 1000.0000, 12, 'HCST', N'1980-07-07T00:00:00', 'BD1 3LY', 2000, 1508, 1481, 470 ), 
( '37', N'2020-08-22T00:00:00', 300.0000, 'HCST', 792.4, 1000.0000, 6, 'HCST', N'1999-01-31T00:00:00', 'HG2 9DX', 35000, 1508, NULL, 470 ), 
( '38', N'2020-08-19T00:00:00', 1000.0000, 'HCST', 790, 1000.0000, 12, 'HCST', N'1990-07-07T00:00:00', 'BD1 3LY', 2000, 1508, 1481, 470 )

This is my query so far:

     SELECT 

        (

           SELECT '99999' AS Submitter

           FOR XML PATH('SubmittingFirm'), ROOT ('PSDFeedHeader'), TYPE

       ),
       (
           SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS ReportCreationDate,
           'PSD006_713984__51' AS ReportIdentifier
                  
           FOR XML PATH('ReportDetails'),  ROOT ('PSDFeedHeader'), TYPE
       ),


       
       (
           SELECT TOP 1 * FROM #Loans FOR XML PATH('ShortTermLoans'), TYPE
       )

FOR XML RAW(''), ROOT('PSD006-ShortTermLoans'), ELEMENTS XSINIL;

Which gives the XML below and as you can see I've made some progress however I'm having difficulty nesting the selects. For example the Submitter and ReportDetails node should be nested within the PSDFeedHeader node.

       <PSD006-ShortTermLoans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <PSDFeedHeader>
    <SubmittingFirm>
      <Submitter>99999</Submitter>
    </SubmittingFirm>
  </PSDFeedHeader>
  <PSDFeedHeader>
    <ReportDetails>
      <ReportCreationDate>24/09/2020</ReportCreationDate>
      <ReportIdentifier>PSD006_713984__51</ReportIdentifier>
    </ReportDetails>
  </PSDFeedHeader>
  <ShortTermLoans>
    <Transref>11</Transref>
    <TransactionDate>2020-06-22</TransactionDate>
    <OriginalAdvance>300.0000</OriginalAdvance>
    <LoanType>HCST</LoanType>
    <ContractualAPR>7.9000000e+002</ContractualAPR>
    <OriginalProjectedTotalRepayable>1000.0000</OriginalProjectedTotalRepayable>
    <OriginalTerm>6</OriginalTerm>
    <ReasonforLoan>HCST</ReasonforLoan>
    <DOB>1999-01-31</DOB>
    <Postcode>HG2 9DX</Postcode>
    <Salary>20000</Salary>
    <SalaryFrequency>1508</SalaryFrequency>
    <ResidentialStatus>1486</ResidentialStatus>
    <EmploymentStatus>470</EmploymentStatus>
  </ShortTermLoans>
</PSD006-ShortTermLoans>
Solution

  • Here is a method to construct desired XML via XQuery and FLWOR expression.

    If needed, you can apply the same method of using SQL variables for the rest of the hard-coded values inside XML.

    SQL

    -- DDL and sample data population, start
DECLARE @loans TABLE ( [Transref] varchar(16), [TransactionDate] date, [OriginalAdvance] money, [LoanType] varchar(4), [ContractualAPR] float(8), [OriginalProjectedTotalRepayable] money, [OriginalTerm] int, [ReasonforLoan] varchar(4), [DOB] date, [Postcode] varchar(8), [Salary] int, [SalaryFrequency] int, [ResidentialStatus] int, [EmploymentStatus] int )
INSERT INTO @Loans
VALUES
( '11', N'2020-06-22T00:00:00', 300.0000, 'HCST', 790, 1000.0000, 6, 'HCST', N'1999-01-31T00:00:00', 'HG2 9DX', 20000, 1508, 1486, 470 ), 
( '12', N'2020-06-22T00:00:00', 1000.0000, 'HCST', 99.9, 1000.0000, 12, 'HCST', N'1999-01-31T00:00:00', 'HG2 9DX', 20000, 1508, 1486, 470 ), 
( '13', N'2020-06-22T00:00:00', 1000.0000, 'HCST', 99.9, 1000.0000, 12, 'HCST', N'1999-01-31T00:00:00', 'HG2 9DX', 20000, 1508, 1486, 470 ), 
( '14', N'2020-06-22T00:00:00', 1000.0000, 'HCST', 99.9, 1000.0000, 12, 'HCST', N'1999-01-31T00:00:00', 'HG2 9DX', 20000, 1508, 1486, 470 ), 
( '17', N'2020-08-15T00:00:00', 750.0000, 'HCST', 392.71, 1000.0000, 9, 'HCST', N'1995-01-01T00:00:00', 'SE21 8NZ', 15000, 1508, 1472, 449 ), 
( '17', N'2020-08-15T00:00:00', 750.0000, 'HCST', 392.71, 1000.0000, 9, 'HCST', N'1995-01-01T00:00:00', 'SE21 8NZ', 15000, 1508, 1472, 449 ), 
( '20', N'2020-07-30T00:00:00', 800.0000, 'HCST', 399.4, 1000.0000, 9, 'HCST', N'1985-03-12T00:00:00', 'EH21 6UH', 21000, 1499, 1477, 500 ), 
( NULL, N'2020-08-05T00:00:00', 1000.0000, 'HCST', 790, 1000.0000, 12, 'HCST', N'1980-07-07T00:00:00', 'BD1 3LY', 2000, 1508, 1481, 470 ), 
( '37', N'2020-08-22T00:00:00', 300.0000, 'HCST', 792.4, 1000.0000, 6, 'HCST', N'1999-01-31T00:00:00', 'HG2 9DX', 35000, 1508, NULL, 470 ), 
( '38', N'2020-08-19T00:00:00', 1000.0000, 'HCST', 790, 1000.0000, 12, 'HCST', N'1990-07-07T00:00:00', 'BD1 3LY', 2000, 1508, 1481, 470 );
-- DDL and sample data population, end

DECLARE @SubmittingFirm VARCHAR(20) = '713984'
    , @ReportCreationDate DATE = GETDATE()
    , @Cancellation VARCHAR(10) = 'false';

;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' AS [xsi]
    , DEFAULT 'urn:fsa-gov-uk:MER:PSD006:1')
SELECT (
SELECT TOP(1) * FROM @loans
FOR XML PATH('r'), TYPE, ROOT('root')
).query('<PSD006-ShortTermLoans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                       xsi:schemaLocation="urn:fsa-gov-uk:MER:PSD006:1 PSD006-Schema.xsd"
                       xmlns="urn:fsa-gov-uk:MER:PSD006:1">
<PSDFeedHeader>
    <Submitter>
        <SubmittingFirm>{sql:variable("@SubmittingFirm")}</SubmittingFirm>
    </Submitter>
    <ReportDetails>
        <ReportCreationDate>{sql:variable("@ReportCreationDate")}</ReportCreationDate>
        <ReportIdentifier>PSD006_713984__50</ReportIdentifier>
    </ReportDetails>
</PSDFeedHeader>
<PSD006FeedMsg>
    <CoreItems>
        <FirmReferenceNumber>{sql:variable("@SubmittingFirm")}</FirmReferenceNumber>
        <TransRef>713984_D000147827</TransRef>
        <Cancellation>{sql:variable("@Cancellation")}</Cancellation>
    </CoreItems>
{
    for $x in /root/r
    return <ShortTermLoans>{$x/*}</ShortTermLoans>
}
</PSD006FeedMsg>
</PSD006-ShortTermLoans>');

    Output

    <PSD006-ShortTermLoans xmlns="urn:fsa-gov-uk:MER:PSD006:1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:fsa-gov-uk:MER:PSD006:1 PSD006-Schema.xsd">
  <PSDFeedHeader>
    <Submitter>
      <SubmittingFirm>713984</SubmittingFirm>
    </Submitter>
    <ReportDetails>
      <ReportCreationDate>2020-09-24</ReportCreationDate>
      <ReportIdentifier>PSD006_713984__50</ReportIdentifier>
    </ReportDetails>
  </PSDFeedHeader>
  <PSD006FeedMsg>
    <CoreItems>
      <FirmReferenceNumber>713984</FirmReferenceNumber>
      <TransRef>713984_D000147827</TransRef>
      <Cancellation>false</Cancellation>
    </CoreItems>
    <ShortTermLoans>
      <Transref>11</Transref>
      <TransactionDate>2020-06-22</TransactionDate>
      <OriginalAdvance>300.0000</OriginalAdvance>
      <LoanType>HCST</LoanType>
      <ContractualAPR>7.9000000e+002</ContractualAPR>
      <OriginalProjectedTotalRepayable>1000.0000</OriginalProjectedTotalRepayable>
      <OriginalTerm>6</OriginalTerm>
      <ReasonforLoan>HCST</ReasonforLoan>
      <DOB>1999-01-31</DOB>
      <Postcode>HG2 9DX</Postcode>
      <Salary>20000</Salary>
      <SalaryFrequency>1508</SalaryFrequency>
      <ResidentialStatus>1486</ResidentialStatus>
      <EmploymentStatus>470</EmploymentStatus>
    </ShortTermLoans>
  </PSD006FeedMsg>
</PSD006-ShortTermLoans>