Splitting based on an XML element value

DM_Cal · Post by **DM_Cal** » Thu Oct 18, 2007 9:27 pm

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

Post by **DataMystic Support** » Fri Oct 19, 2007 6:04 am

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 · Post by **DM_Cal** » Fri Oct 19, 2007 5:07 pm

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...

Post by **DataMystic Support** » Fri Oct 19, 2007 5:36 pm

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 · Post by **DM_Cal** » Fri Oct 19, 2007 6:17 pm

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?

Post by **DataMystic Support** » Fri Oct 19, 2007 9:26 pm

It's feasible - but you'd need to use scripting to achieve that result (Special\Scripting filter).

DM_Cal · Post by **DM_Cal** » Fri Oct 19, 2007 9:42 pm

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