Transform one XML infopath source file to another XML destination file using XSL or Xpath

Question

I am really new to XSLT, XPath and XML. I am designing a prototype to interface an infopath form(Expenses Entry) xml file to an ERP to generate invoices.

Here is below the XML source file from the infopath form (simplified Header):

<?xml version="1.0" encoding="UTF-8"?>
<my:Requester>The Requester</my:Requester>
<my:Date>2015-07-20</my:Date>
<my:grProgram></my:grProgram>
<my:Program>Program A</my:Program>
<my:grSite>
    <my:Site>Site A</my:Site>
    <my:grVolunteer>
        <my:Name>0000003686</my:Name>
        <my:VendorID>0000003686</my:VendorID>
        <my:InvoiceID>BMFF036862015-07-20</my:InvoiceID>
        <my:InvoiceDate>2015-07-20</my:InvoiceDate>
        <my:Comments></my:Comments>
        <my:MoreInformation>Volunteer 1</my:MoreInformation>
        <my:grExpenses>
            <my:Category>Stipend</my:Category>
            <my:Amount>75</my:Amount>
            <my:Account>754100</my:Account>
            <my:SpeedChart>G1002469</my:SpeedChart>
            <my:AccountingDepartment>3059000</my:AccountingDepartment>
            <my:AccountingPCBusUnit>GRT01</my:AccountingPCBusUnit>
            <my:AccountingFundCode>3100</my:AccountingFundCode>
            <my:AccountingProgramCode>C001</my:AccountingProgramCode>
            <my:AccountingProjectID>1002469</my:AccountingProjectID>
            <my:AccountingActivityID>1</my:AccountingActivityID>
        </my:grExpenses>
        <my:grExpenses>
            <my:Category>Miles</my:Category>
            <my:Amount>45</my:Amount>
            <my:Account>750190</my:Account>
            <my:SpeedChart>G1002159</my:SpeedChart>
            <my:AccountingDepartment>3059000</my:AccountingDepartment>
            <my:AccountingPCBusUnit>GRT01</my:AccountingPCBusUnit>
            <my:AccountingFundCode>3900</my:AccountingFundCode>
            <my:AccountingProgramCode>C001</my:AccountingProgramCode>
            <my:AccountingProjectID>1002159</my:AccountingProjectID>
            <my:AccountingActivityID>1</my:AccountingActivityID>
        </my:grExpenses>
        <my:Total>120</my:Total>
    </my:grVolunteer>
        <my:grVolunteer>
        <my:Name>0000003707</my:Name>
        <my:VendorID>0000003707</my:VendorID>
        <my:InvoiceID>SFFF037072015-07-20</my:InvoiceID>
        <my:InvoiceDate>2015-07-20</my:InvoiceDate>
        <my:Comments></my:Comments>
        <my:MoreInformation>Volunteer 2</my:MoreInformation>
        <my:grExpenses>
            <my:Category>Stipend</my:Category>
            <my:Amount>12</my:Amount>
            <my:Account>754100</my:Account>
            <my:SpeedChart>G1002469</my:SpeedChart>
            <my:AccountingDepartment>3059000</my:AccountingDepartment>
            <my:AccountingPCBusUnit>GRT01</my:AccountingPCBusUnit>
            <my:AccountingFundCode>3100</my:AccountingFundCode>
            <my:AccountingProgramCode>C001</my:AccountingProgramCode>
            <my:AccountingProjectID>1002469</my:AccountingProjectID>
            <my:AccountingActivityID>1</my:AccountingActivityID>
        </my:grExpenses>
            <my:grExpenses>
            <my:Category>Miles</my:Category>
            <my:Amount>15</my:Amount>
            <my:Account>750190</my:Account>
            <my:SpeedChart>G1002159</my:SpeedChart>
            <my:AccountingDepartment>3059000</my:AccountingDepartment>
            <my:AccountingPCBusUnit>GRT01</my:AccountingPCBusUnit>
            <my:AccountingFundCode>3900</my:AccountingFundCode>
            <my:AccountingProgramCode>C001</my:AccountingProgramCode>
            <my:AccountingProjectID>1002159</my:AccountingProjectID>
            <my:AccountingActivityID>1</my:AccountingActivityID>
        </my:grExpenses><my:grExpenses>
            <my:Category>Miles</my:Category>
            <my:Amount>45</my:Amount>
            <my:Account>750190</my:Account>
            <my:SpeedChart>G1002164</my:SpeedChart>
            <my:AccountingDepartment>3059000</my:AccountingDepartment>
            <my:AccountingPCBusUnit>GRT01</my:AccountingPCBusUnit>
            <my:AccountingFundCode>3900</my:AccountingFundCode>
            <my:AccountingProgramCode>C001</my:AccountingProgramCode>
            <my:AccountingProjectID>1002164</my:AccountingProjectID>
            <my:AccountingActivityID>1</my:AccountingActivityID>
        </my:grExpenses><my:Total>72</my:Total>
    </my:grVolunteer></my:grSite>
<my:grSite>
    <my:Site>WI</my:Site>
    <my:grVolunteer>
        <my:Name>0000002967</my:Name>
        <my:VendorID>0000002967</my:VendorID>
        <my:InvoiceID>CCFW029672015-07-20</my:InvoiceID>
        <my:InvoiceDate>2015-07-20</my:InvoiceDate>
        <my:Comments></my:Comments>
        <my:MoreInformation>Volunteer 3</my:MoreInformation>
        <my:grExpenses>
            <my:Category>Stipend</my:Category>
            <my:Amount>78</my:Amount>
            <my:Account>754100</my:Account>
            <my:SpeedChart>3140110F25</my:SpeedChart>
            <my:AccountingDepartment>3140110</my:AccountingDepartment>
            <my:AccountingPCBusUnit></my:AccountingPCBusUnit>
            <my:AccountingFundCode>2500</my:AccountingFundCode>
            <my:AccountingProgramCode>C001</my:AccountingProgramCode>
            <my:AccountingProjectID></my:AccountingProjectID>
            <my:AccountingActivityID></my:AccountingActivityID>
        </my:grExpenses>
        <my:Total>78</my:Total>
    </my:grVolunteer>
</my:grSite></my:grSCorpProgrFields>

Here is the XML destination file:

<?xml version="1.0" encoding="UTF-8"?>
<VCHR_HDR_STG>
    <BUSINESS_UNIT>BU</BUSINESS_UNIT>
    <VOUCHER_ID>NEXT</VOUCHER_ID>
    <VOUCHER_STYLE>REG</VOUCHER_STYLE>
    <INVOICE_ID>BMFF036862015-07-20</INVOICE_ID>
    <INVOICE_DT>07/20/2015</INVOICE_DT>
    <VENDOR_ID>0000003686</VENDOR_ID>
    <GROSS_AMT>120</GROSS_AMT>
    <DESCR254_MIXED>Volunteer 1</DESCR254_MIXED>
    <VCHR_SRC>XLS</VCHR_SRC>
    <VCHR_LINE_STG>
    <BUSINESS_UNIT>BU</BUSINESS_UNIT>
    <VOUCHER_LINE_NUM>1</VOUCHER_LINE_NUM>
    <DESCR>Stipend</DESCR>
    <MERCHANDISE_AMT>75</MERCHANDISE_AMT>
    <VOUCHER_ID>NEXT</VOUCHER_ID>
    <VCHR_DIST_STG>
        <BUSINESS_UNIT>BU</BUSINESS_UNIT>
        <VOUCHER_LINE_NUM>1</VOUCHER_LINE_NUM>
        <DISTRIB_LINE_NUM>1</DISTRIB_LINE_NUM>
        <ACCOUNT >754100</ACCOUNT >
        <DEPTID>3059000</DEPTID>
        <MERCHANDISE_AMT>75</MERCHANDISE_AMT>
        <BUSINESS_UNIT_PC>GRT01</BUSINESS_UNIT_PC>
        <ACTIVITY_ID>1</ACTIVITY_ID>
        <FUND_CODE>3100</FUND_CODE>
        <PROGRAM_CODE >C001</PROGRAM_CODE >
        <PROJECT_ID>1002469</PROJECT_ID>
        <VOUCHER_ID>NEXT</VOUCHER_ID>
    </VCHR_DIST_STG>
    </VCHR_LINE_STG>
    <VCHR_LINE_STG>
    <BUSINESS_UNIT>BU</BUSINESS_UNIT>
    <VOUCHER_LINE_NUM>2</VOUCHER_LINE_NUM>
    <DESCR>Miles</DESCR>
    <MERCHANDISE_AMT>45</MERCHANDISE_AMT>
    <VOUCHER_ID>NEXT</VOUCHER_ID>
    <VCHR_DIST_STG>
        <BUSINESS_UNIT>BU</BUSINESS_UNIT>
        <VOUCHER_LINE_NUM>2</VOUCHER_LINE_NUM>
        <DISTRIB_LINE_NUM>2</DISTRIB_LINE_NUM>
        <ACCOUNT >750190</ACCOUNT >
        <DEPTID>3059000</DEPTID>
        <MERCHANDISE_AMT>45</MERCHANDISE_AMT>
        <BUSINESS_UNIT_PC>GRT01</BUSINESS_UNIT_PC>
        <ACTIVITY_ID>1</ACTIVITY_ID>
        <FUND_CODE>3900</FUND_CODE>
        <PROGRAM_CODE >C001</PROGRAM_CODE >
        <PROJECT_ID>1002159</PROJECT_ID>
        <VOUCHER_ID>NEXT</VOUCHER_ID>
    </VCHR_DIST_STG>
    </VCHR_LINE_STG>
</VCHR_HDR_STG>
<VCHR_HDR_STG>
    <BUSINESS_UNIT>BU</BUSINESS_UNIT>
    <VOUCHER_ID>NEXT</VOUCHER_ID>
    <VOUCHER_STYLE>REG</VOUCHER_STYLE>
    <INVOICE_ID>SFFF037072015-07-20</INVOICE_ID>
    <INVOICE_DT>07/20/2015</INVOICE_DT>
    <GROSS_AMT>72</GROSS_AMT>
    <VENDOR_ID>0000002250</VENDOR_ID>
    <DESCR254_MIXED>Volunteer 2</DESCR254_MIXED>
    <VCHR_SRC>XLS</VCHR_SRC>
    <VCHR_LINE_STG>
    <BUSINESS_UNIT>BU</BUSINESS_UNIT>
    <VOUCHER_LINE_NUM>1</VOUCHER_LINE_NUM>
    <DESCR>Stipend</DESCR>
    <MERCHANDISE_AMT>12</MERCHANDISE_AMT>
    <VOUCHER_ID>NEXT</VOUCHER_ID>
    <VCHR_DIST_STG>
        <BUSINESS_UNIT>BU</BUSINESS_UNIT>
        <VOUCHER_LINE_NUM>1</VOUCHER_LINE_NUM>
        <DISTRIB_LINE_NUM>1</DISTRIB_LINE_NUM>
        <ACCOUNT >754100</ACCOUNT >
        <DEPTID>3059000</DEPTID>
        <MERCHANDISE_AMT>12</MERCHANDISE_AMT>
        <BUSINESS_UNIT_PC>GRT01</BUSINESS_UNIT_PC>
        <ACTIVITY_ID>1</ACTIVITY_ID>
        <FUND_CODE>3100</FUND_CODE>
        <PROGRAM_CODE >C001</PROGRAM_CODE >
        <PROJECT_ID>1002469</PROJECT_ID>
        <VOUCHER_ID>NEXT</VOUCHER_ID>
       </VCHR_DIST_STG>
    </VCHR_LINE_STG>
    <VCHR_LINE_STG>
    <BUSINESS_UNIT>BU</BUSINESS_UNIT>
    <VOUCHER_LINE_NUM>2</VOUCHER_LINE_NUM>
    <DESCR>Miles</DESCR>
    <MERCHANDISE_AMT>15</MERCHANDISE_AMT>
    <VOUCHER_ID>NEXT</VOUCHER_ID>
    <VCHR_DIST_STG>
        <BUSINESS_UNIT>BU</BUSINESS_UNIT>
        <VOUCHER_LINE_NUM>2</VOUCHER_LINE_NUM>
        <DISTRIB_LINE_NUM>2</DISTRIB_LINE_NUM>
        <ACCOUNT >750190</ACCOUNT >
        <DEPTID>3059000</DEPTID>
        <MERCHANDISE_AMT>15</MERCHANDISE_AMT>
        <BUSINESS_UNIT_PC>GRT01</BUSINESS_UNIT_PC>
        <ACTIVITY_ID>1</ACTIVITY_ID>
        <FUND_CODE>3900</FUND_CODE>
        <PROGRAM_CODE >C001</PROGRAM_CODE >
        <PROJECT_ID>1002159</PROJECT_ID>
        <VOUCHER_ID>NEXT</VOUCHER_ID>
       </VCHR_DIST_STG>
    </VCHR_LINE_STG>
    <VCHR_LINE_STG>
    <BUSINESS_UNIT>BU</BUSINESS_UNIT>
    <VOUCHER_LINE_NUM>3</VOUCHER_LINE_NUM>
    <DESCR>Miles</DESCR>
    <MERCHANDISE_AMT>45</MERCHANDISE_AMT>
    <VOUCHER_ID>NEXT</VOUCHER_ID>
    <VCHR_DIST_STG>
        <BUSINESS_UNIT>BU</BUSINESS_UNIT>
        <VOUCHER_LINE_NUM>3</VOUCHER_LINE_NUM>
        <DISTRIB_LINE_NUM>3</DISTRIB_LINE_NUM>
        <ACCOUNT >750190</ACCOUNT >
        <DEPTID>3059000</DEPTID>
        <MERCHANDISE_AMT>45</MERCHANDISE_AMT>
        <BUSINESS_UNIT_PC>GRT01</BUSINESS_UNIT_PC>
        <ACTIVITY_ID>1</ACTIVITY_ID>
        <FUND_CODE>3900</FUND_CODE>
        <PROGRAM_CODE >C001</PROGRAM_CODE >
        <PROJECT_ID>1002164</PROJECT_ID>
        <VOUCHER_ID>NEXT</VOUCHER_ID>
       </VCHR_DIST_STG>
    </VCHR_LINE_STG>
</VCHR_HDR_STG>
<VCHR_HDR_STG>
    <BUSINESS_UNIT>BU</BUSINESS_UNIT>
    <VOUCHER_ID>NEXT</VOUCHER_ID>
    <VOUCHER_STYLE>REG</VOUCHER_STYLE>
    <INVOICE_ID>CCFW029672015-07-20</INVOICE_ID>
    <INVOICE_DT>07/20/2015</INVOICE_DT>
    <VENDOR_ID>0000002962</VENDOR_ID>
    <GROSS_AMT>78</GROSS_AMT>
    <DESCR254_MIXED>Volunteer 3</DESCR254_MIXED>
    <VCHR_SRC>XLS</VCHR_SRC>
    <VCHR_LINE_STG>
    <BUSINESS_UNIT>BU</BUSINESS_UNIT>
    <VOUCHER_LINE_NUM>1</VOUCHER_LINE_NUM>
    <DESCR>Stipend</DESCR>
    <MERCHANDISE_AMT>78</MERCHANDISE_AMT>
    <VOUCHER_ID>NEXT</VOUCHER_ID>
    <VCHR_DIST_STG>
        <BUSINESS_UNIT>BU</BUSINESS_UNIT>
        <VOUCHER_LINE_NUM>1</VOUCHER_LINE_NUM>
        <DISTRIB_LINE_NUM>1</DISTRIB_LINE_NUM>
        <ACCOUNT >754100</ACCOUNT >
        <DEPTID>3140110</DEPTID>
        <MERCHANDISE_AMT>78</MERCHANDISE_AMT>
        <BUSINESS_UNIT_PC></BUSINESS_UNIT_PC>
        <ACTIVITY_ID></ACTIVITY_ID>
        <FUND_CODE>2500</FUND_CODE>
        <PROGRAM_CODE ></PROGRAM_CODE >
        <PROJECT_ID></PROJECT_ID>
        <VOUCHER_ID>NEXT</VOUCHER_ID>
    </VCHR_DIST_STG>
    </VCHR_HDR_STG>
</VCHR_HDR_STG>

For each Volunteer, we generate a Voucher Header (VCHR_HDR_STG), and for each volunteer expense category (Stipend, Miles), we generate a child Voucher line node (VCHR_HDR_STG) and Child distribution (VCHR_DIST_STG).

Some tags/items for the XMl destination file are defaulted to constants as Next, BU, REG and XLS.

Here is the mapping between the XML datasource and the XML. output:

BUSINESS_UNIT(by default to 'BU')

VOUCHER_ID(by default to 'NEXT')

VOUCHER_STYLE(by default to'NEXT')

InvoiceID > INVOICE_ID

InvoiceDate > INVOICE_DT

VendorID > VENDOR_ID

Total > GROSS_AMT

MoreInformation > DESCR254_MIXED

VCHR_SRC(by default to'XLS')

VOUCHER_LINE_NUM and DISTRIB_LINE_NUM are equals and we can use the row count of expenses by volunteer to increment VOUCHER_LINE_NUM and DISTRIB_LINE_NUM.

Category > DESCR

Amount > MERCHANDISE_AMT

AccountingDepartment > DEPTID

AccountingPCBusUnit > BUSINESS_UNIT_PC

AccountingActivityID > ACTIVITY_ID

AccountingFundCode > FUND_CODE

AccountingProgramCode > PROGRAM_CODE

AccountingProjectID > PROJECT_ID

Thank you Micheal, I am working on this ERP project as Functional. I have a small experience in coding and it was very longtime ago. I did learn recently how to design an infopath for data validation entry and in my organization, we do not have the skills for this task. I am only asking for help. Thank you again. — Saladine, Aug 05 '15 at 19:22

score 0 · Answer 1 · answered Aug 05 '15 at 20:06

So, you need to transform one XML into another, through an XSLT stylehseet:

An XSLT stylesheet is basically based on events: You must declare what node you are expecting (with <xsl:template>) and within it, the text you want to produce, and the sub-events that you expect after (with <xsl:apply-templates>).

Here I give you a skeleton stylesheet that you'll have to complete:

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:my="declare-here-the-input-namespace-uri">

<xsl:output method="xml" version="1.0" encoding="UTF-8"/>

<!--The input root node-->
<xsl:template match="/my:grSCorpProgrFields">
<VCHR_HDR_STG>
    <xsl:apply-templates select="my:grSite"></xsl:apply-templates>
</VCHR_HDR_STG>
</xsl:template>

<xsl:template match="my:grSite">
    <VCHR_HDR_STG>
        <BUSINESS_UNIT>BU</BUSINESS_UNIT>
        <VOUCHER_ID>NEXT</VOUCHER_ID>
        <VOUCHER_STYLE>REG</VOUCHER_STYLE>
        <INVOICE_ID><xsl:value-of select="my:InvoiceID"/></INVOICE_ID>
        <INVOICE_DT><xsl:value-of select="my:InvoiceDate"/></INVOICE_DT>
        ...
    </VCHR_HDR_STG>
</xsl:template>

</xsl:stylesheet>

(Notice that I've left the declaration of the namespace of xmlns:my, and neither know exactly what is the root node of the input XML.)

When you have complete your XSL stylesheet, you have to use it through the proper transformation API, which depends on which platform/language you are using.

I really appreciate your help. I will complete and apply the transformation by using Visual basic. I will publish the completed tested solution. Thanks again. — Saladine, Aug 05 '15 at 20:35

Transform one XML infopath source file to another XML destination file using XSL or Xpath

1 Answers1