Reputation: 39
I'm very new to Integrations and Middleware. I just want to transform my XML to Flat File.
Right now I'm using Azure Logic Apps for Middleware Integration and Integration Account (Azure) for Maps and Schemas. I have a structured XML
<?xml version="1.0" encoding="utf-8"?>
<LOAN_APPLICATION>
<ADDITIONAL_CASE_DATA_x0020_TRANSMITTAL_DATA>
<Header>EH</Header>
<InvestorInstitutionIdentifier></InvestorInstitutionIdentifier>
</ADDITIONAL_CASE_DATA_x0020_TRANSMITTAL_DATA>
<VARIABLE_1>
<Header>TH</Header>
<Transaction_ID>T100099-002</Transaction_ID>
<Transaction_Control_Number>2000</Transaction_Control_Number>
</VARIABLE_1>
<VARIABLE_2>
<Header>TPI</Header>
<Version_ID>1.000</Version_ID>
<Identify_Type_Code>1</Identify_Type_Code>
<Identifier></Identifier>
<Import_Action_Indicator>N</Import_Action_Indicator>
</VARIABLE_2>
<VARIABLE_3>
<Variable>000</Variable>
<File_Type>1</File_Type>
<File_Version_ID>3.20</File_Version_ID>
</VARIABLE_3>
<MORTGAGE_TERMS>
<Header>01A</Header>
<MortgageType>01</MortgageType>
<AgencyCaseIdentifier />
<BaseLoanAmount>142500.00</BaseLoanAmount>
<RequestedInterestRatePercent>5.50</RequestedInterestRatePercent>
<LoanAmortizationTermMonths>360</LoanAmortizationTermMonths>
<LoanAmortizationType>05</LoanAmortizationType>
</MORTGAGE_TERMS>
<PROPERTY>
<Header>02A</Header>
<_StreetAddress>123 TBD</_StreetAddress>
<_City>zelienople</_City>
<_State>PA</_State>
<_PostalCode>16063</_PostalCode>
<_FinancedNumberOfUnits>3</_FinancedNumberOfUnits>
</PROPERTY>
<LOAN_PURPOSE>
<Header>02B</Header>
<_Type>16</_Type>
<PropertyUsageType>1</PropertyUsageType>
<PropertyRightsType>1</PropertyRightsType>
</LOAN_PURPOSE>
<LOAN_PURPOSE_REFINANCE>
<Header>02D</Header>
<GSERefinancePurposeType>CashOutH</GSERefinancePurposeType>
</LOAN_PURPOSE_REFINANCE>
<DOWN_PAYMENT>
<Header>02E</Header>
<_Type>BridgeLoan</_Type>
<_Amount>1000.00</_Amount>
</DOWN_PAYMENT>
</LOAN_APPLICATION>
And I want to create its flat file something like this.
EH
TH T100099-0022000
TPI 1.0001 N
0001 3.20
01A01 142500.00 5.50 36005
02A123 TBD PA 16063 3
02B 16 1 1
02D CashOutH
02EF11000.00
I'm not finding any clue of how can I do it. I'm expecting XML positional schema so that everytime I've this format of XML, I just pass that XML into that schema and it will automatically convert into FLAT Text.
Previously a developer did same task in out team and generate this schema for XML to Flat file conversion.
<xs:schema xmlns="http://BizTalk_Server_Project1.3_2FlatFileSchema" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://BizTalk_Server_Project1.3_2FlatFileSchema">
<xs:annotation>
<xs:appinfo>
<schemaEditorExtension:schemaInfo xmlns:schemaEditorExtension="http://schemas.microsoft.com/BizTalk/2003/SchemaEditorExtensions" namespaceAlias="b" extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension" standardName="Flat File" />
<b:schemaInfo standard="Flat File" codepage="65001" default_pad_char=" " pad_char_type="char" count_positions_by_byte="false" parser_optimization="speed" lookahead_depth="3" suppress_empty_nodes="false" generate_empty_nodes="true" allow_early_termination="false" early_terminate_optional_fields="false" allow_message_breakup_of_infix_root="false" compile_parse_tables="false" root_reference="LOAN_APPLICATION" />
</xs:appinfo>
</xs:annotation>
<xs:element name="LOAN_APPLICATION">
<xs:annotation>
<xs:appinfo>
<schemaEditorExtension:schemaInfo xmlns:schemaEditorExtension="http://schemas.microsoft.com/BizTalk/2003/SchemaEditorExtensions" namespaceAlias="b" extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension" standardName="Flat File" />
<b:schemaInfo standard="Flat File" codepage="65001" default_pad_char=" " pad_char_type="char" count_positions_by_byte="false" parser_optimization="speed" lookahead_depth="3" suppress_empty_nodes="false" generate_empty_nodes="true" allow_early_termination="false" early_terminate_optional_fields="false" allow_message_breakup_of_infix_root="false" compile_parse_tables="false" root_reference="LOAN_APPLICATION" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:annotation>
<xs:appinfo>
<groupInfo xmlns="http://schemas.microsoft.com/BizTalk/2003" sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element name="ADDITIONAL_CASE_DATA_x0020_TRANSMITTAL_DATA">
<xs:annotation>
<xs:appinfo>
<b:recordInfo structure="positional" sequence_number="1" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:annotation>
<xs:appinfo>
<groupInfo xmlns="http://schemas.microsoft.com/BizTalk/2003" sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Header" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="3" sequence_number="1" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="InvestorInstitutionIdentifier" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="37" sequence_number="2" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="VARIABLE_1">
<xs:annotation>
<xs:appinfo>
<b:recordInfo structure="positional" sequence_number="2" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:annotation>
<xs:appinfo>
<groupInfo xmlns="http://schemas.microsoft.com/BizTalk/2003" sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Header" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="3" sequence_number="1" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="Transaction_ID" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="11" sequence_number="2" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="Transaction_Control_Number" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="13" sequence_number="3" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="VARIABLE_2">
<xs:annotation>
<xs:appinfo>
<b:recordInfo structure="positional" sequence_number="3" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:annotation>
<xs:appinfo>
<groupInfo xmlns="http://schemas.microsoft.com/BizTalk/2003" sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Header" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="4" sequence_number="1" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="Version_ID" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="5" sequence_number="2" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="Identify_Type_Code" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="2" sequence_number="3" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="Identifier" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="30" sequence_number="4" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="Import_Action_Indicator" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="7" sequence_number="5" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="VARIABLE_3">
<xs:annotation>
<xs:appinfo>
<b:recordInfo structure="positional" sequence_number="4" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:annotation>
<xs:appinfo>
<groupInfo xmlns="http://schemas.microsoft.com/BizTalk/2003" sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Variable" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="3" sequence_number="1" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="File_Type" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="3" sequence_number="2" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="File_Version_ID" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="9" sequence_number="3" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="MORTGAGE_TERMS">
<xs:annotation>
<xs:appinfo>
<b:recordInfo structure="positional" sequence_number="5" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:annotation>
<xs:appinfo>
<groupInfo xmlns="http://schemas.microsoft.com/BizTalk/2003" sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Header" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="3" sequence_number="1" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="MortgageType" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="82" sequence_number="2" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="AgencyCaseIdentifier" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="45" sequence_number="3" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="BaseLoanAmount" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="15" sequence_number="4" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="RequestedInterestRatePercent" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="7" sequence_number="5" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="LoanAmortizationTermMonths" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="3" sequence_number="6" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="LoanAmortizationType" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="142" sequence_number="7" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="PROPERTY">
<xs:annotation>
<xs:appinfo>
<b:recordInfo structure="positional" sequence_number="6" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:annotation>
<xs:appinfo>
<groupInfo xmlns="http://schemas.microsoft.com/BizTalk/2003" sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Header" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="3" sequence_number="1" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="_StreetAddress" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="50" sequence_number="2" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="_City" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="35" sequence_number="3" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="_State" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="3" sequence_number="4" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="_PostalCode" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="8" sequence_number="5" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="_FinancedNumberOfUnits" type="xs:string">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" pos_offset="0" pos_length="84" sequence_number="6" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
I just need to re-engineer it again. Is it like I need to write this schema XML manually by hand or do I have another option of using any tool to generate this long XML again.
Upvotes: 0
Views: 1154
Reputation: 163272
It's easy enough to do this transformation with XSLT. However, it's not very clear to me what rules you are applying. For example, in the row with header 02A, I can't see why the string "123 TBD" is repeated, and I can't make any sense of the whitespace at all.
If your rule is to output the content of all text nodes, using space as a separator between level-2 nodes, and newline before any level-1 nodes (and not making any provision for any text at deeper levels than this) then you could do:
<xsl:transform version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<xsl:for-each select="*/*">
<xsl:text>
</xsl:text>
<xsl:value-of select="*/text()"/>
</xsl:for-each>
</xsl:template>
</xsl:transform>
The schema here is irrelevant.
Upvotes: 1