XMLInput stage repetition element

[dav_mcnair]

I am trying to code the XML Input stage repetition element and not having much luck. The issue is that my repetition element will change based on the data being sent. In the example below my repetition element could be either of the following:
1) POSLog/Transaction/RetailTransaction/SaleForPickup/LineItem/Item/POSItemID
2) POSLog/Transaction/RetailTransaction/SaleForPickup/LineItem/Division/Item/POSItemID
3) POSLog/Transaction/RetailTransaction/SaleForDelivery/LineItem/Item/POSItemID
4) POSLog/Transaction/RetailTransaction/SaleForDelivery/LineItem/Division/Item/POSItemID

<?xml version="1.0" encoding="UTF-8"?>
<POSLog>
<Transaction>
<SequenceNumber>1</SequenceNumber>
<RetailTransaction>
<SaleForDelivery>
<LineItem>
<SequenceNumber>1</SequenceNumber>
<Division>
<Item>
<POSItemID>10</POSItemID>
</Item>
<Item>
<POSItemID>11</POSItemID>
</Item>
</Division>
</LineItem>
</SaleForDelivery>
</RetailTransaction>
</Transaction>
<Transaction>
<SequenceNumber>2</SequenceNumber>
<RetailTransaction>
<SaleForDelivery>
<LineItem>
<SequenceNumber>2</SequenceNumber>
<Item>
<POSItemID>20</POSItemID>
</Item>
<Item>
<POSItemID>21</POSItemID>
</Item>
</LineItem>
</SaleForDelivery>
</RetailTransaction>
</Transaction>
<Transaction>
<SequenceNumber>3</SequenceNumber>
<RetailTransaction>
<SaleForPickup>
<LineItem>
<SequenceNumber>3</SequenceNumber>
<Item>
<POSItemID>30</POSItemID>
</Item>
<Item>
<POSItemID>31</POSItemID>
</Item>
</LineItem>
</SaleForPickup>
</RetailTransaction>
</Transaction>
<Transaction>
<SequenceNumber>4</SequenceNumber>
<RetailTransaction>
<SaleForPickup>
<LineItem>
<Division>
<SequenceNumber>4</SequenceNumber>
<Item>
<POSItemID>40</POSItemID>
</Item>
<Item>
<POSItemID>41</POSItemID>
</Item>
</Division>
</LineItem>
</SaleForPickup>
</RetailTransaction>
</Transaction>
</POSLog>

Any ideas??? Do I need to break the XML into different chunks before processing it..

[eostic]

In the most basic scenario, these are four links, four separate "normalized" tables from a hierarchical XML document, and thus would each be the repetition element for their given link. The element names are the same, but that doesn't mean anything from a pure xml perspective --- they are entirely unique by the definition of their parents.

However, it's one that can be masked, certainly across the Delivery and Pickup higher level elements.... I haven't tried masking across levels......

The syntax is nicely outlined in the XML Best Practices document that Kim Duke has on his site, and is mentioned here in the forum frequently...in brief, the technique is outlined for different types of addresses and takes advantage of XPath editing --- one of the benefits to us having XSLT behind the scenes in XMLPack.

I'll "paraphrase" it here...it should end up being something like (showing column name and associated XPath for the two elements that are "above" Division):

POSItemID /POSLog/Transaction/Retail/Transaction/*[local-name()="SaleForPickup" or local-name()="SaleForDelivery"]/LineItem/Item/POSItemID

That covers the two at the Item level --- it's very possible that the wildcard will work across two levels (Item and Division), but I'd have to experiment with it. I'd try masking for the Item level and then Division level first..get that working and then play with the "across nodes" scenario. I'll try some things also, but won't be able to get to it until much later.

Let us know how it goes!

Ernie

[kduke]

That document is on my tips page. Great doc.

[dav_mcnair]

I was able to get the masking to work for the Item node and the Division node but still cannot get it to mask over both nodes to allow me to choose one repetition key. All transaction will have either a LineItem\Item\POSIDdentity\POSItemID node or a LineItem\Division\Item\POSIDdentity\POSItemID node. Anyone have ideas? I also tired one XMLInput with 2 different output links which allowed me to set the two different repetition keys but did not bring back all the columns. For example if I had the example below it does not bring back both <RetailPriceModifier> nodes. Only bring back "123".
<?xml version="1.0" encoding="UTF-8"?>
<POSLog>
<Transaction>
<Transaction>
<SequenceNumber>1</SequenceNumber>
<RetailTransaction>
<SaleForDelivery>
<LineItem>
<SequenceNumber>2</SequenceNumber>
<RetailPriceModifier>
<ReasonCode>123</ReasonCode>
</RetailPriceModifier>
<RetailPriceModifier>
<ReasonCode>456</ReasonCode>
</RetailPriceModifier>
<Item>
<POSItemID>20</POSItemID>
</Item>
<Item>
<POSItemID>21</POSItemID>
</Item>
</LineItem>
</SaleForDelivery>
</RetailTransaction>
</Transaction>
</POSLog>

[eostic]

Too bad you couldn't mask across nodes levels. I had my doubts there, although there still may be some advanced xpath that we haven't tried yet.

Nevertheless, two links should always work, and then you can just figure out from there how to bring your data together.

...Now, RetailPriceModifier is a whole different thing altogether.... welcome to the world of multi-path hierarchies. RetailPriceModifer, in the example above, is it's own repeating group beneath LineItem. It will be described in its own table definition, and it's own (third) link, with ReasonCode as its repeating element.

All that's happening is that DataStage is "normalizing" this xml into its set of meanful relational tables.

Ernie