Nested Loops from XML to Flat Fiel Conversion

chilly · April 14th, 2010, 09:50 PM

I kind of knew to XSLT World. I am using XSLT 1.0

The requirement is transforming from XML to Flat File Conversion. In the sequence, where ever you see a field with 2 values, you need to split and write it into a row by looping with multiples of multiples. Not sure explaining right. But will give you an example here.

Source XML

<?xml version="1.0" encoding="UTF-8"?>
<RecordSet>
<Data>
<Field1>Field1</Field1>
<Field2>Field2</Field2>
<Field34>Field3~Field4</Field34>
<Field56>Field5~Field6</Field56>
<Field7>Field7</Field7>
<Field89>Field8~Field9</Field89>
<Field10>Field10</Field10>
</Data>
</RecordSet>

Output Flat File

Field1,Field2,Field3,Field5,Field7,Field8,Field10E SC
Field1,Field2,Field3,Field5,Field7,Field9,Field10E SC
Field1,Field2,Field3,Field6,Field7,Field8,Field10E SC
Field1,Field2,Field3,Field6,Field7,Field9,Field10E SC
Field1,Field2,Field4,Field5,Field7,Field8,Field10E SC
Field1,Field2,Field4,Field5,Field7,Field9,Field10E SC
Field1,Field2,Field4,Field6,Field7,Field8,Field10E SC
Field1,Field2,Field4,Field6,Field7,Field9,Field10E SC

If you see the above output flat conversion, it's recursive effort, to drill down from the bottom to up approach. Writing each probability of the combination of the records needed to be given as output.

In this case, the compound fields are 3 (Field3&4, Field5&6 and Field 8&9. If you look at the combination of records you output will be 8. The single entity elements will be written as is. In original source, i have 12 of these compound type Fields with multiple Split values inside.

I have some dummy data present here to help with the original structure. I hope i provided enough info, so that any guru's can help me here understanding on how to proceed.

Appreciated

mrame · April 15th, 2010, 02:57 AM

Your input was least enought to solve your problem, but with some assumption I could derive the below:
Assumed input XML:

Code:

<name>
<RecordSet>
<Data>
<Audit_Auditees>Field1</Audit_Auditees>
<Audit_Auditees>Field2</Audit_Auditees>
<Audit_Auditees>Field3~Field4</Audit_Auditees>
<Audit_Auditees>Field5~Field6</Audit_Auditees>
<Audit_Auditees>Field7</Audit_Auditees>
<Audit_Auditees>Field8~Field9</Audit_Auditees>
<Audit_Auditees>Field10</Audit_Auditees>
</Data>
</RecordSet>
</name>

XSLT code:

Code:

<xsl:stylesheet
  version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" indent="yes"/>
 
<xsl:template match="/">
<Recordset>
<xsl:for-each select="name/RecordSet">
<Data>
<Type><xsl:value-of select="Data/Type"></xsl:value-of></Type>
<xsl:for-each select="Data/Audit_Auditees">
<xsl:apply-templates select="."/>
</xsl:for-each>
</Data>
</xsl:for-each>
</Recordset>
</xsl:template>
<xsl:template match="Data/Audit_Auditees">
<Audit_Auditees>
<xsl:choose>
<xsl:when test="contains(., '~')">
<xsl:call-template name="output-tokens">
<xsl:with-param name="list"><xsl:value-of select="."/></xsl:with-param>
<xsl:with-param name="delimiter">~</xsl:with-param>
</xsl:call-template> 
</xsl:when>
<xsl:otherwise>
<xsl:call-template name="replaceCharsInString">
<xsl:with-param name="stringIn" select="string(.)"/>
<xsl:with-param name="charsIn" select="'|'"/>
<xsl:with-param name="charsOut" select="'{//}'"/>
</xsl:call-template> 
</xsl:otherwise>
</xsl:choose>
</Audit_Auditees>
</xsl:template>
 
<xsl:template name="output-tokens">
<xsl:param name="list"/>
<xsl:param name="delimiter" />
<xsl:variable name="newlist">
<xsl:choose>
<xsl:when test="contains($list, $delimiter)"><xsl:value-of select="normalize-space($list)" /></xsl:when>
<xsl:otherwise><xsl:value-of select="concat(normalize-space($list), $delimiter)"/></xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:variable name="first" select="substring-before($newlist, $delimiter)" />
<xsl:variable name="remaining" select="substring-after($newlist, $delimiter)" />
<Auditee>
<xsl:call-template name="replaceCharsInString">
<xsl:with-param name="stringIn" select="string($first)"/>
<xsl:with-param name="charsIn" select="'|'"/>
<xsl:with-param name="charsOut" select="'{//}'"/>
</xsl:call-template> 
</Auditee>
<xsl:if test="$remaining">
<xsl:call-template name="output-tokens">
<xsl:with-param name="list" select="$remaining" />
<xsl:with-param name="delimiter"><xsl:value-of select="$delimiter"/></xsl:with-param>
</xsl:call-template>
</xsl:if>
</xsl:template>
 
<xsl:template name="replaceCharsInString">
<xsl:param name="stringIn"/>
<xsl:param name="charsIn"/>
<xsl:param name="charsOut"/>
<xsl:choose>
<xsl:when test="contains($stringIn,$charsIn)">
<xsl:value-of select="concat(substring-before($stringIn,$charsIn),$charsOut)"/>
<xsl:call-template name="replaceCharsInString">
<xsl:with-param name="stringIn" select="substring-after($stringIn,$charsIn)"/>
<xsl:with-param name="charsIn" select="$charsIn"/>
<xsl:with-param name="charsOut" select="$charsOut"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$stringIn"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>

I have changed your code and tested for single element namely "Audit_Auditees". Find the output xml below:

Code:

<Recordset>
   <Data>
      <Type/>
      <Audit_Auditees>Field1</Audit_Auditees>
      <Audit_Auditees>Field2</Audit_Auditees>
      <Audit_Auditees>
         <Auditee>Field3</Auditee>
         <Auditee>Field4</Auditee>
      </Audit_Auditees>
      <Audit_Auditees>
         <Auditee>Field5</Auditee>
         <Auditee>Field6</Auditee>
      </Audit_Auditees>
      <Audit_Auditees>Field7</Audit_Auditees>
      <Audit_Auditees>
         <Auditee>Field8</Auditee>
         <Auditee>Field9</Auditee>
      </Audit_Auditees>
      <Audit_Auditees>Field10</Audit_Auditees>
   </Data>
</Recordset>

Someone should have much patience to read all your code, without relevant xml. You can post some relevant xml next time.

mhkay · April 15th, 2010, 03:10 AM

I'd be inclined to use logic like this:

Code:

<xsl:for-each select="tokenize(Field1)">
  <xsl:variable name="f1v" select="."/>
  <xsl:for-each select="tokenize(Field2)">
    <xsl:variable name="f2v" select="."/>
       ...
       <xsl:value-of select="concat($f1v, ',' $f2v, ',', ....)"/>

where tokenize is the real tokenize function if you're using XSLT 2.0, or exslt:tokenize if using XSLT 1.0.

For a further level of abstraction, to make it work over any number of "fields" irrespective of the element names, you can use head-tail recursion over the list of fields.

chilly · April 15th, 2010, 09:14 AM

Quote:

Originally Posted by mhkay

I'd be inclined to use logic like this:

Code:

<xsl:for-each select="tokenize(Field1)">
  <xsl:variable name="f1v" select="."/>
  <xsl:for-each select="tokenize(Field2)">
    <xsl:variable name="f2v" select="."/>
       ...
       <xsl:value-of select="concat($f1v, ',' $f2v, ',', ....)"/>

Hi Michael
I am sorry for the confusion. I removed my code

Now i understand string and splitting with tokenize function, where i have written the same as a template i guess.

But if you see my output, i need those probabilities to be written in each line. Because the target system is flat file processor, the system expects each value written in one line for every possibility.

I don't know how to call it, but each compound field will be split and applies multiplication to write the records.

In this case, i have 3 Compound fields * 2 fields for each Compound = 8 Rows

chilly · April 16th, 2010, 12:28 PM

Hi Michael
Would you mind sharing some knowledge here... I started counting on you.

There are no strikers for me at this moment

mhkay · April 16th, 2010, 12:37 PM

I don't really have anything to add to my original reply. I don't understand the comments you made about it. As far as I can see, it does what you need.

chilly · April 16th, 2010, 01:29 PM

OK, I have been trying to execute the code with your snippet. But getting some errors.

Here is the code

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

<xsl:template match="/">
<xsl:variable name="Data" select="name/Recordset/Data"/>
<xsl:for-each select="$Data">
<xsl:for-each select="tokenize(Audit_Auditees,'~')">
<xsl:variable name="f1v" select="$Data/."/>
<xsl:for-each select="tokenize(Audit_Auditors,'~')">
<xsl:variable name="f2v" select="$Data/."/>
<xsl:value-of select="concat($f1v, '{//}', $f2v/Audit_Auditors, '{//}')"/>
</xsl:for-each>
</xsl:for-each>
</xsl:for-each>
</xsl:template>

Error
java.lang.RuntimeException: Error: at xsl:template on line 5 column 26 of fSPLITT%7E1.XSL:
XPTY0020: Axis step child::element(Audit_Auditors, xs:anyType) cannot be used here: the context item is an atomic value

at com.exln.stylus.CSaxon8Driver.doProcessing(CSaxon8 Driver.java:269)
at com.exln.stylus.CProcessorDriver.process(CProcesso rDriver.java:104)
Splitting.xsl (5, 26)

mhkay · April 16th, 2010, 02:13 PM

Try changing to:

Code:

<xsl:template match="/">
  <xsl:variable name="Data" select="name/Recordset/Data"/>
  <xsl:for-each select="$Data">
    <xsl:variable name="this" select="."/>
    <xsl:for-each select="tokenize($this/Audit_Auditees,'~')">
      <xsl:variable name="f1v" select="$Data/."/>
      <xsl:for-each select="tokenize($this/Audit_Auditors,'~')">
etc.

chilly · April 16th, 2010, 02:51 PM

I have the code working for me. Thanks a bunch

I have one more question. I have multiple name/Recordset's are coming. But the following code only loops at all Data, but write only the first data[1]. How to loop multiple recordset

HTML Code:

	<xsl:template match="/">
		<xsl:variable name="Data" select="name/Recordset/Data"/>
		<xsl:for-each select="$Data">
			<xsl:variable name="this" select="."/>
			<xsl:for-each select="tokenize($this/Audit_Auditees,'~')">
				<xsl:variable name="f1v" select="$Data/Audit_Auditees"/>
				<xsl:for-each select="tokenize($this/Audit_Auditors,'~')">
					<xsl:variable name="f2v" select="$Data/Audit_Auditors"/>
					<xsl:for-each select="tokenize($this/Audit_Device,'~')">
						<xsl:variable name="f3v" select="$Data/Audit_Device"/>
						<xsl:for-each select="tokenize($this/Audit_Lead_Auditor,'~')">
							<xsl:variable name="f4v" select="$Data/Audit_Lead_Auditor"/>
							<xsl:for-each select="tokenize($this/Audit_Sub_System,'|')">
								<xsl:variable name="f5v" select="$Data/Audit_Sub_System"/>
								<xsl:for-each select="tokenize($this/Audit_Sub_Type,',')">
									<xsl:variable name="f6v" select="$Data/Audit_Sub_Type"/>
									<xsl:for-each select="tokenize($this/Closing_Meeting_Attendees,'~')">
										<xsl:variable name="f7v" select="$Data/Closing_Meeting_Attendees"/>
										<xsl:for-each select="tokenize($this/MPG_Audit_Site,'|')">
											<xsl:variable name="f8v" select="$Data/MPG_Audit_Site"/>
											<xsl:for-each select="tokenize($this/MPG_Auditee_Organization,',')">
												<xsl:variable name="f9v" select="$Data/MPG_Auditee_Organization"/>
												<xsl:for-each select="tokenize($this/MPG_Auditing_Body,'|')">
													<xsl:variable name="f10v" select="$Data/MPG_Auditing_Body"/>
													<xsl:for-each select="tokenize($this/MPG_Supplier_Capabilities,',')">
														<xsl:variable name="f11v" select="$Data/MPG_Supplier_Capabilities"/>
														<xsl:for-each select="tokenize($this/Opening_Meeting_Attendees,'~')">
															<xsl:variable name="f12v" select="$Data/Opening_Meeting_Attendees"/>
																<xsl:value-of select="concat($f1v, '{//}', $f2v, '{//}', $f3v, '{//}', $f4v, '{//}', $f5v, '{//}', $f6v, '{//}', $f7v, '{//}', $f8v, '{//}', $f9v, '{//}', $f10v, '{//}', $f11v, '{//}', $f12v, '{//}', '&#xA;')"/>
														</xsl:for-each>
														</xsl:for-each>
													</xsl:for-each>
												</xsl:for-each>
											</xsl:for-each>
										</xsl:for-each>
									</xsl:for-each>
								</xsl:for-each>
							</xsl:for-each>
						</xsl:for-each>
					</xsl:for-each>
				</xsl:for-each>
			</xsl:for-each>
</xsl:template>

Also have one more issue while the string doesn't have any tokens to split, it is throwing an error, not sure how to resolve this
FORX0003: The regular expression in tokenize() must not be one that matches a zero-length string

at com.exln.stylus.CSaxon8Driver.doProcessing(CSaxon8 Driver.java:269)
at com.exln.stylus.CProcessorDriver.process(CProcesso rDriver.java:104)
Splitting.xsl (17, 68)
Error: at xsl:for-each on line 17 column 68

chilly · April 16th, 2010, 05:07 PM

Sorry for bugging you again and again. Now i am posting XSLT, Results and Input XML Values as well.

It seems like the code isn't working for the results as expected though.

Code

HTML Code:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
	<xsl:output method="text"/>
	<xsl:template match="/">
		<xsl:variable name="Data" select="name/Recordset/Data"/>
		<xsl:for-each select="$Data">
			<xsl:variable name="this" select="."/>
			<xsl:for-each select="tokenize($this/Audit_Auditees,'~')">
				<xsl:variable name="f1v" select="$Data/Audit_Auditees"/>
				<xsl:for-each select="tokenize($this/Audit_Auditors,'~')">
					<xsl:variable name="f2v" select="$Data/Audit_Auditors"/>
					<xsl:for-each select="tokenize($this/Audit_Device,',')">
						<xsl:variable name="f3v" select="$Data/Audit_Device"/>
						<xsl:value-of select="concat($f1v, '{//}', $f2v, '{//}', $f3v, '{//}','&#xA;')"/>
					</xsl:for-each>
				</xsl:for-each>
			</xsl:for-each>
		</xsl:for-each>
	</xsl:template>
</xsl:stylesheet>

Result:

HTML Code:

Auditee 1|Title 1~Auditee 2|Title 2~Auditee 3|Title 3{//}~Auditor 1|Auditor Title 1{//}Device 1,Device 2,Device 3{//}
Auditee 1|Title 1~Auditee 2|Title 2~Auditee 3|Title 3{//}~Auditor 1|Auditor Title 1{//}Device 1,Device 2,Device 3{//}
Auditee 1|Title 1~Auditee 2|Title 2~Auditee 3|Title 3{//}~Auditor 1|Auditor Title 1{//}Device 1,Device 2,Device 3{//}
Auditee 1|Title 1~Auditee 2|Title 2~Auditee 3|Title 3{//}~Auditor 1|Auditor Title 1{//}Device 1,Device 2,Device 3{//}
Auditee 1|Title 1~Auditee 2|Title 2~Auditee 3|Title 3{//}~Auditor 1|Auditor Title 1{//}Device 1,Device 2,Device 3{//}
Auditee 1|Title 1~Auditee 2|Title 2~Auditee 3|Title 3{//}~Auditor 1|Auditor Title 1{//}Device 1,Device 2,Device 3{//}
Auditee 1|Title 1~Auditee 2|Title 2~Auditee 3|Title 3{//}~Auditor 1|Auditor Title 1{//}Device 1,Device 2,Device 3{//}
Auditee 1|Title 1~Auditee 2|Title 2~Auditee 3|Title 3{//}~Auditor 1|Auditor Title 1{//}Device 1,Device 2,Device 3{//}

Source:

HTML Code:

<?xml version="1.0" encoding="UTF-8"?>
<name>
<Recordset>
		<Data>
			<Audit_Auditees>Auditee 1|Title 1~Auditee 2|Title 2~Auditee 3|Title 3</Audit_Auditees>
			<Audit_Auditors>~Auditor 1|Auditor Title 1</Audit_Auditors>
			<Audit_Device>Device 1,Device 2,Device 3</Audit_Device>
			<Audit_Lead_Auditor>Murray, Christopher|Murray, Christopher</Audit_Lead_Auditor>
			<Audit_Sub_System>SubSystem1|Subsystem2|Subsystem3|Subsystem4</Audit_Sub_System>
			<Audit_Sub_Type>SubType1|SubType2|</Audit_Sub_Type>
			<Closing_Meeting_Attendees>Closing1~Closing2~Closing3</Closing_Meeting_Attendees>
			<Closing_Meeting_Held_Date>2009-04-21T17:00:00Z</Closing_Meeting_Held_Date>
			<MPG_Audit_Days>6</MPG_Audit_Days>
			<MPG_Audit_Site>Abbott Park, IL</MPG_Audit_Site>
			<MPG_Auditee_Organization>GPRD - Global Pharmaceutical R &amp; D</MPG_Auditee_Organization>
			<MPG_Auditing_Body>GPRD - Global Pharmaceutical R &amp; D</MPG_Auditing_Body>
			<MPG_Supplier_Capabilities/>
			<Opening_Meeting_Attendees>Opening1~Opening2~Opening3</Opening_Meeting_Attendees>
		</Data>
	</Recordset>
	</name>