CODEWITHSUNDEEP
excelxmlxsdexportdenormalized
Kyuxof
Kyuxof

Reputation: 1

XML Export - Denormalized Data

I work for a financial firm who are required to report trades, sales data etc. to the regulator. At the moment we are doing this per individual because we cannot get their XML schema template to work. I am trying to import this into excel using the xml source pane.

The problem we have is that when we come to export the document, there is a denormalized data error which we can't get around. Unfortunately, myself and the team haven’t got much experience in mapping XML to know how to properly troubleshoot it. I’ve changed various bits of code to no success and even contacted the FCA (who say there is nothing wrong with it or dead links…lol).

Can someone offer some insight or guidance on how we can correct this issue?

The Schema is as follows:

<?xml version="1.0" encoding="utf-8"?>
<!-- edited with XMLSpy v2005 U (http://www.xmlspy.com) by Clive Raven (LogicaCMG) -->
<!--
********************************************************************************
*
* Project Name : FSA Transaction Reporting System
* Reference    :
* Description  : Definition For FSA High-Street Firms Feed For Retail Investment Products
* Dependencies : http://www.fsa.gov.uk/XMLSchema/FSAFeedCommon-v1-1.xsd
*
* Revision History
* Version   Author              Date                Description
*  1.1          FSA                 07/04/2004      First published draft
*  1.2          FSA                 22/11/2004      Final draft
*  1.3          FSA                 19/02/2007      Added Self Invested Personal Pensions (SIPPs) product type in TypeOfPolicyType
*
* Copyright Financial Services Authority 2004
********************************************************************************
-->
<xs:schema xmlns="http://www.fsa.gov.uk/XMLSchema/FSAHSFFeedRI-v1-2" 
    xmlns:cmn="http://www.fsa.gov.uk/XMLSchema/FSAFeedCommon-v1-2" 
    xmlns:xs="http://www.w3.org/2001/XMLSchema" 
    targetNamespace="http://www.fsa.gov.uk/XMLSchema/FSAHSFFeedRI-v1-2" 
    elementFormDefault="qualified" 
    version="1.0"   
    id="FSAHSFFeedRI-v1-3">
    <xs:import namespace="http://www.fsa.gov.uk/XMLSchema/FSAFeedCommon-v1-2" 
        schemaLocation="https://gabriel.fca.org.uk/specifications/MER/DRG/PSD-CommonTypes/v1.2/FSAFeedCommon-v1-2.xsd"/>
    <!--Types used in FSAHSFFeedRI-->
    <xs:simpleType name="TypeOfPolicyType">
        <xs:annotation>
            <xs:documentation>Retail Investment Product Code.       </xs:documentation>
        </xs:annotation>
        <xs:restriction base="xs:string">
            <xs:enumeration value="01">
                <xs:annotation>
                    <xs:documentation>Unit trust/OEIC</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="02">
                <xs:annotation>
                    <xs:documentation>Investment trust</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="03">
                <xs:annotation>
                    <xs:documentation>ISA</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="04">
                <xs:annotation>
                    <xs:documentation>Structured capital at risk product</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="05">
                <xs:annotation>
                    <xs:documentation>With profit bond</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="06">
                <xs:annotation>
                    <xs:documentation>Unit linked bond</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="07">
                <xs:annotation>
                    <xs:documentation>Distribution bond</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="08">
                <xs:annotation>
                    <xs:documentation>With profit endowment</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="09">
                <xs:annotation>
                    <xs:documentation>Endowment savings plan</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="10">
                <xs:annotation>
                    <xs:documentation>Mortgage endowment</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="11">
                <xs:annotation>
                    <xs:documentation>Guaranteed income/growth/investment bond</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="12">
                <xs:annotation>
                    <xs:documentation>Trustee investment bond</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="13">
                <xs:annotation>
                    <xs:documentation>Life annuity</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="14">
                <xs:annotation>
                    <xs:documentation>Pension annuity</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="15">
                <xs:annotation>
                    <xs:documentation>Long term care insurance</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="16">
                <xs:annotation>
                    <xs:documentation>Stakeholder pension</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="17">
                <xs:annotation>
                    <xs:documentation>Personal pension</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="18">
                <xs:annotation>
                    <xs:documentation>Group personal pension</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="19">
                <xs:annotation>
                    <xs:documentation>FSAVC</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="20">
                <xs:annotation>
                    <xs:documentation>Individual pension transfer</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="21">
                <xs:annotation>
                    <xs:documentation>Pension opt out</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="22">
                <xs:annotation>
                    <xs:documentation>Section 32 buy out</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="23">
                <xs:annotation>
                    <xs:documentation>Group section 32 buy out</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="24">
                <xs:annotation>
                    <xs:documentation>Income drawdown</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="25">
                <xs:annotation>
                    <xs:documentation>Executive pension</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="26">
                <xs:annotation>
                    <xs:documentation>SSAS</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="27">
                <xs:annotation>
                    <xs:documentation>Group money purchase</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="28">
                <xs:annotation>
                    <xs:documentation>AVC final salary</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="29">
                <xs:annotation>
                    <xs:documentation>AVC group money purchase</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="30">
                <xs:annotation>
                    <xs:documentation>Self Invested Personal Pensions (SIPPs)</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="99">
                <xs:annotation>
                    <xs:documentation>Other. Use this when product is not one of above.</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
        </xs:restriction>
    </xs:simpleType>
    <!--Structures used in FSAHSFFeedRI -->
    <xs:complexType name="RetailInvestmentStructure">
        <xs:sequence>
            <xs:element name="PrincipalOrNetworkFSARef" type="cmn:FSARefType" minOccurs="0">
                <xs:annotation>
                    <xs:documentation>This field only applies if the sale has been made by an intermediary who has a principal or is part of a network. </xs:documentation>
                </xs:annotation>
            </xs:element>
            <xs:element name="TypePolicy" type="TypeOfPolicyType">
                <xs:annotation>
                    <xs:documentation>Type of policy.</xs:documentation>
                </xs:annotation>
            </xs:element>
            <xs:element name="AdvisedSale" type="cmn:YNType" minOccurs="0">
                <xs:annotation>
                    <xs:documentation>This field is optional until July 2006, when it will become mandatory.  Y = Sale was Advised, N = Sale was non-Advised. For PSD reporting purposes non-Advised includes execution only and direct offer transactions.</xs:documentation>
                </xs:annotation>
            </xs:element>
            <xs:element name="CustPostCode" type="cmn:PostCodeType" minOccurs="0">
                <xs:annotation>
                    <xs:documentation>Full U.K postcode of customer.</xs:documentation>
                </xs:annotation>
            </xs:element>
            <xs:element name="PremPaymentMethod" type="cmn:PremiumPaymentMethodType">
                <xs:annotation>
                    <xs:documentation>Method of premium/ contribution payment.</xs:documentation>
                </xs:annotation>
            </xs:element>
            <xs:element name="TotalPremAmt" type="cmn:HSFStdSterlingAmountType">
                <xs:annotation>
                    <xs:documentation>Total premium/contribution amount. Annualised amount rounded to nearest pound.</xs:documentation>
                </xs:annotation>
            </xs:element>
            <xs:element name="CustDOB" type="xs:date" minOccurs="0">
                <xs:annotation>
                    <xs:documentation>Date of birth of customer. Applies to first named customer at time of sale i.e. age obtained at proposal stage. Must represent an age up to and including 115.</xs:documentation>
                </xs:annotation>
            </xs:element>
            <xs:element name="DateInForce" type="xs:date"/>
        </xs:sequence>
    </xs:complexType>
    <!--FSAHSFFeedRI -->
    <xs:element name="FSAHSFFeedRI">
        <xs:complexType>
            <xs:sequence>
                <xs:element ref="cmn:FSAFeedHeader"/>
                <xs:element name="FSAHSFFeedRIMsg" minOccurs="0" maxOccurs="unbounded">
                    <xs:annotation>
                        <xs:documentation>Defines the inidividual transactions</xs:documentation>
                    </xs:annotation>
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element ref="cmn:CoreItems"/>
                            <xs:element name="RetailInvestment" type="RetailInvestmentStructure">
                                <xs:annotation>
                                    <xs:documentation>Defines the elements that are specific to a Retail Investment transaction.</xs:documentation>
                                </xs:annotation>
                            </xs:element>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>

When importing the schema there is an error importing the common feed due to a dead link referenced in the schema:

SchemaLocation="https://gabriel.fca.org.uk/specifications/MER/DRG/PSD-CommonTypes/v1.2/FSAFeedCommon-v1-2.xsd

This can be downloaded separately from the FCA resources section and the schema can be edited to point to the file locally. See below:

<?xml version="1.0" encoding="utf-8"?>
<!-- edited with XMLSpy v2005 U (http://www.xmlspy.com) by Clive Raven (LogicaCMG) -->
<!--
********************************************************************************
*
* Project Name : FSA Transaction Reporting System
* Reference    :
* Description  : Common Definitions For FSA Feeds
* Dependencies :
*
* Revision History
* Version   Author          Date                Description
*  1.1          FSA             07/04/2004      Published with early draft of SBD Schema's
*  1.2          FSA             22/11/2004      Published with final draft of all TRS Schema's
*
* Copyright Financial Services Authority 2004
********************************************************************************
-->
<xs:schema xmlns="http://www.fsa.gov.uk/XMLSchema/FSAFeedCommon-v1-2" 
    xmlns:xs="http://www.w3.org/2001/XMLSchema" 
    targetNamespace="http://www.fsa.gov.uk/XMLSchema/FSAFeedCommon-v1-2" 
    elementFormDefault="qualified" 
    version="1.2" 
    id="FSAFeedCommon-v1-2">
    <!--Common Types -->
    <xs:simpleType name="NonEmptyString">
        <xs:annotation>
            <xs:documentation>Defines a string type that may not be the empty string. Values of this type are also whitespace normalized meaning all-whitespace strings are also invalid.</xs:documentation>
        </xs:annotation>
        <xs:restriction base="xs:string">
            <xs:whiteSpace value="collapse"/>
            <xs:minLength value="1"/>
        </xs:restriction>
    </xs:simpleType>
    <xs:simpleType name="FSARefType">
        <xs:annotation>
            <xs:documentation>FSA Reference Number. Either a 6 or 7 digit number.  Leading zeroes are not expected.</xs:documentation>
        </xs:annotation>
        <xs:restriction base="xs:string">
            <xs:pattern value="[1-9]{1}[0-9]{5,6}"/>
        </xs:restriction>
    </xs:simpleType>
    <xs:simpleType name="PostCodeType">
        <xs:annotation>
            <xs:documentation>Full or abbreviated UK postcode.</xs:documentation>
        </xs:annotation>
        <xs:restriction base="xs:string">
            <xs:maxLength value="8"/>
        </xs:restriction>
    </xs:simpleType>
    <xs:simpleType name="YNType">
        <xs:annotation>
            <xs:documentation>Y or N</xs:documentation>
        </xs:annotation>
        <xs:restriction base="xs:string">
            <xs:enumeration value="Y"/>
            <xs:enumeration value="N"/>
        </xs:restriction>
    </xs:simpleType>
    <!--Common SBD Types -->
    <xs:simpleType name="HSFStdSterlingAmountType">
        <xs:annotation>
            <xs:documentation>Integer from 1 to 999999999. Representing sterling-equivalent amount.</xs:documentation>
        </xs:annotation>
        <xs:restriction base="xs:positiveInteger">
            <xs:totalDigits value="9"/>
        </xs:restriction>
    </xs:simpleType>
    <xs:simpleType name="PremiumPaymentMethodType">
        <xs:annotation>
            <xs:documentation>Method of Premium/Contribution payment. Single or Regular.</xs:documentation>
        </xs:annotation>
        <xs:restriction base="xs:string">
            <xs:enumeration value="S">
                <xs:annotation>
                    <xs:documentation>Single payment.</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
            <xs:enumeration value="R">
                <xs:annotation>
                    <xs:documentation>Regular payment.</xs:documentation>
                </xs:annotation>
            </xs:enumeration>
        </xs:restriction>
    </xs:simpleType>
    <!--Common Structures -->
    <xs:element name="CoreItems">
        <xs:annotation>
            <xs:documentation>Defines the elements that are common to all transactions.</xs:documentation>
        </xs:annotation>
        <xs:complexType>
            <xs:sequence>
                <xs:element name="FirmFSARef" type="FSARefType">
                    <xs:annotation>
                        <xs:documentation>The FSA code of the reporting firm for this transaction.</xs:documentation>
                    </xs:annotation>
                </xs:element>
                <xs:element name="TransRef">
                    <xs:annotation>
                        <xs:documentation>The unique reference, internal to the reporting firm, that will enable the firm to provide the FSA with more information concerning the trade if required. This reference must be unique within each report file, with the exception that a reference can occur twice if one occurrence is a cancellation. </xs:documentation>
                    </xs:annotation>
                    <xs:simpleType>
                        <xs:restriction base="NonEmptyString">
                            <xs:maxLength value="25"/>
                        </xs:restriction>
                    </xs:simpleType>
                </xs:element>
                <xs:element name="Cancellation" type="xs:boolean" minOccurs="0">
                    <xs:annotation>
                        <xs:documentation>Indicates if the transaction is a cancellation. If ommitted this is logically the same as providing a value of 'false'.</xs:documentation>
                    </xs:annotation>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
    <xs:element name="FSAFeedHeader">
        <xs:annotation>
            <xs:documentation>Defines the report header, common to all reports.</xs:documentation>
        </xs:annotation>
        <xs:complexType>
            <xs:sequence>
                <xs:element name="FeedTargetSchemaVersion" type="xs:string">
                    <xs:annotation>
                        <xs:documentation>Identifies the version of the business specific schema to which this report conforms.</xs:documentation>
                    </xs:annotation>
                </xs:element>
                <xs:element name="Submitter">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="SubmittingFirm" type="FSARefType">
                                <xs:annotation>
                                    <xs:documentation>The FSA code of the firm submitting the report.</xs:documentation>
                                </xs:annotation>
                            </xs:element>
                            <xs:element name="SubmittingDept" minOccurs="0">
                                <xs:annotation>
                                    <xs:documentation>The identifier of the department within the submitting firm who created this report - optional.</xs:documentation>
                                </xs:annotation>
                                <xs:simpleType>
                                    <xs:restriction base="NonEmptyString">
                                        <xs:maxLength value="20"/>
                                        <xs:pattern value="[a-zA-Z0-9]+"/>
                                    </xs:restriction>
                                </xs:simpleType>
                            </xs:element>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
                <xs:element name="ReportDetails">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="ReportCreationDate" type="xs:date">
                                <xs:annotation>
                                    <xs:documentation>Report creation date.</xs:documentation>
                                </xs:annotation>
                            </xs:element>
                            <xs:element name="ReportIdentifier">
                                <xs:annotation>
                                    <xs:documentation>Unique identifier for the report (wthin the context of the submitter, submitting firm, submitting department and report creation date).</xs:documentation>
                                </xs:annotation>
                                <xs:simpleType>
                                    <xs:restriction base="NonEmptyString">
                                        <xs:maxLength value="25"/>
                                    </xs:restriction>
                                </xs:simpleType>
                            </xs:element>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>

Resources:
https://regdata.fca.org.uk/specifications/DRG/PSD002/v1.5/FSAHSFFeedRI-v1-3.xsd - Full Schema
https://regdata.fca.org.uk/specifications/DRG/PSD-CommonTypes/v1.2/FSAFeedCommon-v1-2.xsd - Schema referenced using a dead link
https://regdata.fca.org.uk/#/layout/resources - General Resources

Upvotes: 0

Views: 346

Answers (1)

Ben
Ben

Reputation: 17

I'm not sure what your question is exactly. You point to 2 problems:

  1. "denormalized data error": what do you mean? how is this related to the schema?
  2. The broken link in the main schema file. You solved it yourself by editing the schemaLocation in the main schema. If you need a way to automatically correct the location without editing the main schema file, you can use an XML Catalog. Is it what you're looking for?

What do you mean also by "importing" the schema? Are you importing it in your own schema?

A catalog would look like this:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE catalog PUBLIC "-//OASIS//DTD XML Catalogs V1.1//EN" "http://www.oasis-open.org/committees/entity/release/1.1/catalog.dtd">
<catalog xmlns="urn:oasis:names:tc:entity:xmlns:xml:catalog">      
    <uri name="http://www.fsa.gov.uk/XMLSchema/FSAFeedCommon-v1-2"
  uri="path/to/local/copy/of/FSAFeedCommon-v1-2.xsd"/> 
</catalog>

Then, you need to configure your parser (validation tool) to use that catalog.

Upvotes: 0

Related Questions