Phil B.
Reputation: 1
Need to transform XML document of all Siblings into XML with Parent/Child groups
I am new to XSLT. I have an XML response document that is returned from a web service which contains a header (Result_HEADER) for each suffix and the transaction details (Result_Y and Result_Addendum) for that suffix below the header. Both the header and transaction elements are in the response XML as siblings. Here is the original XML:
<?xml version="1.0" encoding="UTF-8"?>
<Peak mlns="http://peak.company.com"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<OffTime>
<CommandRs>
<ResponseHeader>
<Status>
<StatusCode>00</StatusCode>
<StatusDesc>SUCCESSFUL</StatusDesc>
</Status>
</ResponseHeader>
<AHINRs>
<Result_HEADER>
<Identifier>HEADER</Identifier>
<Account>11111111</Account>
<Suffix>0</Suffix>
<Name>JohnDoe</Name>
<FinancialInstitutionDt>44444444</FinancialInstitutionDt>
<BranchID>3</BranchID>
</Result_HEADER>
<Identifier>HEADER</Identifier>
<Account>11111111</Account>
<Suffix>0</Suffix>
<Name>JohnDoe</Name>
<FinancialInstitutionDt>44444444</FinancialInstitutionDt>
<BranchID>3</BranchID>
</Result_HEADER>
<Result_Y>
<DetailFlag>Y</DetailFlag>
<ID>2734</ID>
<BatchID>0312</BatchID>
<TraceID>6355577</TraceID>
<EffectiveDt>20180411</EffectiveDt>
<PostingCode>DS</PostingCode>
<TransactionType>AE</TransactionType>
<Amount>-45400</Amount>
<Group>8888</Group>
<Command>ACHP</Command>
<ReturnCode />
<Status>POSTED</Status>
<TypeCode>RC</TypeCode>
<CompanyName>USAA.COM PAYMNT</CompanyName>
<CompanyID>1234567890</CompanyID>
<Description>CREDIT CRD</Description>
</Result_Y>
<Result_Y>
<DetailFlag>Y</DetailFlag>
<ID>2735</ID>
<BatchID>0312</BatchID>
<TraceID>6355577</TraceID>
<EffectiveDt>20180411</EffectiveDt>
<PostingCode>DS</PostingCode>
<TransactionType>AE</TransactionType>
<Amount>-45400</Amount>
<Group>8888</Group>
<Command>ACHP</Command>
<ReturnCode />
<Status>POSTED</Status>
<TypeCode>RC</TypeCode>
<CompanyName>USAA.COM PAYMNT</CompanyName>
<CompanyID>1234567890</CompanyID>
<Description>CREDIT CRD</Description>
</Result_Y>
<Result_HEADER>
<Identifier>HEADER</Identifier>
<Account>11111111</Account>
<Suffix>2</Suffix>
<Name>JohnDoe</Name>
<FinancialInstitutionDt>44444444</FinancialInstitutionDt>
<BranchID>3</BranchID>
</Result_HEADER>
<Result_Y>
<DetailFlag>Y</DetailFlag>
<ID>2687</ID>
<BatchID>0207</BatchID>
<TraceID>3362157</TraceID>
<EffectiveDt>020180301</EffectiveDt>
<PostingCode>PA</PostingCode>
<TransactionType />
<Amount>93741</Amount>
<Group>1017</Group>
<Command>PTDD</Command>
<ReturnCode />
<Status>PENDING</Status>
<TypeCode>RC</TypeCode>
<CompanyName>VACP TREAS 310</CompanyName>
<CompanyID>1234567890</CompanyID>
<Description>XXVA BENEF</Description>
</Result_Y>
<Result_ARADDENDUM>
<OutputType>ARADDENDUM</OutputType>
<ACHAddendum>REF*48*VA COMPENSATION *02/01/18-02/28/18 \</ACHAddendum>
</Result_ARADDENDUM>
<Result_Y>
<DetailFlag>Y</DetailFlag>
<ID>2695</ID>
<BatchID>0646</BatchID>
<TraceID>0144778</TraceID>
<EffectiveDt>20180301</EffectiveDt>
<PostingCode>PA</PostingCode>
<TransactionType />
<Amount>236594</Amount>
<Group>1014</Group>
<Command>PTDD</Command>
<ReturnCode />
<Status>PENDING</Status>
<TypeCode>RC</TypeCode>
<CompanyName>DFAS-CLEVELAND</CompanyName>
<CompanyID>1234567890</CompanyID>
<Description>RET NET</Description>
</Result_Y>
<Result_Y>
<DetailFlag>Y</DetailFlag>
<ID>2725</ID>
<BatchID>0597</BatchID>
<TraceID>3366249</TraceID>
<EffectiveDt>20180330</EffectiveDt>
<PostingCode>PA</PostingCode>
<TransactionType />
<Amount>93741</Amount>
<Group>1017</Group>
<Command>PTDD</Command>
<ReturnCode />
<Status>PENDING</Status>
<TypeCode>RC</TypeCode>
<CompanyName>VACP TREAS 310</CompanyName>
<CompanyID>1234567890</CompanyID>
<Description>XXVA BENEF</Description>
</Result_Y>
<Result_ARADDENDUM>
<OutputType>ARADDENDUM</OutputType>
<ACHAddendum>REF*48*VA COMPENSATION *03/01/18-03/31/18 \</ACHAddendum>
</Result_ARADDENDUM>
<Result_Y>
<DetailFlag>Y</DetailFlag>
<ID>2734</ID>
<BatchID>0312</BatchID>
<TraceID>6355577</TraceID>
<EffectiveDt>20180411</EffectiveDt>
<PostingCode>DS</PostingCode>
<TransactionType>AE</TransactionType>
<Amount>-45400</Amount>
<Group>8888</Group>
<Command>ACHP</Command>
<ReturnCode />
<Status>POSTED</Status>
<TypeCode>RC</TypeCode>
<CompanyName>USAA.COM PAYMNT</CompanyName>
<CompanyID>1234567890</CompanyID>
<Description>CREDIT CRD</Description>
</Result_Y>
<Result_Y>
<DetailFlag>Y</DetailFlag>
<ID>2735</ID>
<BatchID>0312</BatchID>
<TraceID>6355577</TraceID>
<EffectiveDt>20180411</EffectiveDt>
<PostingCode>DS</PostingCode>
<TransactionType>AE</TransactionType>
<Amount>-45400</Amount>
<Group>8888</Group>
<Command>ACHP</Command>
<ReturnCode />
<Status>POSTED</Status>
<TypeCode>RC</TypeCode>
<CompanyName>USAA.COM PAYMNT</CompanyName>
<CompanyID>1234567890</CompanyID>
<Description>CREDIT CRD</Description>
</Result_Y>
<Result_HEADER>
<Identifier>HEADER</Identifier>
<Account>11111111</Account>
<Suffix>3</Suffix>
<Name>JohnDoe</Name>
<FinancialInstitutionDt>44444444</FinancialInstitutionDt>
<BranchID>3</BranchID>
</Result_HEADER>
<Result_Y>
<DetailFlag>Y</DetailFlag>
<ID>2732</ID>
<BatchID>0911</BatchID>
<TraceID>6314883</TraceID>
<EffectiveDt>20180403</EffectiveDt>
<PostingCode>DS</PostingCode>
<TransactionType>AE</TransactionType>
<Amount>-18638</Amount>
<Group>8888</Group>
<Command>ACHP</Command>
<ReturnCode />
<Status>PENDING</Status>
<TypeCode>RC</TypeCode>
<CompanyName>VERIZON</CompanyName>
<CompanyID>1234567890</CompanyID>
<Description>PAYMENTREC</Description>
</Result_Y>
<Result_Y>
<DetailFlag>Y</DetailFlag>
<ID>2732</ID>
<BatchID>1751</BatchID>
<TraceID>3553385</TraceID>
<EffectiveDt>20180403</EffectiveDt>
<PostingCode>PA</PostingCode>
<TransactionType />
<Amount>119391</Amount>
<Group>8888</Group>
<Command>PTDD</Command>
<ReturnCode />
<Status>PENDING</Status>
<TypeCode>RC</TypeCode>
<CompanyName>CONCUR</CompanyName>
<CompanyID>1234567890</CompanyID>
<Description>EXPENSE</Description>
</Result_Y>
<Result_ARADDENDUM>
<OutputType>ARADDENDUM</OutputType>
<ACHAddendum>Flight home for Ethridge</ACHAddendum>
</Result_ARADDENDUM>
<Result_Y>
<DetailFlag>Y</DetailFlag>
<ID>2732</ID>
<BatchID>1751</BatchID>
<TraceID>3553387</TraceID>
<EffectiveDt>20180403</EffectiveDt>
<PostingCode>PA</PostingCode>
<TransactionType />
<Amount>61500</Amount>
<Group>8888</Group>
<Command>PTDD</Command>
<ReturnCode />
<Status>PENDING</Status>
<TypeCode>RC</TypeCode>
<CompanyName>CONCUR</CompanyName>
<CompanyID>1234567890</CompanyID>
<Description>EXPENSE</Description>
</Result_Y>
<Result_ARADDENDUM>
<OutputType>ARADDENDUM</OutputType>
<ACHAddendum>Flight to CRC to Williams</ACHAddendum>
</Result_ARADDENDUM>
</AHINRs>
</CommandRs>
</OffTime>
</Peak>
I need to be able to restructure the document so that each Header element contains a Results element which contains the transaction details for that header here is the desired.XML:
<?xml version="1.0" encoding="UTF-8"?>
<Peak xmlns="http://peak.company.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<OffTime>
<CommandRs>
<ResponseHeader>
<Status>
<StatusCode>00</StatusCode>
<StatusDesc>SUCCESSFUL</StatusDesc>
</Status>
</ResponseHeader>
<AHINRs>
<Result_HEADER>
<Identifier>HEADER</Identifier>
<Account>11111111</Account>
<Suffix>0</Suffix>
<Name>JohnDoe</Name>
<FinancialInstitutionDt>44444444</FinancialInstitutionDt>
<BranchID>3</BranchID>
<Results>
<Result_Y>
<DetailFlag>Y</DetailFlag>
<ID>2734</ID>
<BatchID>0312</BatchID>
<TraceID>6355577</TraceID>
<EffectiveDt>20180411</EffectiveDt>
<PostingCode>DS</PostingCode>
<TransactionType>AE</TransactionType>
<Amount>-45400</Amount>
<Group>8888</Group>
<Command>ACHP</Command>
<ReturnCode />
<Status>POSTED</Status>
<TypeCode>RC</TypeCode>
<CompanyName>USAA.COM PAYMNT</CompanyName>
<CompanyID>1234567890</CompanyID>
<Description>CREDIT CRD</Description>
</Result_Y>
<Result_Y>
<DetailFlag>Y</DetailFlag>
<ID>2735</ID>
<BatchID>0312</BatchID>
<TraceID>6355577</TraceID>
<EffectiveDt>20180411</EffectiveDt>
<PostingCode>DS</PostingCode>
<TransactionType>AE</TransactionType>
<Amount>-45400</Amount>
<Group>8888</Group>
<Command>ACHP</Command>
<ReturnCode />
<Status>POSTED</Status>
<TypeCode>RC</TypeCode>
<CompanyName>USAA.COM PAYMNT</CompanyName>
<CompanyID>1234567890</CompanyID>
<Description>CREDIT CRD</Description>
</Result_Y>
</Results>
</Result_HEADER>
<Result_HEADER>
<Identifier>HEADER</Identifier>
<Account>11111111</Account>
<Suffix>2</Suffix>
<Name>JohnDoe</Name>
<FinancialInstitutionDt>44444444</FinancialInstitutionDt>
<BranchID>3</BranchID>
</Results>
<Result_Y>
<DetailFlag>Y</DetailFlag>
<ID>2687</ID>
<BatchID>0207</BatchID>
<TraceID>3362157</TraceID>
<EffectiveDt>020180301</EffectiveDt>
<PostingCode>PA</PostingCode>
<TransactionType />
<Amount>93741</Amount>
<Group>1017</Group>
<Command>PTDD</Command>
<ReturnCode />
<Status>PENDING</Status>
<TypeCode>RC</TypeCode>
<CompanyName>VACP TREAS 310</CompanyName>
<CompanyID>1234567890</CompanyID>
<Description>XXVA BENEF</Description>
</Result_Y>
<Result_ARADDENDUM>
<OutputType>ARADDENDUM</OutputType>
<ACHAddendum>REF*48*VA COMPENSATION *02/01/18-02/28/18 \</ACHAddendum>
</Result_ARADDENDUM>
<Result_Y>
<DetailFlag>Y</DetailFlag>
<ID>2695</ID>
<BatchID>0646</BatchID>
<TraceID>0144778</TraceID>
<EffectiveDt>20180301</EffectiveDt>
<PostingCode>PA</PostingCode>
<TransactionType />
<Amount>236594</Amount>
<Group>1014</Group>
<Command>PTDD</Command>
<ReturnCode />
<Status>PENDING</Status>
<TypeCode>RC</TypeCode>
<CompanyName>DFAS-CLEVELAND</CompanyName>
<CompanyID>1234567890</CompanyID>
<Description>RET NET</Description>
</Result_Y>
<Result_Y>
<DetailFlag>Y</DetailFlag>
<ID>2725</ID>
<BatchID>0597</BatchID>
<TraceID>3366249</TraceID>
<EffectiveDt>20180330</EffectiveDt>
<PostingCode>PA</PostingCode>
<TransactionType />
<Amount>93741</Amount>
<Group>1017</Group>
<Command>PTDD</Command>
<ReturnCode />
<Status>PENDING</Status>
<TypeCode>RC</TypeCode>
<CompanyName>VACP TREAS 310</CompanyName>
<CompanyID>1234567890</CompanyID>
<Description>XXVA BENEF</Description>
</Result_Y>
<Result_ARADDENDUM>
<OutputType>ARADDENDUM</OutputType>
<ACHAddendum>REF*48*VA COMPENSATION *03/01/18-03/31/18 \</ACHAddendum>
</Result_ARADDENDUM>
<Result_Y>
<DetailFlag>Y</DetailFlag>
<ID>2734</ID>
<BatchID>0312</BatchID>
<TraceID>6355577</TraceID>
<EffectiveDt>20180411</EffectiveDt>
<PostingCode>DS</PostingCode>
<TransactionType>AE</TransactionType>
<Amount>-45400</Amount>
<Group>8888</Group>
<Command>ACHP</Command>
<ReturnCode />
<Status>POSTED</Status>
<TypeCode>RC</TypeCode>
<CompanyName>USAA.COM PAYMNT</CompanyName>
<CompanyID>1234567890</CompanyID>
<Description>CREDIT CRD</Description>
</Result_Y>
<Result_Y>
<DetailFlag>Y</DetailFlag>
<ID>2735</ID>
<BatchID>0312</BatchID>
<TraceID>6355577</TraceID>
<EffectiveDt>20180411</EffectiveDt>
<PostingCode>DS</PostingCode>
<TransactionType>AE</TransactionType>
<Amount>-45400</Amount>
<Group>8888</Group>
<Command>ACHP</Command>
<ReturnCode />
<Status>POSTED</Status>
<TypeCode>RC</TypeCode>
<CompanyName>USAA.COM PAYMNT</CompanyName>
<CompanyID>1234567890</CompanyID>
<Description>CREDIT CRD</Description>
</Result_Y>
</Results>
</Result_HEADER>
<Result_HEADER>
<Identifier>HEADER</Identifier>
<Account>11111111</Account>
<Suffix>3</Suffix>
<Name>JohnDoe</Name>
<FinancialInstitutionDt>44444444</FinancialInstitutionDt>
<BranchID>3</BranchID>
<Results>
<Result_Y>
<DetailFlag>Y</DetailFlag>
<ID>2732</ID>
<BatchID>0911</BatchID>
<TraceID>6314883</TraceID>
<EffectiveDt>20180403</EffectiveDt>
<PostingCode>DS</PostingCode>
<TransactionType>AE</TransactionType>
<Amount>-18638</Amount>
<Group>8888</Group>
<Command>ACHP</Command>
<ReturnCode />
<Status>PENDING</Status>
<TypeCode>RC</TypeCode>
<CompanyName>VERIZON</CompanyName>
<CompanyID>1234567890</CompanyID>
<Description>PAYMENTREC</Description>
</Result_Y>
<Result_Y>
<DetailFlag>Y</DetailFlag>
<ID>2732</ID>
<BatchID>1751</BatchID>
<TraceID>3553385</TraceID>
<EffectiveDt>20180403</EffectiveDt>
<PostingCode>PA</PostingCode>
<TransactionType />
<Amount>119391</Amount>
<Group>8888</Group>
<Command>PTDD</Command>
<ReturnCode />
<Status>PENDING</Status>
<TypeCode>RC</TypeCode>
<CompanyName>CONCUR</CompanyName>
<CompanyID>1234567890</CompanyID>
<Description>EXPENSE</Description>
</Result_Y>
<Result_ARADDENDUM>
<OutputType>ARADDENDUM</OutputType>
<ACHAddendum>Flight home for Ethridge</ACHAddendum>
</Result_ARADDENDUM>
<Result_Y>
<DetailFlag>Y</DetailFlag>
<ID>2732</ID>
<BatchID>1751</BatchID>
<TraceID>3553387</TraceID>
<EffectiveDt>20180403</EffectiveDt>
<PostingCode>PA</PostingCode>
<TransactionType />
<Amount>61500</Amount>
<Group>8888</Group>
<Command>PTDD</Command>
<ReturnCode />
<Status>PENDING</Status>
<TypeCode>RC</TypeCode>
<CompanyName>CONCUR</CompanyName>
<CompanyID>1234567890</CompanyID>
<Description>EXPENSE</Description>
</Result_Y>
<Result_ARADDENDUM>
<OutputType>ARADDENDUM</OutputType>
<ACHAddendum>Flight to CRC to Williams</ACHAddendum>
</Result_ARADDENDUM>
</Results>
</Result_HEADER>
</AHINRs>
</CommandRs>
</OffTime>
</Peak>
I have been able to use the XSL shown to create the headers and add the Results element to each header. I cannot figure out how to select and insert the transactions belonging to each header as children in the correct Results element:
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="2.0" xmlns:fn="http://peak.company.com" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:template match="/">
<xsl:apply-templates select="fn:Peak"/>
</xsl:template>
<xsl:template match="fn:Peak">
<xsl:copy select=".">
<xsl:copy-of select="@*"/>
<xsl:apply-templates select="fn:OffTime"/>
</xsl:copy>
</xsl:template>
<xsl:template match="fn:OffTime">
<xsl:copy select=".">
<xsl:apply-templates select="fn:CommandRs"/>
</xsl:copy>
</xsl:template>
<xsl:template match="fn:CommandRs">
<xsl:copy select=".">
<xsl:apply-templates select="fn:ResponseHeader"/>
<xsl:apply-templates select="fn:AHINRs"/>
</xsl:copy>
</xsl:template>
<xsl:template match="fn:ResponseHeader">
<xsl:copy-of select="."/>
</xsl:template>
<xsl:template match="fn:AHINRs">
<xsl:copy select=".">
<xsl:apply-templates select="fn:Result_HEADER"/>
</xsl:copy>
</xsl:template>
<xsl:template match="fn:Result_HEADER">
<xsl:if test="position() = last()">
<xsl:call-template name="copy-children"/>
<xsl:element name="Results" namespace="http://peak.company.com"/>
</xsl:if>
<xsl:if test="position() != last()">
<xsl:call-template name="copy-children"/>
<xsl:element name="Results" namespace="http://peak.company.com"/>
</xsl:if>
</xsl:template>
<!-- Copy the children of the current node. -->
<xsl:template name="copy-children">
<xsl:copy-of select="./*"/>
</xsl:template>
</xsl:stylesheet>
Help would be greatly appreciated!
Upvotes: 0
Views: 44
Answers (1)
Martin Honnen
Reputation: 167426
I think, assuming XSLT 2 or 3, you want to apply xsl:for-each-group select="*" group-starting-with="Result_HEADER" in the context of a template matching AHINRs:
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:math="http://www.w3.org/2005/xpath-functions/math"
exclude-result-prefixes="xs math"
version="3.0">
<xsl:strip-space elements="*"/>
<xsl:output indent="yes"/>
<xsl:mode on-no-match="shallow-copy"/>
<xsl:template match="AHINRs">
<xsl:copy>
<xsl:for-each-group select="*" group-starting-with="Result_HEADER">
<xsl:copy>
<xsl:apply-templates/>
<Results>
<xsl:apply-templates select="current-group() except ."/>
</Results>
</xsl:copy>
</xsl:for-each-group>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
https://xsltfiddle.liberty-development.net/bFDb2BW has a working demo, if you are tied to XSLT 2 replace the xsl:mode with an identity transformation template.
Also looking more closely it seems your elements are supposed to be in that namespace (although the posted sample does not have a proper namespace declaration) so in your real code you probably want to start the XSLT with
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:math="http://www.w3.org/2005/xpath-functions/math"
exclude-result-prefixes="xs math"
xpath-default-namespace="http://peak.company.com"
xmlns="http://peak.company.com"
version="3.0">
to take the namespace into account.
Upvotes: 1
Related Questions
- Transform all child siblings into parent-like node
- XSLT: How to transform the xml data into a hierarchical structure using XSLT
- xslt from siblings to nested elements
- Transform an xml containing a list of child nodes to separate xml
- How to transform Xml to hierarchical Xml
- XSLT transform siblings to children
- XSLT to Transform XML into Hierarchial Format
- XML to XML using XSLT - transforming similar parent nodes
- xml to xml transformation using xslt based on parent and child relationship
- how to get a transformed xml file with all child tags of every occurance of parent tag under only one parent tag