merge multiple XML's

[kiranh_1214]

Hi ,

Can yoy please guide me how to merge multiple XML's into one target XML with new XML stage.

Source files.

Code: Select all

File_1.xml
<?xml version="1.0" encoding="UTF-8"?>
<CollateralSchema:ObjectData xmlns:CollateralSchema="http://www.AAA.com/AAASchema.xsd">
    <CollateralSchema:Request>
        <CollateralSchema:DocumentRequest>
            <CollateralSchema:accountID>100</CollateralSchema:accountID>
        </CollateralSchema:DocumentRequest>
    </CollateralSchema:Request>
</CollateralSchema:ObjectData>

File_2.xml
<?xml version="1.0" encoding="UTF-8"?>
<CollateralSchema:ObjectData xmlns:CollateralSchema="http://www.AAA.com/AAASchema.xsd">
    <CollateralSchema:Request>
        <CollateralSchema:DocumentRequest>
            <CollateralSchema:accountID>101</CollateralSchema:accountID>
        </CollateralSchema:DocumentRequest>
    </CollateralSchema:Request>
</CollateralSchema:ObjectData>

File_3.xml
<?xml version="1.0" encoding="UTF-8"?>
<CollateralSchema:ObjectData xmlns:CollateralSchema="http://www.AAA.com/AAASchema.xsd">
    <CollateralSchema:Request>
        <CollateralSchema:DocumentRequest>
            <CollateralSchema:accountID>102</CollateralSchema:accountID>
        </CollateralSchema:DocumentRequest>
    </CollateralSchema:Request>
</CollateralSchema:ObjectData>

target File: File_tgt.xml

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<CollateralSchema:ObjectData xmlns:CollateralSchema="http://www.AAA.com/AAASchema.xsd">
    <CollateralSchema:Request>
        <CollateralSchema:DocumentRequest>
            <CollateralSchema:accountID>100</CollateralSchema:accountID>
        </CollateralSchema:DocumentRequest>
    </CollateralSchema:Request>
	<CollateralSchema:Request>
        <CollateralSchema:DocumentRequest>
            <CollateralSchema:accountID>101</CollateralSchema:accountID>
        </CollateralSchema:DocumentRequest>
    </CollateralSchema:Request>
	<CollateralSchema:Request>
        <CollateralSchema:DocumentRequest>
            <CollateralSchema:accountID>102</CollateralSchema:accountID>
        </CollateralSchema:DocumentRequest>
    </CollateralSchema:Request>
</CollateralSchema:ObjectData>

[Damonsal]

You have multiple xml, use three stages and funnel it later in your job OR
Load this in each run by calling the same job 3 times or by looping via multi instance method.

[kiranh_1214]

Hi


I am sorry, I have multiple XML file in a directory for every day.

I just tried a design

ESS stage--> XML stage

in ESS stage --> program Files.
#FileName# as ls /dirmain/lan/ls *.xml but iam not able to view data in ESS

I am geeting a error like Could not open source list file " ls /dirmain/lan/ls *.xml"

Hope will get reply from our XML master (Ernie Ostic)

[chulett]

So, you need to merge any number of XML files rather than specifically three? And your error is coming from the ESS stage or the XML stage? You will be processing those XML files individually so I don't see how any kind of a 'merge' is possible without a more complex job design. Seems to me you'd need to flatten all of your daily files into a database table and then pull them back out and build a single output XML from the table.

Or just wait for Ernie.

[eostic]

....yep.

Ya gotta flatten 'em first. There's no other way to do it, unless the xml was so simple that you could just remove the headers and append....but that is highly unlikely.

But....you don't have to necessarily flatten them all the way. You could "chunk" them on the major groupings and then just put those chunks back together. Chunk is a right click option while in the tree when you pick your "root element" in the xml Parser step of the xml Stage.

If the xml docs themselves aren't horribly large, this can be easily done also in the xmlInput Stage. Just have a column called (in this example) something like:

RequestChunk Varchar and some long length.... with a Description property of /CollateralSchema:ObjectData/CollateralSchema:Request/ .
Make it a key and then you will get as many rows output as you have "Request" nodes in your document. ....then funnel and bring back together with an xmlOutput Stage or xml Stage as suggested above.

I will assume you know how to import the xml via the metadata importer and how to pick up the namespace declaration (for "Collateral Schema").

Ernie