XML to pipe-delimited - How to copy/duplicate data to different rows

Question

I'm new to XSLT and trying to transform from XML to pipe-delimited format. The problem that I'm having is that in the output, each claim has to be duplicated for each service line.

Expected Output:

EP030315706890704|TESTSUBMITTER|FAMILY HEALTHCARE|1122334455|1|99214|179.00
EP030315706890704|TESTSUBMITTER|FAMILY HEALTHCARE|1122334455|2|2000F|0.00
EP030315706890705|TESTSUBMITTER2|FAMILY HEALTHCARE|1122334455|1|99214|179.00
EP030315706890705|TESTSUBMITTER2|FAMILY HEALTHCARE|1122334455|2|2000F|0.00

Input XML looks as follows:

<payloadContainer>
   <afile>
      <clm>
         <hdr>
            <corn>EP030315706890704</corn>
            <idSend>112233445</idSend>
            <nmSend>TESTSUBMITTER</nmSend> 
         </hdr>
         <provBill>
            <name>
               <nmOrg>FAMILY HEALTHCARE</nmOrg>
            </name>
            <id T="XX" P="P">1122334455</id>
         </provBill>
         <serv S="1">
            <numLine>1</numLine>
            <prof>
               <px L="S">
                  <cdPx T="HC">99214</cdPx>
               </px>
               <amtChrg>179.00</amtChrg>
            </prof>


         </serv>
         <serv S="2">
            <numLine>2</numLine>
            <prof>
               <px L="S">
                  <cdPx T="HC">2000F</cdPx>
               </px>
               <amtChrg>0.00</amtChrg>
            </prof>
         </serv>
    </clm>
    <clm>
         <hdr>
            <corn>EP030315706890705</corn>
            <idSend>112233445</idSend>
            <nmSend>TESTSUBMITTER2</nmSend> 
         </hdr>
         <provBill>
            <name>
               <nmOrg>FAMILY HEALTHCARE</nmOrg>
            </name>
            <id T="XX" P="P">1122334455</id>
         </provBill>
         <serv S="1">
            <numLine>1</numLine>
            <prof>
               <px L="S">
                  <cdPx T="HC">99214</cdPx>
               </px>
               <amtChrg>179.00</amtChrg>
            </prof>
         </serv>
         <serv S="2">
            <numLine>2</numLine>
            <prof>
               <px L="S">
                  <cdPx T="HC">2000F</cdPx>
               </px>
               <amtChrg>0.00</amtChrg>
            </prof>
         </serv>
    </clm>
  </afile>
</payloadContainer>

Desired output XML:

<Table>
<row>
.... All the fields represented here. 
</row>
</Table>

Possible solution: https://www.dropbox.com/s/wzvtzw7ihtgxx9o/claimtoRedshift.xsl

This scenario creates two row's dynamically. However, I'm still stuck at how to duplicate for each service line.

I suggest you post a separate question if you also need an XML output - and show there the **exact** output you expect to get in that format - not a blanket statement like "*All the fields represented here.*". — michael.hor257k, Feb 19 '16 at 21:29

score 0 · Answer 1 · answered Feb 19 '16 at 21:25

I don't see the connection of the linked XSLT to the question presented here. AFAICT, the following stylesheet will return the expected pipe-delimited output:

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" encoding="UTF-8"/>

<xsl:template match="/payloadContainer">
    <xsl:for-each select="afile/clm">
        <xsl:variable name="common">
            <xsl:value-of select="hdr/corn"/>
            <xsl:text>|</xsl:text>
            <xsl:value-of select="hdr/nmSend"/>
            <xsl:text>|</xsl:text>
            <xsl:value-of select="provBill/name/nmOrg"/>
            <xsl:text>|</xsl:text>
            <xsl:value-of select="provBill/id"/>
            <xsl:text>|</xsl:text>
        </xsl:variable>
        <xsl:for-each select="serv">
            <xsl:value-of select="$common"/>
            <xsl:value-of select="numLine"/>
            <xsl:text>|</xsl:text>
            <xsl:value-of select="prof/px/cdPx"/>
            <xsl:text>|</xsl:text>
            <xsl:value-of select="prof/amtChrg"/>
            <xsl:text>&#10;</xsl:text>
        </xsl:for-each>
    </xsl:for-each>
</xsl:template>

</xsl:stylesheet>

Let me clarify. I'm using a product called DataDirect(not sure if you're familiar with that product). Anyway, I'm not dynamically creating the "|" between each element. The XML output that I was referencing is the output XML that is created by the DataDirect converter. It's a representation of the pipe-delimited format. So the output looks like — Clarice Ogburn, Feb 19 '16 at 22:01
Please don't post code in comments - edit your question instead. I am not familiar with DataDirect. If your question is about XSLT, then there must be an XML input, and an output which can be either XML, HTML or text. Your question shows one XML input and two outputs - that's not possible (at least not with XSLT 1.0). — michael.hor257k, Feb 19 '16 at 22:23

XML to pipe-delimited - How to copy/duplicate data to different rows

1 Answers1