xml manipulation using xMII Logic Editor

[Former Member]

Hi Friends,

I have an input xml file :-

<?xml version="1.0" encoding="UTF-8"?>

<CampaignDetails>

<CampaignID> 1</CampaignID>

<CampaignID> 2</CampaignID>

<CampaignID> 3</CampaignID>

<StartTime CampaignID="1"> st1</StartTime>

<StartTime CampaignID="2"> st2</StartTime>

<StartTime CampaignID="3"> st3</StartTime>

<EndTime CampaignID="1">et1</EndTime>

<EndTime CampaignID="2">et2</EndTime>

<EndTime CampaignID="3">et3</EndTime>

<Description CampaignID="1"> This is Campaign 1</Description>

<Description CampaignID="2"> This is Campaign 2</Description>

<Description CampaignID="3"> This is Campaign 3</Description>

</CampaignDetails>

I want to manipulate this xml file as per the following :-

<?xml version="1.0" encoding="UTF-8"?>

<CampaignDetails>

<Campaign>

<ID>1</ID>

<Description> This is campaign 1</Description>

<StartTime> st1</StartTime>

<EndTime> et1</EndTime>

</Campaign>

<Campaign>

<ID>1</ID>

<Description> This is campaign 2</Description>

<StartTime> st2</StartTime>

<EndTime> et2</EndTime>

</Campaign>

<Campaign>

<ID>1</ID>

<Description> This is campaign 3</Description>

<StartTime> st3</StartTime>

<EndTime> et3</EndTime>

</Campaign>

</CampaignDetails>

Can anybody please tell me as to how to proceed using xMII Logic Editor?? I have tried a lot but I am failing.

And Please explain the steps in detail.

Thanks in Advance.

Lipsa.

[Former Member · Accepted solution]

Here's what I'd do:

1) Add an XML loader that points to your input XML, and name the action CampaignLoader

2) Create two local properties of type XML, named CampaignDetails and Campaign, and one local property of type String, named CampaignID.

2a) For the contents of the first XML property (CampaignDetails ) type in:

<CampaignDetails/>

2b) For the contents of the second XML property (Campaign) type in :

<Campaign>
<ID/>
<Description/>
<StartTime/>
<EndTime/>
</Campaign>

3) In a new sequence, add a repeater action and call it CampaignRepeater. For the repeat expression, use:

CampaignLoader.XmlContent{/CampaignDetails/CampaignID}

4) Because your data is "dirty" (there are extra spaces in your CampaignID elements), we'll need to add a cleanup step. In a new sequence, add an assignment action. Assign the following expression to Local.CampaignID in an "<b>assign value</b>" link:

stringtrim(CampaignRepeater.Output{/CampaignID})

5) In a new sequence, add an assignment action. You'll define four "<b>assign value</b>" links with the following target/expression(s):

Expression -> Target

Local.CampaignID --> Local.Campaign{/Campaign/ID} 

CampaignLoader.XmlContent{/CampaignDetails/StartTime[@CampaignID = '#Local.CampaignID#']}   -->  Local.Campaign{/Campaign/StartTime}   

CampaignLoader.XmlContent{/CampaignDetails/EndTime[@CampaignID = '#Local.CampaignID#']}  -->  Local.Campaign{/Campaign/EndTime}  

CampaignLoader.XmlContent{/CampaignDetails/EndTime[@CampaignID = '#Local.CampaignID#']}   -->  Local.Campaign{/Campaign/Description}

The magic in these assignments is the use of what is called a "dynamic link" - allowing the XPath to be dynamic, with embedded values.

6) In a new sequence, add an assignment action. You'll define one "<b>append xml</b>" link with the following target/expression:

Local.Campaign{/Campaign}  --> Local.CampaignDetails{/CampaignDetails}

7) After completing these steps, the XML is in your desired format in the Local parameter CampaignDetails, and you can do whatever you want with it!

- Rick

[Former Member · Accepted solution]

Hi Rick,

Thank you for such a detailed explanation.

But, I still dont get the output. I have assigned the Local parameter CampaignDetails to a Transaction output parameter of string type. And I get the result as

<?xml version="1.0" encoding="UTF-8" ?>

- <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

- <soap:Body>

- <XacuteResponse xmlns="http://www.lighthammer.com/Xacute">

- <Rowsets DateCreated="2007-05-11T12:10:28" EndDate="2007-05-11T12:10:28" StartDate="2007-05-11T12:10:28" Version="11.5.1">

- <Rowset>

- <Columns>

<Column Description="" MaxRange="0" MinRange="0" Name="Output" SQLDataType="1" SourceColumn="Output" />

</Columns>

- <Row>

<b><Output><?xml version="1.0" encoding="UTF-8"?><CampaignDetails/></Output></b>

</Row>

</Rowset>

</Rowsets>

</XacuteResponse>

</soap:Body>

</soap:Envelope>

I have followed the steps as you wrote.

Still not getting the desirable output. Please Help.

Thanks,

Lipsa.

[Former Member · Accepted solution]

Hi Rick,

The problem is solved.

I am now getting the desirable output.

Thank you very much.

Regards,

Lipsa.

[Former Member · Accepted solution]

Hi Friends,

I have a xml file :-

<?xml version="1.0" encoding="UTF-8"?>

<CampaignDetails>

<CampaignID> 1</CampaignID>

<CampaignID> 2</CampaignID>

<CampaignID> 3</CampaignID>

<CampaignID>4</CampaignID>

<StartTime CampaignID="1"> st1</StartTime>

<StartTime CampaignID="2"> st2</StartTime>

<StartTime CampaignID="3"> st3</StartTime>

<StartTime CampaignID="4">st4</StartTime>

<EndTime CampaignID="1">et1</EndTime>

<EndTime CampaignID="2">et2</EndTime>

<EndTime CampaignID="3">et3</EndTime>

<EndTime CampaignID="4">et4</EndTime>

<Description CampaignID="1"> This is Campaign 1</Description>

<Description CampaignID="2"> This is Campaign 2</Description>

<Description CampaignID="3"> This is Campaign 3</Description>

<Description CampaignID="4"> This is Campaign 4</Description>

</CampaignDetails>

But this time the output file is having a different structure.

<?xml version="1.0" encoding="UTF-8"?>

<Location>

<CampaignDetails>

<Campaign>

<ID>1</ID>

<Description> This is Campaign 1</Description>

<StartTime> st1</StartTime>

<EndTime>et1</EndTime>

</Campaign>

<Campaign>

<ID>2</ID>

<Description> This is Campaign 2</Description>

<StartTime> st2</StartTime>

<EndTime>et2</EndTime>

</Campaign>

</CampaignDetails>

<CampaignDetails>

<Campaign>

<ID>3</ID>

<Description> This is Campaign 3</Description>

<StartTime> st3</StartTime>

<EndTime>et3</EndTime>

</Campaign>

<Campaign>

<ID>4</ID>

<Description> This is Campaign 4</Description>

<StartTime>st4</StartTime>

<EndTime>et4</EndTime>

</Campaign>

</CampaignDetails>

</Location>

How do I proceed??

Thanks in Advance,

Lipsa.

[Former Member · Accepted solution]

This is exactly the same technique as the other solution, except simply change the initial content of the local variable CampaignDetails to:

<Location><CampaignDetails/></Location>

Then, when you do the Append Xml assignment, the target will be:

Local.CampaignDetails{/Location/CampaignDetails}

- Rick

[Former Member · Accepted solution]

Hi Rick,

The desirable format has to be something like this :-

<Location>

<CampaignDetails>

<Campaign>

<Id>1<Id/>

<Description>This is CampaignID1<Description/>

<StartTime>st1<StartTime/>

<EndTime>et1<EndTime/>

</Campaign>

<Campaign>

<Id>2<Id/>

<Description>This is CampaignID2<Description/>

<StartTime>st2<StartTime/>

<EndTime>et2<EndTime/>

</Campaign>

</CampaignDetails>

<CampaignDetails>

<Campaign>

<Id>3<Id/>

<Description>This is CampaignID3<Description/>

<StartTime>st3<StartTime/>

<EndTime>et3<EndTime/>

</Campaign>

<Campaign>

<Id>4<Id/>

<Description>This is CampaignID4<Description/>

<StartTime>st4<StartTime/>

<EndTime>et4<EndTime/></Campaign>

</CampaignDetails>

</Location>

and not the one which I had posted in my earlier question.

And the input xml is the same.

I was trying to get the output in this format but failed. So, help me out.

And please write the steps in details, if at all this procedure is different from that of the previous one.

Thanks in Advance,

Lipsa.

[Former Member · Accepted solution]

No offense, but I think you can take the time to figure this one out - I prefer teaching via the "Socratic Method" . The solution(s) provided by others should give you all of the information you need to make this very small modification. It will be a good exercise for you to tackle this problem, so you will be better prepared to deal with them on your own.

Take a look at the original solution, think about what the differences need to be, and I'm very confident you can figure it out.

Let us know when you're done!

[Former Member · Accepted solution]

Hi Rick,

Thanks for having that confidence in me,

I am trying this since past two days, and I really dont have any hint of how to crack this. And now I am banging my head against the wall. Any hint??? Any clue???

Regards,

Lipsa.

[Former Member · Accepted solution]

Hi Rick,

I tried but I am getting the following ouput.

<?xml version="1.0" encoding="UTF-8"?><Location><CampaignDetails> <Campaign> <ID>1</ID> <Description> This is Campaign 1</Description> <StartTime> st1</StartTime> <EndTime>et1</EndTime> </Campaign> <Campaign> <ID/> <Description/> <StartTime/> <EndTime/> </Campaign> </CampaignDetails><CampaignDetails> <Campaign> <ID>2</ID> <Description> This is Campaign 2</Description> <StartTime> st2</StartTime> <EndTime>et2</EndTime> </Campaign> <Campaign> <ID/> <Description/> <StartTime/> <EndTime/> </Campaign> </CampaignDetails><CampaignDetails> <Campaign> <ID>3</ID> <Description> This is Campaign 3</Description> <StartTime> st3</StartTime> <EndTime>et3</EndTime> </Campaign> <Campaign> <ID/> <Description/> <StartTime/> <EndTime/> </Campaign> </CampaignDetails><CampaignDetails> <Campaign> <ID>4</ID> <Description> This is Campaign 4</Description> <StartTime>st4</StartTime> <EndTime>et4</EndTime> </Campaign> <Campaign> <ID/> <Description/> <StartTime/> <EndTime/> </Campaign> </CampaignDetails></Location>

And not the desirable one.

Please help me out.

Output has to be in the following way:-

<?xml version="1.0" encoding="UTF-8"?>

<Location>

<CampaignDetails>

<Campaign>

<ID>1</ID>

<Description> This is Campaign 1</Description>

<StartTime> st1</StartTime>

<EndTime>et1</EndTime>

</Campaign>

<Campaign>

<ID>2</ID>

<Description> This is Campaign 2</Description>

<StartTime> st2</StartTime>

<EndTime>et2</EndTime>

</Campaign>

</CampaignDetails>

<CampaignDetails>

<Campaign>

<ID>3</ID>

<Description> This is Campaign 3</Description>

<StartTime> st3</StartTime>

<EndTime>et3</EndTime>

</Campaign>

<Campaign>

<ID>4</ID>

<Description> This is Campaign 4</Description>

<StartTime> st4</StartTime>

<EndTime>et4</EndTime>

</Campaign>

</CampaignDetails>

</Location>

and the input is :

<?xml version="1.0" encoding="UTF-8"?>

<CampaignDetails>

<CampaignID> 1</CampaignID>

<CampaignID> 2</CampaignID>

<CampaignID> 3</CampaignID>

<CampaignID>4</CampaignID>

<StartTime CampaignID="1"> st1</StartTime>

<StartTime CampaignID="2"> st2</StartTime>

<StartTime CampaignID="3"> st3</StartTime>

<StartTime CampaignID="4">st4</StartTime>

<EndTime CampaignID="1">et1</EndTime>

<EndTime CampaignID="2">et2</EndTime>

<EndTime CampaignID="3">et3</EndTime>

<EndTime CampaignID="4">et4</EndTime>

<Description CampaignID="1"> This is Campaign 1</Description>

<Description CampaignID="2"> This is Campaign 2</Description>

<Description CampaignID="3"> This is Campaign 3</Description>

<Description CampaignID="4"> This is Campaign 4</Description>

</CampaignDetails>

Please help.

Thanks in Advance,

Lipsa.

[Former Member · Accepted solution]

Here's the original instructions modified slightly for your new structure.

1) Add an XML loader that points to your input XML, and name the action CampaignLoader

2) Create two local properties of type XML, named CampaignDetails and Campaign, and one local property of type String, named CampaignID.

2a) For the contents of the first XML property (CampaignDetails ) type in:

<Location><CampaignDetails/></Location>

2b) For the contents of the second XML property (Campaign) type in :

<Campaign>

<ID/>

<Description/>

<StartTime/>

<EndTime/>

</Campaign>

3) In a new sequence, add a repeater action and call it CampaignRepeater. For the repeat expression, use:

CampaignLoader.XmlContent{/CampaignDetails/CampaignID}

4) Because your data is "dirty" (there are extra spaces in your CampaignID elements), we'll need to add a cleanup step. In a new sequence, add an assignment action. Assign the following expression to Local.CampaignID in an "assign value" link:

stringtrim(CampaignRepeater.Output{/CampaignID})

5) In a new sequence, add an assignment action. You'll define four "assign value" links with the following target/expression(s):

Expression -> Target

Local.CampaignID --> Local.Campaign{/Campaign/ID}

CampaignLoader.XmlContent{/CampaignDetails/StartTime[@CampaignID = '#Local.CampaignID#']} --> Local.Campaign{/Campaign/StartTime}

CampaignLoader.XmlContent{/CampaignDetails/EndTime[@CampaignID = '#Local.CampaignID#']} --> Local.Campaign{/Campaign/EndTime}

CampaignLoader.XmlContent{/CampaignDetails/EndTime[@CampaignID = '#Local.CampaignID#']} --> Local.Campaign{/Campaign/Description}

The magic in these assignments is the use of what is called a "dynamic link" - allowing the XPath to be dynamic, with embedded values.

6) In a new sequence, add an assignment action. You'll define one "append xml" link with the following target/expression:

Local.Campaign{/Campaign} --> Local.CampaignDetails{/Location/CampaignDetails}

7) After completing these steps, the XML is in your desired format in the Local parameter CampaignDetails, and you can do whatever you want with it!

- Rick

[Former Member · Accepted solution]

Hi Rick,

I am getting the output as :-

<?xml version="1.0" encoding="UTF-8"?>

<Location>

<CampaignDetails>

<Campaign>

<ID>1</ID>

<Description>This is Campaign 1</Description>

<StartTime>st1</StartTime>

<EndTime>et1</EndTime>

</Campaign>

<Campaign>

<ID>2</ID>

<Description>This is Campaign 2</Description>

<StartTime>st2</StartTime>

<EndTime>et2</EndTime>

</Campaign>

<Campaign>

<ID>3</ID>

<Description>This is Campaign 3</Description>

<StartTime>st3</StartTime>

<EndTime>et3</EndTime>

</Campaign>

<Campaign>

<ID>4</ID>

<Description>This is Campaign 4</Description>

<StartTime>st4</StartTime>

<EndTime>et4</EndTime>

</Campaign>

</CampaignDetails>

</Location>

But this is not what I wanted.

I had already cracked this issue long back.

What I wanted is as below :-

<?xml version="1.0" encoding="UTF-8"?>

<Location>

<CampaignDetails>

<Campaign>

<ID>1</ID>

<Description>This is Campaign 1</Description>

<StartTime>st1</StartTime>

<EndTime>et1</EndTime>

</Campaign>

<Campaign>

<ID>2</ID>

<Description>This is Campaign 2</Description>

<StartTime>st2</StartTime>

<EndTime>et2</EndTime>

</Campaign>

</CampaignDetails>

<CampaignDetails>

<Campaign>

<ID>3</ID>

<Description>This is Campaign 3</Description>

<StartTime>st3</StartTime>

<EndTime>et3</EndTime>

</Campaign>

<Campaign>

<ID>4</ID>

<Description>This is Campaign 4</Description>

<StartTime>st4</StartTime>

<EndTime>et4</EndTime>

</Campaign>

</CampaignDetails>

</Location>

There is a small difference in the above two structures.

I want the steps for the second structure and not the first one, the input being same.

Thanks in Advance,

Lipsa.

[Former Member · Accepted solution]

What is the "rule" to determine how many campaigns go into a CampaignDetails grouping? Is it always two?

[Former Member · Accepted solution]

Hi Rick,

Yes. Right now, it is 2.

This thread has become sufficiently long. So, I have started a new thread in the name "xml manipulation". Please look for the new thread for further conversation.

Thanks,

Lipsa.

[Former Member]

1. create an XML reference document with your final XML structure.

2. create an XML output transaction property and associate that parameter to the reference document.

3. use a repeater with xpath /CampaignDetails/CampaignID. For each loop of the repeater:

...3a. use an Assign action to set the output XML. Select the appropriate sub-node contents based on its CampaignID attribute matching the repeater's CampaignID value. For example,

Target XPath: Transaction.output{/CampaignDetails/Campaign/Description}
expression:
Transaction.input{/Description[@CampaignID=Repeater.Output{/CampaignID}]

...3b. rinse and repeat for each output node