XML output generation

velagapudi_k · Post by **velagapudi_k** » Mon Apr 16, 2007 8:57 am

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"?>

<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>

<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>

<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"?>

<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>

<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>

<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.

chulett · Post by **chulett** » Mon Apr 16, 2007 9:02 am

First suggestion would be to import the metadata from an xsd if at all possible, not from the xml itself. That can help tremendously.

velagapudi_k · Post by **velagapudi_k** » Mon Apr 16, 2007 9:05 am

Craig thanks for the reply. I dont know a lot about xml stuff. Can I generate an xsd from a sample xml file?

chulett · Post by **chulett** » Mon Apr 16, 2007 9:12 am

Perhaps, but it stands a very good chance of being incomplete or incorrect. Request the xsd from whomever you are generating the xml for, your external vendor in this case.

velagapudi_k · Post by **velagapudi_k** » Mon Apr 16, 2007 9:21 am

Craig, I have a .DTD file. Will that help? If yes pls help me to import metadata from it. Meanwhile, I will ask for the xsd file.

chulett · Post by **chulett** » Mon Apr 16, 2007 9:26 am

That's pretty old school. If you generated it from the xml, I'd be inclined to not use it. Best to get the xsd and use that.

velagapudi_k · Post by **velagapudi_k** » Mon Apr 16, 2007 9:31 am

No craig I didnt generate it from the XML. I got it from the vendor. He asked to use it for validation of xml once it is generated.

chulett · Post by **chulett** » Mon Apr 16, 2007 9:47 am

So... they're not going to give you an xsd?

Point the importer to your dtd file, pretty sure it will accept that. Import the metadata in the same manner as you did before.

velagapudi_k · Post by **velagapudi_k** » Mon Apr 16, 2007 9:53 am

Craig
I asked for the xsd.
When I am trying to use the dtd file it is throwing me an error.

The file specified was not recognized as a valid xml or xml schema document.

chulett · Post by **chulett** » Mon Apr 16, 2007 10:09 am

Then unfortunately it sounds like you'll need to wait for the xsd.

eostic · Post by **eostic** » Tue Apr 17, 2007 8:35 pm

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

chulett · Post by **chulett** » Tue Apr 17, 2007 8:43 pm

Hey Ernie - there's a new TV show that at first I thougt was about you. It's called "Thank God You're Here!".

:D

eostic · Post by **eostic** » Tue Apr 17, 2007 8:43 pm

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

eostic · Post by **eostic** » Tue Apr 17, 2007 8:45 pm

You are too funny. Thank goodness that "I'm" here? How many posts do I have compared to some of you true heros on this forum?

chulett · Post by **chulett** » Tue Apr 17, 2007 8:46 pm

eostic wrote:this is fairly well documented in the best practices doc that is referenced in many places on this forum.

Available at Kim Duke's website for download. Operators are standing by.