I have an Excel file with 29 columns of 220 rows of data. The schema I am supposed to use is from http://www.fsa.gov.uk/mer/drg/PSD007/v1-3/PSD007-Schema.xsd which is a UK government data schema for mortgage reporting by banks. The schema is also available for download at https://www.fca.org.uk/firms/systems-reporting/gabriel/system-information/data-reference-guides/psd/psd007

When I add this to the XML Sources in Excel (from the URL above) for my data I get no errors.

But when I try to map the elements to my columns, although I get no errors, the entire column is not selected and only the headings row or the first row is exported to XML.

Can anyone look at the Schema below tell me what I am doing wrong?

Here below is the schema in case you need to look at it without going onto the website:

<?xml version="1.0" encoding="UTF-8"?>
<!-- ******************************************************************* * * Schema for: Data Item PSD007-Mortgage Performance Sales Data * * Version: 1.3 * Date: 20/08/2014 * * Dependencies: Version 7 CommonTypes-Schema.xsd * History: Version issued following changes to the following: * - RepaymentMethod * - The elements "LoanPurchase", "OrigLoanSize", "OrigLoanTerm" and "OrigProviderFRN" moved into "CoreItems" * - The enumerations for'TypeIntRate' updated * - "AcctOpenedDate", "DateLitigationStarted" and "OrigLoanTerm" updated to minOccurs="0" * ******************************************************************* -->

-<xs:schema xmlns="urn:fsa-gov-uk:MER:PSD007:1" version="1.3" id="MER-PSD007" xmlns:mer-meta="urn:fsa-gov-uk:MER:Meta-Data:1" targetNamespace="urn:fsa-gov-uk:MER:PSD007:1" attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
-<xs:annotation>
-<xs:documentation>
<mer-meta:DataItemReference>PSD007</mer-meta:DataItemReference>
<mer-meta:DataItemName>Mortgage Performance Sales Data</mer-meta:DataItemName>
</xs:documentation>
</xs:annotation>
<xs:include schemaLocation="../../CommonTypes/v7/CommonTypes-Schema.xsd"/>
-<xs:element name="PSD007-MortgagePerformanceSalesData">

-<xs:complexType>
-<xs:sequence>
-<xs:element name="PSDFeedHeader" minOccurs="1">
-<xs:complexType>
-<xs:sequence>
-<xs:element name="Submitter" minOccurs="1">
-<xs:complexType>
-<xs:sequence>
<xs:element name="SubmittingFirm" minOccurs="1" type="FRNType"/>
</xs:sequence>
</xs:complexType>
</xs:element>
-<xs:element name="ReportDetails" minOccurs="1">
-<xs:complexType>
-<xs:sequence>
<xs:element name="ReportCreationDate" minOccurs="1" type="xs:date"/>

<xs:element name="ReportIdentifier" minOccurs="1" type="String100Type"/>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>


-<xs:element name="PSD007FeedMsg" maxOccurs="unbounded">


-<xs:annotation>

<xs:documentation>Defines the individual transactions to be reported</xs:documentation>

</xs:annotation>


-<xs:complexType>


-<xs:sequence>


-<xs:element name="CoreItems" minOccurs="1">


-<xs:complexType>


-<xs:sequence>

<xs:element name="AdminFRN" minOccurs="0" type="FRNType"/>

<xs:element name="Postcode" minOccurs="1" type="PostCodeType"/>

<xs:element name="MainDOB" minOccurs="1" type="xs:date"/>

<xs:element name="AcctOpenedDate" minOccurs="0" type="xs:date"/>

<xs:element name="TransRef" minOccurs="1" type="String100Type"/>

<xs:element name="DateofBalance" minOccurs="1" type="xs:date"/>

<xs:element name="LoanPurchase" minOccurs="1" type="YNType"/>

<xs:element name="OrigLoanSize" minOccurs="0" type="NonNegativeMonetaryType"/>

<xs:element name="OrigLoanTerm" minOccurs="0" type="NonNegativeIntegerType"/>

<xs:element name="OrigProviderFRN" minOccurs="0" type="FRNType"/>

<xs:element name="Cancellation" minOccurs="0" type="xs:boolean"/>

</xs:sequence>

</xs:complexType>

</xs:element>


-<xs:element name="PerformanceSalesData" minOccurs="1">


-<xs:complexType>


-<xs:sequence>

<xs:element name="OutstandingBalance" minOccurs="1" type="MonetaryType"/>

<xs:element name="MonthlyPayment" minOccurs="1" type="NonNegativeMonetaryType"/>

<xs:element name="ValueLinkedAccounts" minOccurs="1" type="MonetaryType"/>

<xs:element name="GrossInterestCharged" minOccurs="1" type="PercentDP2Type"/>


-<xs:element name="TypeIntRate" minOccurs="1">


-<xs:simpleType>


-<xs:restriction base="xs:string">

<xs:enumeration value="01"/>

<xs:enumeration value="02"/>

<xs:enumeration value="04"/>

<xs:enumeration value="05"/>

<xs:enumeration value="06"/>

<xs:enumeration value="07"/>

<xs:enumeration value="08"/>

<xs:enumeration value="99"/>

</xs:restriction>

</xs:simpleType>

</xs:element>

<xs:element name="IncentivisedRate" minOccurs="1" type="YNType"/>

<xs:element name="DateIncentiveEnds" minOccurs="0" type="xs:date"/>

<xs:element name="RemainingTerm" minOccurs="1" type="NonNegativeIntegerType"/>

<xs:element name="MonthsPastMaturity" minOccurs="1" type="NonNegativeIntegerType"/>


-<xs:element name="RepaymentMethod" minOccurs="1">


-<xs:simpleType>


-<xs:restriction base="xs:string">

<xs:enumeration value="C"/>

<xs:enumeration value="I"/>

<xs:enumeration value="M"/>

</xs:restriction>

</xs:simpleType>

</xs:element>


-<xs:element name="ReasonForClosure" minOccurs="0">


-<xs:simpleType>


-<xs:restriction base="xs:string">

<xs:enumeration value="R"/>

<xs:enumeration value="M"/>

<xs:enumeration value="P"/>

<xs:enumeration value="A"/>

<xs:enumeration value="V"/>

<xs:enumeration value="O"/>

</xs:restriction>

</xs:simpleType>

</xs:element>

<xs:element name="CurrentPaymentShortfall" minOccurs="1" type="NonNegativeMonetaryType"/>

<xs:element name="ArrearsStartDate" minOccurs="0" type="xs:date"/>

<xs:element name="PaymentShortfallArrangement" minOccurs="1" type="YNType"/>

<xs:element name="DateFormalArrangement" minOccurs="0" type="xs:date"/>

<xs:element name="PaymentShortfallCapitalisation" minOccurs="1" type="YNType"/>

<xs:element name="CapitalisationDate" minOccurs="0" type="xs:date"/>

<xs:element name="SwitchInterestOnly" minOccurs="1" type="YNType"/>

<xs:element name="InterestOnlyDate" minOccurs="0" type="xs:date"/>

<xs:element name="PaymentsSuspended" minOccurs="1" type="YNType"/>

<xs:element name="DatesPaymentsSuspended" minOccurs="0" type="xs:date"/>

<xs:element name="ReducedPayments" minOccurs="1" type="YNType"/>
<xs:element name="DateReducedPayments" minOccurs="0" type="xs:date"/>
<xs:element name="TermExtension" minOccurs="1" type="YNType"/>
<xs:element name="DateTermExtension" minOccurs="0" type="xs:date"/>
<xs:element name="OtherForbearance" minOccurs="1" type="YNType"/>
<xs:element name="DateOtherForbearance" minOccurs="0" type="xs:date"/>
<xs:element name="DateLitigationStarted" minOccurs="0" type="xs:date"/>
<xs:element name="PossessionOrder" minOccurs="1" type="YNType"/>
<xs:element name="DatePossession" minOccurs="0" type="xs:date"/>
<xs:element name="RepossessionSaleValueAchieved" minOccurs="0" type="MonetaryType"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="currency" use="required" fixed="GBP"/>
</xs:complexType>
</xs:element>
</xs:schema>

• Edited by Our Man In Bananas Monday, July 06, 2015 3:06 PM added info

Hi,

I tested you issue in my own environment, and I got the result as shown in the following figure.

I imported XML Source from DATA- Get External Data- From Other Sources- From XML Data Import- Import XML Source file

Did you need to get the same result? If you want to check my XML Source file and this Excel File, you can download them from these links:

XML Source file: http://1drv.ms/1M9QpgE

Excel file: http://1drv.ms/1M9QJfg

Please let me know if they work for you, I'm glad to help and follow up your reply.<o:p></o:p>

Regards,

Emi Zhang
TechNet Community Su

Hi Emil,

Thanks for your help with this - I really appreciate it.

Regarding your success, I think you misunderstand - you seem to have loaded the XML Schema and mapped it. I can load the XML Schema, but I cannot map the data rows properly - I suspect it is because the XML Schema provided by the FCA (UK Government) has multiple levels, so there is a header element, then for each row there is a Core, and a Data element...

I have even got to the point ignoring Excel's XML Export, and of outputting the data to an xml file by looping through in VBA, and it works, but when I open the XML file in VS2013 as an XML file, I get lot's of validation errors where there are empty elements (even though in the Schema they are defined as minOccurrs=0) - and when I change my VBA to skip the element if that cell is empty, the resulting XML shows errors in VS2013 that the data is incomplete. For example, VS2013 highlights that ArrearsStartDate is missing even though I can clearly see the below in the XSD file (crazy!).

<xs:element name="ArrearsStartDate" minOccurs="0" type="xs:date"/>

I tried to use your OneDrive links (ay home), but I cannot open the Excel file.

Even though I am using IE11 and Office 2013...I get an error message that the Excel file cannot be opened online as it uses XML Maps, and I also cannot save it to my pc ... I think I need to be able to download it to open it.

At work we are using IE11, Windows 7 64-bit, and Office 2010.