Clarity PPM

Expand all | Collapse all

Parsing and Loading an XML File via GEL

Jump to Best Answer
  • 1.  Parsing and Loading an XML File via GEL

    Posted 08-11-2014 01:14 PM

    I am building an integration with another system. I've done tons via SQL and .csv, but I've never tried to parse and load an xml file. I've included a sample xml file that resides on a shared drive and I can easily read. I have the java to find the location/file. The problem is parsing.

     

    Here is the sample:

    <?xml version="1.0" encoding="UTF-8"?>

    <Project:Application>

                    <header:SubmissionHeader>

                                    <header:Name>CA Technologies</header:Name>

                                    <header:Number>PRJ12345</header:Number>

                                    <header:Title>XML File Parse</header:Title>

                    </header:SubmissionHeader>

                    <Project:Forms>

                                    <Budget:Budget>

                                                    <Budget1:ID>12345</Budget1:ID>

                                                    <Budget1:Type>Project</Budget1:Type>

                    </Project:Forms>

    </Project:Application>

     

    Here is what I traditionally use for csv:

     

    <core:catch var="filerr">

      <file:readFile commentIndicator="#" delimiter="," embedded="false" fileName="${wrkDir}/${wrkFile}" var="input"/>

      </core:catch>

      <core:choose>

      <core:when test="${filerr != null}">

      <gel:log level="ERROR">Error: ${fileerr}.  Check input File. "${wrkDir}/${wrkFile}"</gel:log>

      </core:when>

      <core:otherwise>

     

     

                <core:forEach begin="1" indexVar="i" items="${input.rows}" step="1" var="row">

                <!-- Start of CSV Variables-->

     

     

      <core:set value="${row[0]}" var="record_type"/>

      <core:set value="${row[1]}" var="id"/>

      <core:set value="${row[2]}" var="segment_date"/>

      <core:set value="${row[3]}" var="segment_amount"/>

      <core:set value="${row[4]}" var="time_stamp"/>

     

    From here, I have them in variables and can xog into the staging object, yada, yada, away we go. But how do I do the same with an XML file instead of a csv?


    Thanks for the help. 



  • 2.  Re: Parsing and Loading an XML File via GEL
    Best Answer

    Posted 08-11-2014 07:10 PM

    You use the gel:parse or xml:parse tags to read in your xml file.

     

    Here is an example script:

     

    First I updated your xml to include namespaces and additional budget elements.

     

    <Project:Application
        xmlns:Budget="http://com.ggreiff/Budget"
        xmlns:Budget1="http://com/ggreiff/Budget1"
        xmlns:Project="http://com.ggreiff/Project"
        xmlns:header="http://com.ggreiff/Header">
        <header:SubmissionHeader>
            <header:Name>CA Technologies</header:Name>
            <header:Number>PRJ12345</header:Number>
            <header:Title>XML File Parse</header:Title>
        </header:SubmissionHeader>
        <Project:Forms>
            <Budget:Budget>
                <Budget1:ID>12345</Budget1:ID>
                <Budget1:Type>Project A</Budget1:Type>
            </Budget:Budget>
            <Budget:Budget>
                <Budget1:ID>6789</Budget1:ID>
                <Budget1:Type>Project B</Budget1:Type>
            </Budget:Budget>
            <Budget:Budget>
                <Budget1:ID>01234</Budget1:ID>
                <Budget1:Type>Project C</Budget1:Type>
            </Budget:Budget>
        </Project:Forms>
    </Project:Application>
    

     

    So here is an example for both gel:parse and xml:parse.

     

    <gel:script
        xmlns:core="jelly:core"
        xmlns:j="jelly.core"
        xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
        xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
        xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
        xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
        xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
        xmlns:sql="jelly:sql"
        xmlns:x="jelly:org.apache.commons.jelly.tags.xml.XMLTagLibrary"
        xmlns:util="jelly:util"
        xmlns:xog="http://www.niku.com/xog"
        xmlns:xsd="http://www.w3.org/2001/XMLSchema"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    
        <gel:log message="Start Xml File Parse Test" />
    
        <!-- jelly/gel have troubles with namespaces (or at least I haven't figure out how to get them to behave with namespaces) so let remove them -->
        <x:parse var="RemoveNameSpace"> <!-- this is our xslt to remove the namespaces -->
            <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
                <xsl:output indent="yes" method="xml" encoding="utf-8" omit-xml-declaration="yes"/>
                <xsl:template match="*">
                    <xsl:element name="{local-name()}">
                        <xsl:apply-templates select="@* | node()"/>
                    </xsl:element>
                </xsl:template>
                <xsl:template match="@*">
                    <xsl:attribute name="{local-name()}">
                        <xsl:value-of select="."/>
                    </xsl:attribute>
                </xsl:template>
                <xsl:template match="comment() | text() | processing-instruction()">
                    <xsl:copy/>
                </xsl:template>
            </xsl:stylesheet>
        </x:parse>
    
        <!-- Jelly  parses into a dom4j document / appy transformation to remove namespaces-->
        <x:parse var="xmlRawData" xml="file:///C:/temp/testxml.xml"  />
        <x:transform var="xmlData" xml= "${xmlRawData}" xslt="${RemoveNameSpace}" />
        
        <!-- This is way our xml looks like without namespaces -->
        <gel:log message="${xmlData.asXML()}" />
    
        <!-- Gel parses the document into a org.w3c.dom / appy transformation to remove namespaces-->
        <gel:parse var="gelXmlData">
            <x:transform xml= "${xmlRawData}" xslt="${RemoveNameSpace}" />
        </gel:parse>
    
        <!-- Use gel:set to get a specific node via select -->
        <gel:set var="id" select="$gelXmlData//Application[1]/Forms[1]/Budget[1]/ID[1]/text()" asString="true"/>
        <gel:set var="type" select="$gelXmlData//Application[1]/Forms[1]/Budget[1]/Type[1]/text()" asString="true"/>
        <gel:log message="ID = ${id} of Type = ${type}"/>
    
    
        <!-- loop over the Budget nodes and pull the Id and Type-->
        <x:forEach var="budgetNode" select="$xmlData/Application/Forms/Budget">
            <x:set var="id" asString = "true" select="$budgetNode/ID" />    
            <x:set var="type" asString = "true" select="$budgetNode/Type" />    
            <gel:log message="ID = ${id} of Type = ${type}"/>
        </x:forEach>
    
        <gel:log message="Finish Xml File Parse Test" />
    </gel:script>
    

     

    V/r,

    Gene



  • 3.  Re: Parsing and Loading an XML File via GEL

    Posted 10-10-2014 12:52 PM

    Gene-

     

    I finally got around to this item and this worked flawlessly, thank you!

     

    I have encountered a related, but separate issue. I need to load the nodes into variables and substring them as the suffix seems to be random. Do you, or does anyone know of an elegant way to walk the tree of an xml file and load the node names into variables?

     

    Thanks!



  • 4.  Re: Parsing and Loading an XML File via GEL

    Posted 10-11-2014 03:00 AM

    Given your example xml above.

     

    <Project:Application
        xmlns:Budget="http://com.ggreiff/Budget" 
        xmlns:Budget1="http://com/ggreiff/Budget1" 
        xmlns:Project="http://com.ggreiff/Project" 
        xmlns:header="http://com.ggreiff/Header"> 
        <header:SubmissionHeader>
            <header:Name>CA Technologies</header:Name>
            <header:Number>PRJ12345</header:Number>
            <header:Title>XML File Parse</header:Title>
        </header:SubmissionHeader>
        <Project:Forms>
            <Budget:Budget>
                <Budget1:ID>12345</Budget1:ID>
                <Budget1:Type>Project A</Budget1:Type>
            </Budget:Budget>
            <Budget:Budget>
                <Budget1:ID>6789</Budget1:ID>
                <Budget1:Type>Project B</Budget1:Type>
            </Budget:Budget>
            <Budget:Budget>
                <Budget1:ID>01234</Budget1:ID>
                <Budget1:Type>Project C</Budget1:Type>
            </Budget:Budget>
        </Project:Forms>
    </Project:Application>
    
    
    
    

     

    Just XPath the nodes you need:

     

    Get all Budget1:ID nodes that start with a 1.
    /Project:Application/Project:Forms/Budget:Budget/Budget1:ID[starts-with(.,'1')]
    
    Get the Budget:Budget (parent) node for all Budget1:ID start with a 1.
    /Project:Application/Project:Forms/Budget:Budget/Budget1:ID[starts-with(.,'1')]/..
    
    Get the Project:Forms (granddad) node for all Budget1:ID start with a 1.
    /Project:Application/Project:Forms/Budget:Budget/Budget1:ID[starts-with(.,'1')]/../..
    
    Get all Budget1:ID nodes that contain 3.
    /Project:Application/Project:Forms/Budget:Budget/Budget1:ID[contains(.,'3')]
    
    Unless you mean all the nodes within the Budget1 namespace
    /Project:Application/Project:Forms/Budget:Budget/Budget1:*
    
    
    
    

     

    Once you have the nodes of interest just loop through them saving the values into variables.  Once you have them in a POJO (assuming string here) then just use the java.lang.String method(s) to perform substring,...

     

    V/r,

    Gene



  • 5.  Re: Parsing and Loading an XML File via GEL

    Posted 10-14-2014 09:21 AM

    The issue is that the names of the Budget namespace are variable from file to file.

     

    So budget could be budget:budget; it could be budget123:budget123; it could be budget_4_10:budget_4_10.

     

    About the only thing that's consistent in the file is the first two levels and that the subsequent levels always have the same root, it's just the suffix that varies.

     

    I've been trying to xpath the namespaces to see if I can get those into variables. I'll keep working on that.

     

    Would you recommend the xpath before I remove the namespaces?

     

    Thanks, Gene.



  • 6.  Re: Parsing and Loading an XML File via GEL

    Posted 10-14-2014 10:16 AM

    If you are just interested in budget nodes, you could select them via their local-name.

     

    //*[starts-with(local-name(),'Budget')]
    

     

    This will select all budget nodes no matter what their namespace is and what suffix they have.

     

    V/r,

     

    Gene



  • 7.  Re: Parsing and Loading an XML File via GEL

    Posted 05-12-2015 03:35 PM

    Hi Gene,

     

    I was trying to use your code to remove the namespace from the xml but it is not working. Can you please assist me ?

     

    <gel:parse var="xmlRawData">

        <Project xmlns:header="http://com.ggreiff/Header">

            <SubmissionHeader>

                <Name>CA Technologies</Name>

            </SubmissionHeader>

            <Forms>

                <Budget>

                    <ID>12345</ID>

                </Budget>

            </Forms>

        </Project>   

    </gel:parse>

     

    <x:parse var="RemoveNameSpace"> <!-- this is our xslt to remove the namespaces -->

        <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

            <xsl:output indent="yes" method="xml" encoding="utf-8" omit-xml-declaration="yes"/>

            <xsl:template match="*">

                <xsl:element name="{local-name()}">

                    <xsl:apply-templates select="@* | node()"/>

                </xsl:element>   

            </xsl:template>

            <xsl:template match="@*">

                <xsl:attribute name="{local-name()}">

                    <xsl:value-of select="."/>

                </xsl:attribute>

            </xsl:template>        

            <xsl:template match="comment() | text() | processing-instruction()">

                <xsl:copy/>

            </xsl:template>   

        </xsl:stylesheet>

    </x:parse>

     

    <!-- Gel parses the document into a org.w3c.dom / appy transformation to remove namespaces-->

    <gel:parse var="gelXmlData">

        <x:transform xml="${xmlRawData}" xslt="${RemoveNameSpace.}"/>

    </gel:parse>



  • 8.  Re: Parsing and Loading an XML File via GEL

    Posted 05-13-2015 01:02 AM

    What error are you getting?

     

    Do you have access to the XMLTagLibrary?

     

    V/r,

    Gene

     

    Here is a simpler version which doesn't use a file.

     

    <gel:script
        xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
        xmlns:x="jelly:org.apache.commons.jelly.tags.xml.XMLTagLibrary"
        xmlns:aw="http://www.adventure-works.com"
        >
    
        <gel:log message="Start Xml Parse Test" />
    
        <!-- jelly/gel has troubles with namespaces so let remove them -->
        <x:parse var="RemoveNameSpace">
            <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
                <xsl:output indent="yes" method="xml" encoding="utf-8" omit-xml-declaration="yes"/>
                <xsl:template match="*">
                    <xsl:element name="{local-name()}">
                        <xsl:apply-templates select="@* | node()"/>
                    </xsl:element>
                </xsl:template>
                <xsl:template match="@*">
                    <xsl:attribute name="{local-name()}">
                        <xsl:value-of select="."/>
                    </xsl:attribute>
                </xsl:template>
                <xsl:template match="comment() | text() | processing-instruction()">
                    <xsl:copy/>
                </xsl:template>
            </xsl:stylesheet>
        </x:parse>
    
        <!-- Jelly  parses into a dom4j document / appy transformation to remove namespaces-->
        <x:parse var="xmlRawData">
            <aw:PurchaseOrder PurchaseOrderNumber="99503" OrderDate="1999-10-20"> 
                <aw:Address Type="Shipping">
                    <aw:Name>Ellen Adams</aw:Name>
                    <aw:Street>123 Maple Street</aw:Street>
                    <aw:City>Mill Valley</aw:City>
                    <aw:State>CA</aw:State>
                    <aw:Zip>10999</aw:Zip>
                    <aw:Country>USA</aw:Country>
                </aw:Address>
                <aw:Address Type="Billing">
                    <aw:Name>Tai Yee</aw:Name>
                    <aw:Street>8 Oak Avenue</aw:Street>
                    <aw:City>Old Town</aw:City>
                    <aw:State>PA</aw:State>
                    <aw:Zip>95819</aw:Zip>
                    <aw:Country>USA</aw:Country>
                </aw:Address>
                <aw:DeliveryNotes>Please leave packages in shed by driveway.</aw:DeliveryNotes>
                <aw:Items>
                    <aw:Item PartNumber="872-AA">
                        <aw:ProductName>Lawnmower</aw:ProductName>
                        <aw:Quantity>1</aw:Quantity>
                        <aw:USPrice>148.95</aw:USPrice>
                        <aw:Comment>Confirm this is electric</aw:Comment>
                        <aw:ShipDate>1998-06-21</aw:ShipDate>
                    </aw:Item>
                    <aw:Item PartNumber="926-AA">
                        <aw:ProductName>Baby Monitor</aw:ProductName>
                        <aw:Quantity>2</aw:Quantity>
                        <aw:USPrice>39.98</aw:USPrice>
                        <aw:ShipDate>1999-05-21</aw:ShipDate>
                    </aw:Item>
                </aw:Items>
            </aw:PurchaseOrder>
        </x:parse>
    
        <!-- This is way our xml looks like without namespaces -->    
        <x:transform var="xmlData" xml= "${xmlRawData}" xslt="${RemoveNameSpace}" />
        <gel:log message="${xmlData.asXML()}" />
    
        <!-- Gel parses the document into a org.w3c.dom / appy transformation to remove namespaces-->
        <gel:parse var="gelXmlData">
            <x:transform xml= "${xmlRawData}" xslt="${RemoveNameSpace}" />
        </gel:parse>
    
        <!-- Gel set with a specific node select -->
        <gel:set var="ProductName" select="$gelXmlData//PurchaseOrder[1]/Items[1]/Item/ProductName[1]/text()" asString="true"/>
        <gel:set var="DeliveryNotes" select="$gelXmlData//PurchaseOrder[1]/DeliveryNotes[1]/text()" asString="true"/>
        <gel:log message="ProductName = ${ProductName} of DeliveryNotes = ${DeliveryNotes}"/>
    
        <gel:log message="Finish Xml File Parse Test" />
    
    </gel:script>
    

     

    When run in the console you should get this:

     



  • 9.  Re: Parsing and Loading an XML File via GEL