SSDN - Nesting of records from fixed length file conversion

XML Editor

Sign Up

Search

Options

Chat

Help

News

Log in

Not Logged in

Topic

Topic Page 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Fred Foo

Subject: Nesting of records from fixed length file conversion
Author: Fred Foo
Date: 15 Jul 2012 07:46 PM
Originally Posted: 12 Jul 2012 10:25 PM

I'm using a custom converter to transform a fixed field layout file to xml. The sample input file has different record types, A and B. I've seen from some of the other posts how to use pattern matching to differentiate the record types, and that works well. What I can't seem to do is nest the B records within the A records (B recs should be a child of A). This is the input:
A1234567Diane
B100.00Shoes
B123.00Suit
A1234567Chris
B100.00Saucepan
B123.00Frying Pan

This is the output I'm getting from the conversion:
<ROOT>
<A>
<ACCTNO>1234567</ACCTNO>
<NAME>Diane</NAME>
</A>
<B>
<AMOUNT>100.00</AMOUNT>
<DESCRIPTION>Shoes</DESCRIPTION>
</B>
<B>
<AMOUNT>123.00</AMOUNT>
<DESCRIPTION>Suit</DESCRIPTION>
</B>
<A>
<ACCTNO>1234567</ACCTNO>
<NAME>Chris</NAME>
</A>
<B>
<AMOUNT>100.00</AMOUNT>
<DESCRIPTION>Saucepan</DESCRIPTION>
</B>
<B>
<AMOUNT>123.00</AMOUNT>
<DESCRIPTION>Frying Pan</DESCRIPTION>
</B>
</ROOT>

Is there a way of specifying the hierarchy of the output nodes in a custom conversion to xml? Here's what it should look like:
<ROOT>
<A>
<ACCTNO>1234567</ACCTNO>
<NAME>Diane</NAME>
<B>
<AMOUNT>100.00</AMOUNT>
<DESCRIPTION>Shoes</DESCRIPTION>
</B>
<B>
<AMOUNT>123.00</AMOUNT>
<DESCRIPTION>Suit</DESCRIPTION>
</B>
</A>

<A>
<ACCTNO>1234567</ACCTNO>
<NAME>Chris</NAME>
<B>
<AMOUNT>100.00</AMOUNT>
<DESCRIPTION>Saucepan</DESCRIPTION>
</B>
<B>
<AMOUNT>123.00</AMOUNT>
<DESCRIPTION>Frying Pan</DESCRIPTION>
</B>
</A>
</ROOT>

thanks

Ivan Pedruzzi

Subject: Nesting of records from fixed length file conversion
Author: Ivan Pedruzzi
Date: 13 Jul 2012 02:41 PM

Hi Fred,

You may need to use either XSLT or XQuery for performing these kind of tasks.

If you zip a sample of your input data and your .conv file we can get you started.

Hope this helps
Ivan Pedruzzi
Stylus Studio Team

Fred Foo

Subject: Nesting of records from fixed length file conversion
Author: Fred Foo
Date: 15 Jul 2012 07:51 PM

Thanks Ivan. If it can't be done directly with the .conv, I'd like to do it with xquery if possible. I'll upload the .conv, and input and output files

fixedLength.zip

Ivan Pedruzzi

Subject: Nesting of records from fixed length file conversion
Author: Ivan Pedruzzi
Date: 16 Jul 2012 09:28 PM

The following query should help

Ivan Pedruzzi
Stylus Studio Team

declare variable $doc := doc("converter:fixedLength.conv?input.txt");

declare function local:tx($tx as element(*, xs:untyped)? ) as element(*, xs:untyped)*
{
let $next := $tx/following-sibling::*[1]
return
if (local-name($next) = 'TXN' ) then
(
$next,
local:tx($next)
)
else ()
};

<ROOT>
{
for $cust in $doc/ROOT/CUST
return
<CUST>
{
$cust/*
}
<Transactions>
{
local:tx($cust)
}
</Transactions>
</CUST>
}
</ROOT>

--------------------------
Result

<ROOT>
<CUST>
<ACCTNO>1234567</ACCTNO>
<NAME>Diane</NAME>
<Transactions>
<TXN>
<AMOUNT>100.00</AMOUNT>
<DESCRIPTION>Shoes</DESCRIPTION>
</TXN>
<TXN>
<AMOUNT>123.00</AMOUNT>
<DESCRIPTION>Suit</DESCRIPTION>
</TXN>
</Transactions>
</CUST>
<CUST>
<ACCTNO>1234567</ACCTNO>
<NAME>Chris</NAME>
<Transactions>
<TXN>
<AMOUNT>100.00</AMOUNT>
<DESCRIPTION>Saucepan</DESCRIPTION>
</TXN>
<TXN>
<AMOUNT>123.00</AMOUNT>
<DESCRIPTION>Frying Pan</DESCRIPTION>
</TXN>
</Transactions>
</CUST>
</ROOT>

Fred Foo

Subject: Nesting of records from fixed length file conversion
Author: Fred Foo
Date: 17 Jul 2012 08:16 PM

Many thanks Ivan, that does the trick

Topic Page 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!

Go to Conference:

Log In Options Username: Password:

Site Map | Privacy Policy | Terms of Use | Trademarks

Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.