Get SQL Results In XML Format Using xQuery

Question

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

<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-07-14</ReportCreationDate>
   <ReportIdentifier>PSD006_713984__51</ReportIdentifier>
  </ReportDetails>
 </PSDFeedHeader>
 <PSD006FeedMsg>
  <CoreItems>
   <FirmReferenceNumber>713984</FirmReferenceNumber>
   <TransRef>713984_D000165940</TransRef>
   <Cancellation>false</Cancellation>
  </CoreItems>
  <ShortTermLoans>
   <TransactionDate>2029-06-16</TransactionDate>
   <LoanAmount>150</LoanAmount>
   <LoanType>HCST</LoanType>
   <APR>500.00</APR>
   <ArrangementFee>0</ArrangementFee>
   <TotalAmountPayable>246</TotalAmountPayable>
   <Rollover>N</Rollover>
   <OrderOfRollover>0</OrderOfRollover>
   <LengthOfTerm>101</LengthOfTerm>
   <ReasonForLoan>P</ReasonForLoan>
   <DOBOfBorrower>1999-08-26</DOBOfBorrower>
   <PostCode>HP3 XXX</PostCode>
   <MonthlyIncomeOfBorrower>1800</MonthlyIncomeOfBorrower>
   <MaritalStatusOfBorrower>O</MaritalStatusOfBorrower>
   <ResidentialStatusOfBorrower>X</ResidentialStatusOfBorrower>
   <EmploymentStatusOfBorrower>EF</EmploymentStatusOfBorrower>
  </ShortTermLoans>
 </PSD006FeedMsg>
 <PSD006FeedMsg>
  <CoreItems>
   <FirmReferenceNumber>713984</FirmReferenceNumber>
   <TransRef>713984_D000156085</TransRef>
   <Cancellation>false</Cancellation>
  </CoreItems>
  <ShortTermLoans>
   <TransactionDate>2020-04-30</TransactionDate>
   <LoanAmount>200</LoanAmount>
   <LoanType>HCST</LoanType>
   <APR>500.37</APR>
   <ArrangementFee>0</ArrangementFee>
   <TotalAmountPayable>282</TotalAmountPayable>
   <Rollover>N</Rollover>
   <OrderOfRollover>0</OrderOfRollover>
   <LengthOfTerm>77</LengthOfTerm>
   <ReasonForLoan>P</ReasonForLoan>
   <DOBOfBorrower>1992-08-29</DOBOfBorrower>
   <PostCode>BT10 XXX</PostCode>
   <MonthlyIncomeOfBorrower>1700</MonthlyIncomeOfBorrower>
   <MaritalStatusOfBorrower>O</MaritalStatusOfBorrower>
   <ResidentialStatusOfBorrower>X</ResidentialStatusOfBorrower>
   <EmploymentStatusOfBorrower>EF</EmploymentStatusOfBorrower>
  </ShortTermLoans>
 </PSD006FeedMsg>
 </PSD006-ShortTermLoans>

The output I'm getting is wrong. As you can see below, the CoreItems element is not showing for each record as per the example format above.

<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>2021-01-18</ReportCreationDate>
            <ReportIdentifier>PSD006_713984__50</ReportIdentifier>
        </ReportDetails>
    </PSDFeedHeader>
    <PSD006FeedMsg>
        <CoreItems>
            <FirmReferenceNumber>713984</FirmReferenceNumber>
            <TransRef>713984_D000147827</TransRef>
            <Cancellation>false</Cancellation>
        </CoreItems>
        <ShortTermLoans>
            <TransRef>1152538</TransRef>
            <TransactionDate>2020-12-31T23:51:17.453</TransactionDate>
            <LoanAmount>750.0000</LoanAmount>
            <LoanType>HCST</LoanType>
            <APR>4.950000000000000e+002</APR>
            <ArrangementFee>0</ArrangementFee>
            <TotalRepayable>713.1300</TotalRepayable>
            <Rollover>N</Rollover>
            <OrderOfRollovers>0</OrderOfRollovers>
            <LengthOfTerm>9</LengthOfTerm>
            <ReasonForLoan>P</ReasonForLoan>
            <DOBofBorrower>1993-03-07T00:00:00</DOBofBorrower>
            <Postcode>PA145UT</Postcode>
            <NetMonthlyIncome>0.0000</NetMonthlyIncome>
            <MaritalStatusOfBorrower>O</MaritalStatusOfBorrower>
            <ResidentialStatusOfBorrower>X</ResidentialStatusOfBorrower>
            <EmploymentStatusOfBorrower>EF</EmploymentStatusOfBorrower>
        </ShortTermLoans>
        <ShortTermLoans>
            <TransRef>1152537</TransRef>
            <TransactionDate>2020-12-31T23:42:17.337</TransactionDate>
            <LoanAmount>100.0000</LoanAmount>
            <LoanType>HCST</LoanType>
            <APR>1.095800000000000e+003</APR>
            <ArrangementFee>0</ArrangementFee>
            <TotalRepayable>96.0800</TotalRepayable>
            <Rollover>N</Rollover>
            <OrderOfRollovers>0</OrderOfRollovers>
            <LengthOfTerm>7</LengthOfTerm>
            <ReasonForLoan>P</ReasonForLoan>
            <DOBofBorrower>1964-02-22T00:00:00</DOBofBorrower>
            <Postcode>CM23 XXX</Postcode>
            <NetMonthlyIncome>0.0000</NetMonthlyIncome>
            <MaritalStatusOfBorrower>O</MaritalStatusOfBorrower>
            <ResidentialStatusOfBorrower>X</ResidentialStatusOfBorrower>
            <EmploymentStatusOfBorrower>EF</EmploymentStatusOfBorrower>
        </ShortTermLoans>
        <ShortTermLoans>
            <TransRef>1152536</TransRef>
            <TransactionDate>2029-12-31T23:40:18.573</TransactionDate>
            <LoanAmount>600.0000</LoanAmount>
            <LoanType>HCST</LoanType>
            <APR>4.950000000000000e+002</APR>
            <ArrangementFee>0</ArrangementFee>
            <TotalRepayable>570.5400</TotalRepayable>
            <Rollover>N</Rollover>
            <OrderOfRollovers>0</OrderOfRollovers>
            <LengthOfTerm>9</LengthOfTerm>
            <ReasonForLoan>P</ReasonForLoan>
            <DOBofBorrower>1989-07-26T00:00:00</DOBofBorrower>
            <Postcode>CW12 4WL</Postcode>
            <NetMonthlyIncome>0.0000</NetMonthlyIncome>
            <MaritalStatusOfBorrower>O</MaritalStatusOfBorrower>
            <ResidentialStatusOfBorrower>X</ResidentialStatusOfBorrower>
            <EmploymentStatusOfBorrower>EF</EmploymentStatusOfBorrower>
        </ShortTermLoans>

The query I have so far is:

-- 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(10) * 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>');

I've tried all manner of variations and cannot return the results with CoreItems showing for each loan.

A clarifying question. What is the source for the <TransRef>713984_D000147827</TransRef> element value? My understanding is that 713984 comes from a variable. But what about the 2nd part of the value after the underscore, i.e. like _D000147827 for each loan? — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Jan 20, 2021 at 18:37
In my query it's hardcoded, but it should be a variable for each loan using the Transref column. I didn't explain this to you properly the first time I asked this question... — TheCoxta
– TheCoxta, Commented Jan 20, 2021 at 18:51
To be clearer, the D number in the Transref element can be ignored. It should be the value of the transref column. — TheCoxta
– TheCoxta, Commented Jan 20, 2021 at 19:03

Yitzhak Khabinsky · Accepted Answer · 2021-01-20 19:55:02Z

1

Please try the following.

It shows a great power of XQuery and its FLWOR expression.

The <TransRef> element is taken care too based on my limited understanding of the business.

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 * FROM @loans ORDER BY Transref
    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>
{
    for $x in /root/r
    return <PSD006FeedMsg>
    <CoreItems>
        <FirmReferenceNumber>{sql:variable("@SubmittingFirm")}</FirmReferenceNumber>
        <TransRef>{data($x/Transref)}</TransRef>
        <Cancellation>{sql:variable("@Cancellation")}</Cancellation>
    </CoreItems>
    <ShortTermLoans>{$x/*[local-name(.) ne "Transref"]}</ShortTermLoans>
    </PSD006FeedMsg>
}
</PSD006-ShortTermLoans>');

edited Jan 20, 2021 at 19:55

answered Jan 20, 2021 at 19:12

Yitzhak Khabinsky

22.9k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

TheCoxta Over a year ago

That's progress! However the Transref variable needs to be removed from the ShortTermLoans element and included in the CoreItems element (instead of the hardcoded value).

Yitzhak Khabinsky Over a year ago

@TheCoxta, I adjusted the answer. Check it out.

TheCoxta Over a year ago

That's very close. The Transref element doesn't need the submitting firm included. It should only be the transref variable ie. 11, 12 ,13

Yitzhak Khabinsky Over a year ago

@TheCoxta, how about now?

Yitzhak Khabinsky Over a year ago

@TheCoxta, glad to hear that the proposed solution is working for you. Please up-vote my suggestion: feedback.azure.com/forums/908035-sql-server/suggestions/…

Collectives™ on Stack Overflow

Get SQL Results In XML Format Using xQuery

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related