Dear Members,
I have a table in Oracle DB and one of its column name is INFO which has data in text format which we need to fetch in a script and create an xml file of a new table from the input.
The contents of a single cell of INFO column is like:
Area:app - aam
Clean Up Criteria:
Description:Dummy table for Automation scripts
Expected Num:Less then hundred rows
Life Cycle:Life Cycle - Undecided
Purpose:
Table Name:DUMMY_TABLE
Update Frequency:
------
Column Name:PK_REC_1
Comments:
Default Value:
Description:PK_REC_1
Domain Id:JOBREC
Life Cycle:
New Domain Ind:No
Not Null:Yes
Pk:Yes
Sub Action:New Attribute
------
Column Name:NOT_NULL_REC_1
Comments:
Default Value:
Description:NOT_NULL_REC_1
Domain Id:CUSTOMERID
Life Cycle:
New Domain Ind:No
Not Null:Yes
Pk:No
Sub Action:New Attribute
------
Column Name:NEW_COL_NO_CONST
Comments:
Default Value:
Description:NEW_COL_NO_CONST
Domain Id:GNYESNO
Life Cycle:
New Domain Ind:No
Not Null:No
Pk:No
Sub Action:New Attribute
So, we have to take the contents of this cell as input to the script and create an xml file from it in the following format:
<?xml version="1.0"?>
<Table TabName="DUMMY_TABLE" TabAlias="dumtab" TabStructLastUpdDate="2011-06-21 08:18:13" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<TabMetaData>
<TabBasics>
<ExtendableInd>Yes</ExtendableInd>
<TabArea>app/aam</TabArea>
<TabStorageProfile>Medium</TabStorageProfile>
</TabBasics>
<TabDocs>
<TabDescrip>Dummy table for Automation scripts</TabDescrip>
<TabLifeCycle>Life Cycle - Undecided</TabLifeCycle>
</TabDocs>
</TabMetaData>
<TabColumns>
<Column ColName="PK_REC_1">
<ColBasics>
<ColType>
<DmidRef RefName="JOBREC"/>
</ColType>
<Mandatory>true</Mandatory>
</ColBasics>
<ColDocs>
<ColDescrip>PK_REC_1</ColDescrip>
</ColDocs>
<ColAuditData>
<ColCreDate>2011-06-21</ColCreDate>
<ColCreTime>08:16:46</ColCreTime>
<CreatedByBB>cdom2foxgdd</CreatedByBB>
<CreatedInVersion>88_0</CreatedInVersion>
<ColSeqOrder>1</ColSeqOrder>
</ColAuditData>
</Column>
<Column ColName="NOT_NULL_REC_1">
<ColBasics>
<ColType>
<DmidRef RefName="CUSTOMERID"/>
</ColType>
<Mandatory>true</Mandatory>
</ColBasics>
<ColDocs>
<ColDescrip>NOT_NULL_REC_1</ColDescrip>
</ColDocs>
<ColAuditData>
<ColCreDate>2011-06-21</ColCreDate>
<ColCreTime>08:17:45</ColCreTime>
<CreatedByBB>cdom2foxgdd</CreatedByBB>
<CreatedInVersion>88_0</CreatedInVersion>
<ColSeqOrder>5</ColSeqOrder>
</ColAuditData>
</Column>
<Column ColName="NEW_COL_NO_CONST">
<ColBasics>
<ColType>
<DmidRef RefName="GNYESNO"/>
</ColType>
</ColBasics>
<ColAuditData>
<ColCreDate>2011-06-21</ColCreDate>
<ColCreTime>08:18:02</ColCreTime>
<CreatedByBB>cdom2foxgdd</CreatedByBB>
<CreatedInVersion>88_0</CreatedInVersion>
<ColSeqOrder>5</ColSeqOrder>
</ColAuditData>
</Column>
</TabColumns>
<TabConstraints>
<TabPk PkName="DUMMY_TABLE_PK">
<PkDeferStatus>Not Deferred</PkDeferStatus>
<ColinPk ColRef="PK_REC_1"/>
</TabPk>
</TabConstraints>
<TabUpgrd LastUpdDate="2011-06-21 08:18:12">
<Upgrd CreDate="2011-06-21 08:18:07" UpdDate="2011-06-21 08:18:12" ModVer="88_0">
<UpgrdCol ColRef="PK_REC_1" UpdDate="2011-06-21 08:18:07">
<Type>Constant</Type>
<Value>'NULL'</Value>
</UpgrdCol>
<UpgrdCol ColRef="NOT_NULL_REC_1" UpdDate="2011-06-21 08:18:12">
<Type>Constant</Type>
<Value>NULL</Value>
</UpgrdCol>
</Upgrd>
</TabUpgrd>
</Table>
<!--checkSum:3707227330-->
I see that in the above xml many tags are constant (like ColAuditData) and such a format can be repeated, but how are we supposed to add new column values for table and the subsequent values within tags.
Also, if we already have such an XML file created, how can we parse till the end of the file and add a new column if the need arises?
I am an amateur in scripting and will require a lot of help from you talented members.
Please help me understand the script too so that I can tweak it as and when the need arises.
Thank you!