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?
XML file with multiple repetitive columns
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 60
- Joined: Mon Dec 27, 2004 3:38 am
-
- Participant
- Posts: 60
- Joined: Mon Dec 27, 2004 3:38 am
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
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
-
- Participant
- Posts: 60
- Joined: Mon Dec 27, 2004 3:38 am
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers