XML output generation
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 142
- Joined: Mon Jun 27, 2005 5:31 pm
- Location: Atlanta GA
XML output generation
Hi all, I have a question about xml generation. I need to generate an XML file for an external vendor every week which should have our organization hierarchy. The hierarchy we have is Region(parent)-->District(child)-->Store(child to district). The sample xml file is below
<?xml version="1.0" encoding="UTF-8"?>
<!-- Regions -->
<orgapiload>
<region org_cd="00010" org_level_cd="REGION" name="REGION 10 SOUTHEAST" locationname="USSTO">
<orgrel org_cd="USSTO" org_level_cd="AREA" eff_date="01/01/1900"/>
<status code="Active" eff_date="01/01/1900"/>
</region>
<!-- Districts -->
<district org_cd="00001" org_level_cd="DISTRICT" name="DISTRICT 1 SO FL DAD" locationname="REGION 25 SOUTH">
<orgrel org_cd="00025" org_level_cd="REG" eff_date="01/01/1900"/>
<status code="Active" eff_date="01/01/1900"/>
</district>
<!-- Stores -->
<store org_cd="00001" org_level_cd="STORE" name="00001" locationname="LAUDERDALE LAKES,FL" address1="3301 N STATE RD" address2="# 7 LAKES MALL" city="LAUDERDALE LAKES" state="FL" zip="33319" phone1="9547336100" timezone_cd="EST">
<orgrel org_cd="00010" org_level_cd="DIST" eff_date="12/29/1986"/>
<status code="Active" eff_date="12/29/1986"/>
</store>
</orgapiload>
Generating the above xml is not a problem. when I am using the metadata importer, it is recognising all the organisation levels as I am using different reserve word for different organisation levels. See bold in the above xml.
But the desired output xml is below
<?xml version="1.0" encoding="UTF-8"?>
<!-- Regions -->
<orgapiload>
<entry org_cd="00010" org_level_cd="REGION" name="REGION 10 SOUTHEAST" locationname="USSTO">
<orgrel org_cd="USSTO" org_level_cd="AREA" eff_date="01/01/1900"/>
<status code="Active" eff_date="01/01/1900"/>
</entry>
<!-- Districts -->
<entry org_cd="00001" org_level_cd="DISTRICT" name="DISTRICT 1 SO FL DAD" locationname="REGION 25 SOUTH">
<orgrel org_cd="00025" org_level_cd="REG" eff_date="01/01/1900"/>
<status code="Active" eff_date="01/01/1900"/>
</entry>
<!-- Stores -->
<entry org_cd="00001" org_level_cd="STORE" name="00001" locationname="LAUDERDALE LAKES,FL" address1="3301 N STATE RD" address2="# 7 LAKES MALL" city="LAUDERDALE LAKES" state="FL" zip="33319" phone1="9547336100" timezone_cd="EST">
<orgrel org_cd="00010" org_level_cd="DIST" eff_date="12/29/1986"/>
<status code="Active" eff_date="12/29/1986"/>
</entry>
</orgapiload>
When i am using the metadata importer to get the table defnition from the 2nd xml, its not recognising all the organization levels as I want the same reserve word for all the levels.
Right now I am acheiving the above xml by using 3 different jobs appending to the same file, but there is a lot of manual work to do after the jobs run, to acheive the desired xml.
Pls help me to acheive the above, if possible in one job. Any input is appreciated as I know going through this big post and understanding it takes a lot of time.
<?xml version="1.0" encoding="UTF-8"?>
<!-- Regions -->
<orgapiload>
<region org_cd="00010" org_level_cd="REGION" name="REGION 10 SOUTHEAST" locationname="USSTO">
<orgrel org_cd="USSTO" org_level_cd="AREA" eff_date="01/01/1900"/>
<status code="Active" eff_date="01/01/1900"/>
</region>
<!-- Districts -->
<district org_cd="00001" org_level_cd="DISTRICT" name="DISTRICT 1 SO FL DAD" locationname="REGION 25 SOUTH">
<orgrel org_cd="00025" org_level_cd="REG" eff_date="01/01/1900"/>
<status code="Active" eff_date="01/01/1900"/>
</district>
<!-- Stores -->
<store org_cd="00001" org_level_cd="STORE" name="00001" locationname="LAUDERDALE LAKES,FL" address1="3301 N STATE RD" address2="# 7 LAKES MALL" city="LAUDERDALE LAKES" state="FL" zip="33319" phone1="9547336100" timezone_cd="EST">
<orgrel org_cd="00010" org_level_cd="DIST" eff_date="12/29/1986"/>
<status code="Active" eff_date="12/29/1986"/>
</store>
</orgapiload>
Generating the above xml is not a problem. when I am using the metadata importer, it is recognising all the organisation levels as I am using different reserve word for different organisation levels. See bold in the above xml.
But the desired output xml is below
<?xml version="1.0" encoding="UTF-8"?>
<!-- Regions -->
<orgapiload>
<entry org_cd="00010" org_level_cd="REGION" name="REGION 10 SOUTHEAST" locationname="USSTO">
<orgrel org_cd="USSTO" org_level_cd="AREA" eff_date="01/01/1900"/>
<status code="Active" eff_date="01/01/1900"/>
</entry>
<!-- Districts -->
<entry org_cd="00001" org_level_cd="DISTRICT" name="DISTRICT 1 SO FL DAD" locationname="REGION 25 SOUTH">
<orgrel org_cd="00025" org_level_cd="REG" eff_date="01/01/1900"/>
<status code="Active" eff_date="01/01/1900"/>
</entry>
<!-- Stores -->
<entry org_cd="00001" org_level_cd="STORE" name="00001" locationname="LAUDERDALE LAKES,FL" address1="3301 N STATE RD" address2="# 7 LAKES MALL" city="LAUDERDALE LAKES" state="FL" zip="33319" phone1="9547336100" timezone_cd="EST">
<orgrel org_cd="00010" org_level_cd="DIST" eff_date="12/29/1986"/>
<status code="Active" eff_date="12/29/1986"/>
</entry>
</orgapiload>
When i am using the metadata importer to get the table defnition from the 2nd xml, its not recognising all the organization levels as I want the same reserve word for all the levels.
Right now I am acheiving the above xml by using 3 different jobs appending to the same file, but there is a lot of manual work to do after the jobs run, to acheive the desired xml.
Pls help me to acheive the above, if possible in one job. Any input is appreciated as I know going through this big post and understanding it takes a lot of time.
Venkat Velagapudi
-
- Premium Member
- Posts: 142
- Joined: Mon Jun 27, 2005 5:31 pm
- Location: Atlanta GA
-
- Premium Member
- Posts: 142
- Joined: Mon Jun 27, 2005 5:31 pm
- Location: Atlanta GA
-
- Premium Member
- Posts: 142
- Joined: Mon Jun 27, 2005 5:31 pm
- Location: Atlanta GA
-
- Premium Member
- Posts: 142
- Joined: Mon Jun 27, 2005 5:31 pm
- Location: Atlanta GA
As noted before, DTD's are pretty much "old school" nowadays. The new XMLPack doesn't support them. The old xml meta data importer for the old stages (which are now deprecated) supported them, but I wouldn't advise going back to those -- the new Stages are much better. Find yourself a copy of XMLSpy or other products that support DTD to XSD translation, and then import that. It may not be perfect, but it should be close, depending on the complexity of the DTD.
..or, just import the meta data via the XML instance document itself. It will work, but you risk missing columns that aren't in the XML document you used for import, and all the datatypes will be varchar 255 instead of more realistic values.
Ernie
..or, just import the meta data via the XML instance document itself. It will work, but you risk missing columns that aren't in the XML document you used for import, and all the datatypes will be varchar 255 instead of more realistic values.
Ernie
of course, I should first have looked at the original problem. Sorry. Hard to say "exactly" what you need to do, but it sounds like you are most of the way there already --- it sounds as though these are like mutually exclusive record types (filtered somehow?) if you are today using three separate jobs. Putting the logic for all three together in the same job is not easy, but it's not impossible. I'll look again to consider how you might do it all in one stage, but the general idea is to handle it the same as we would if you had dramatically different sections of a single document (ie..repeating groups for multiple mailing addresses in a Purchase Order and another with line items). Generate "chunks" of XML instead of whole documents, and then feed the finished content from the XML stage into a temporary holding location, such as an rdbms or hash table if you are using Server....then pick up the pre-finished "chunks" of XML via lookups....once they are on the same row, they can be brought back together into a single XML document with one more use of the XMLOutput Stage..... this is fairly well documented in the best practices doc that is referenced in many places on this forum.
Ernie
Ernie