XML file with multiple repetitive columns

[vimali balakrishnan]

Hi

I am trying to populate an XML file with two columns with repitive values to a table in Oracle database.The XML file structure goes like this -


<xs:schema id="UserPrivilegesDelta" targetNamespace="urn:mci.urm.pmi:export:UserPrivilegesDelta:v1" xmlns:mstns="urn:mci.urm.pmi:export:UserPrivilegesDelta:v1" xmlns="urn:mci.urm.pmi:export:UserPrivilegesDelta:v1" xmlns:xs="http://www.w3.org/2001/XMLSchema" attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:element name="Privileges">
<xs:complexType>
<xs:sequence>
<xs:element name="UserChanges" minOccurs="0" maxOccurs="1">
<xs:complexType>
<xs:sequence>
<xs:element name="UserChange" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:attribute name="OldValue" form="unqualified" type="xs:string"/>
<xs:attribute name="NewValue" form="unqualified" type="xs:string"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:choice maxOccurs="3">
<xs:element name="Change">
<xs:complexType>
<xs:sequence>
<xs:element name="Privilege" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="User" minOccurs="1" maxOccurs="1">
<xs:complexType>
<xs:attribute name="Value" form="unqualified" type="xs:string"/>
</xs:complexType>
</xs:element>
<xs:element name="ActionList" minOccurs="1" maxOccurs="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Action" minOccurs="1" maxOccurs="unbounded">
<xs:complexType>
<xs:attribute name="Value" form="unqualified" type="xs:string"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Agency" minOccurs="1" maxOccurs="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Product" minOccurs="1" maxOccurs="unbounded">
<xs:complexType>
<xs:attribute name="Value" form="unqualified" type="xs:string"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="Value" form="unqualified" type="xs:string"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="Type" form="unqualified" type="xs:string"/>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>


In this,the columns Action and Product can have multiple values.We have designed a job with two XML Input stages to split these two columns.The data from these two XML stages would be populated to two different tables in Oracle.Is there a way to populate this XML file into a single table through a single XML stage?

[vimali balakrishnan]

Any inputs on this query?

[eostic]

Hi...

If action and product are entirely separate nodes, unrelated to each other, and each with unbounded occurs possible, then they have to be treated in separate output links from the XMLInput Stage (not necessarily separate Stages). They repeat separately, so it's like they are in separate independent relational tables. If there is an artificial relationship that you are aware of (ie...they might always have the exact same number of occurs --- if so, the xml model is poor, as they ought to be in the same node), then you could create your own key and bring them back together in the job later using various techniques.

Ernie

[chulett]

Also note that Kim Duke's website (check his sig in any kduke post) is hosting the rather helpful Using the XML Pack - Best Practices document from Ascential for us. Anyone can download it from there.

[kduke]

Welcome Ernie. An IBMer awesome.

[vimali balakrishnan]

Ernie

In any case the values have to be populated to independent relational tables?


Thanks

[chulett]

Is that a question or a statement? I can't imagine they would "have" to be populated to different tables, per se. Seems to me it would depend on the design of these target tables and how well that lined up with what you are getting. Having 'separate output links' like Ernie mentions doesn't mean they have to go to different tables or even different stages.