Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everybody,
i have problem that i cannot find solution for. I'm trying to split XML element containing text into 3 parts:
<billing_attributes>Activity=AC32,Costcode=MIMO,Project=999</billing_attributes>
or
<billing_attributes>Costcode=ITD340,Project=999,Activity=AC37</billing_attributes>
or any other mix of Costcode, Project and Activity order in element.
I need to separate fields Costcode, Project and Activity.
I tried
TextBetween(billing_attributes,'Project=',',' or '<') as Project,
TextBetween(billing_attributes,'Costcode=',',' or '<') as "Cost code",
TextBetween(billing_attributes,'Activity=',',' or '<') as Activity
But it didn't work.
Thanks for your advice
Pavel
Perhaps like this:
SubField(Subfield(TextBetween(YourXmlFieldHere,'>','<'),',',1),'=',-1) as Project
SubField(Subfield(TextBetween(YourXmlFieldHere,'>','<'),',',2),'=',-1) as "Cost code"
SubField(Subfield(TextBetween(YourXmlFieldHere,'>','<'),',',3),'=',-1) as Activity
In data model viewer it looks ok. But data are not connected. It doesn't pair with other lists of values.
I have no idea what you mean.
I think your key point here is that the attributes can be in any order so looking for them in a fixed position doesn't work. Attached is a solution that uses preceding load to parse an arbitrary number of attributes in any order.
Data:
LOAD * INLINE [
BillingAttributes
<billing_attributes>Activity=AC32,Costcode=MIMO,Project=999</billing_attributes>
<billing_attributes>Costcode=ITD340,Project=999,Activity=AC37</billing_attributes>
] (delimiter is '|')
;
Attributes:
Generic LOAD RecId, Key, Value
;
LOAD
RecId,
SubField(Pair,'=',1) as Key,
SubField(Pair,'=',2) as Value
;
LOAD
RecId,
SubField(Input,',') as Pair
;
LOAD
RecNo() as RecId,
TextBetween(BillingAttributes, '>', '<') as Input
Resident Data
;
-Rob