Splitting based on an XML element value

Get help with installation and running here.

Moderators: DataMystic Support, Moderators, DataMystic Support, Moderators, DataMystic Support, Moderators

Post Reply
DM_Cal
Posts: 10
Joined: Thu Oct 18, 2007 9:08 pm

Splitting based on an XML element value

Post by DM_Cal »

I am trying to split the following type of XML Order using textpipe:-

<Message>
<Head>
</Head>
<Body>
<OrderHeader>
<OrderDetail><Value>A</Value><Info>ABC</Info></OrderDetail>
<OrderDetail><Value>A</Value><Info>DEF</Info></OrderDetail>
<OrderDetail><Value>B</Value><Info>GHI</Info></OrderDetail>
<OrderDetail><Value>B</Value><Info>JKL</Info></OrderDetail>
</OrderHeader>
</Body>
</Message>

What I want out is :-
File 1
<Message>
<Head>
</Head>
<Body>
<OrderHeader>
<OrderDetail><Value>A</Value><Info>ABC</Info></OrderDetail>
<OrderDetail><Value>A</Value><Info>DEF</Info></OrderDetail>
</OrderHeader>
</Body>
</Message>

and File 2
<Message>
<Head>
</Head>
<Body>
<OrderHeader>
<OrderDetail><Value>B</Value><Info>GHI</Info></OrderDetail>
<OrderDetail><Value>B</Value><Info>JKL</Info></OrderDetail>
</OrderHeader>
</Body>
</Message>

I know how to get the <Head> and </OrderHeader> etc info into the split files but I don't know how to group the <OrderDetail> together by <Value> and then split it since we could have any number of these <OrderDetail> tags with any value of <Value>.

Any ideas?
thanks
User avatar
DataMystic Support
Site Admin
Posts: 2227
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Post by DataMystic Support »

Hi DM,

If OrderDetails is all on one line, you can use a Restrict to matching lines filter with a Special\Secondary Output filter as a subfilter. You'd just use a pair of these - one for Value=A and one for Value=B.
DM_Cal
Posts: 10
Joined: Thu Oct 18, 2007 9:08 pm

Post by DM_Cal »

Sorry Simon maybe I wasn't specific enough. Unfortunately I can't guarantee what range of values I will receive. For instance I could get 2 x Value A OrderDetails, 1 x Value B, 4 x Value Cs, 3 x Value Ds, and so on. Therefore I can't have the subfilters specify what the value is since it is variable, nor can I tell how many subfilters I should have.
For instance if I take <Value> as being a location in a list of n locations then I could have from 1 to n splits to consider within that overall Order...
User avatar
DataMystic Support
Site Admin
Posts: 2227
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Post by DataMystic Support »

TP can't deal with a variable number of splits, however you could use JScript or VBScript to build a TextPipe filter to cope with a known number of splits on the fly.
DM_Cal
Posts: 10
Joined: Thu Oct 18, 2007 9:08 pm

Post by DM_Cal »

Can I maybe approach this from a different perspective? For instance if I can get the file into lines of data like this:-

<OrderDetail><Value>A</Value><Info>ABC</Info></OrderDetail>
<OrderDetail><Value>A</Value><Info>DEF</Info></OrderDetail>
<OrderDetail><Value>B</Value><Info>GHI</Info></OrderDetail>
<OrderDetail><Value>C</Value><Info>JKL</Info></OrderDetail>
<OrderDetail><Value>C</Value><Info>GHI</Info></OrderDetail>
<OrderDetail><Value>C</Value><Info>JKL</Info></OrderDetail>

Is there a way I can put in some sort of 'identifier' so that after all the values of one type it puts in some sequence of chars such as &*&*. eg I get this out:-

<OrderDetail><Value>A</Value><Info>ABC</Info></OrderDetail>
<OrderDetail><Value>A</Value><Info>DEF</Info></OrderDetail>&*&*
<OrderDetail><Value>B</Value><Info>GHI</Info></OrderDetail>&*&*
<OrderDetail><Value>C</Value><Info>JKL</Info></OrderDetail>
<OrderDetail><Value>C</Value><Info>GHI</Info></OrderDetail>
<OrderDetail><Value>C</Value><Info>JKL</Info></OrderDetail>&*&*

and then maybe split that way...is that feasible?
User avatar
DataMystic Support
Site Admin
Posts: 2227
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Post by DataMystic Support »

It's feasible - but you'd need to use scripting to achieve that result (Special\Scripting filter).
DM_Cal
Posts: 10
Joined: Thu Oct 18, 2007 9:08 pm

Post by DM_Cal »

OK I will look into it.
thanks Simon for all your help with this
Post Reply