AnsweredAssumed Answered

How to split delimited text into multiple elements with Groovy when XML has multiple namespaces?

Question asked by PatrickBrilino6911 on Oct 15, 2013
Latest reply on Mar 11, 2016 by rajavenkatapavanmanoj.abburi136897
I'm trying to split the data in the element serialNumbers into multiple elementa using a Data Process step with Groovy code.  I've followed the instructions here, but my XML source data has multiple namespaces in it, so the xpath expression doesn't work.

Below you can see the XML data with the serialNumbers element that I want to split out into multiple elements based on the delimiter (a space in this case).  I've also posted my groovy code that I tried to use, but my XPath expression doesn't find any elements because the XML has multiple namespaces in it.  How can I reference the element tranSales:serialNumbers below using an XPath expression?

Here is the XML:
     <tranSales:ItemFulfillment xmlns:tranSales="urn:sales_2013_1.transactions.webservices.netsuite.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" internalId="76668">
        <tranSales:createdDate>2013-07-01T15:53:33.000-07:00</tranSales:createdDate>
        <tranSales:lastModifiedDate>2013-07-04T21:37:44.000-07:00</tranSales:lastModifiedDate>
        <tranSales:entity internalId="86418">
           <platformCore:name xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com">Test Customer</platformCore:name>
        </tranSales:entity>
        <tranSales:createdFrom internalId="68640">
           <platformCore:name xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com">Sales Order #SO-000467</platformCore:name>
        </tranSales:createdFrom>
        <tranSales:transactionShipAddress xmlns:platformCommon="urn:common_2013_1.platform.webservices.netsuite.com">
           <platformCommon:shipAddressee>Test Customer</platformCommon:shipAddressee>
           <platformCommon:shipAddr1>A. Stocletlaan 202</platformCommon:shipAddr1>
           <platformCommon:shipCity>Duffel</platformCommon:shipCity>
           <platformCommon:shipState>Duffel</platformCommon:shipState>
           <platformCommon:shipZip>2570</platformCommon:shipZip>
           <platformCommon:shipCountry>_belgium</platformCommon:shipCountry>
           <platformCommon:shipIsResidential>false</platformCommon:shipIsResidential>
        </tranSales:transactionShipAddress>
        <tranSales:shipAddress>Test Customer&lt;br&gt;A. Stocletlaan 202&lt;br&gt;2570 Duffel&lt;br&gt;Belgium</tranSales:shipAddress>
        <tranSales:tranDate>2013-07-01T00:00:00.000-07:00</tranSales:tranDate>
        <tranSales:tranId>838</tranSales:tranId>
        <tranSales:shipMethod internalId="4">
           <platformCore:name xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com">UPS</platformCore:name>
        </tranSales:shipMethod>
        <tranSales:shippingCost>0.0</tranSales:shippingCost>
        <tranSales:memo>.</tranSales:memo>
        <tranSales:packageList>
           <tranSales:package>
              <tranSales:packageWeight>330.0</tranSales:packageWeight>
              <tranSales:packageDescr>UPS International</tranSales:packageDescr>
              <tranSales:packageTrackingNumber>1Z68A22EXXYYQ66610792411Z68A22E66617116551Z68A22E6661903662</tranSales:packageTrackingNumber>
           </tranSales:package>
        </tranSales:packageList>
        <tranSales:itemList>
           <tranSales:item>
              <tranSales:itemReceive>true</tranSales:itemReceive>
              <tranSales:itemName>NX-3150-128GB</tranSales:itemName>
              <tranSales:description>NX-3150, 1 Nodes, 128GB Mem</tranSales:description>
              <tranSales:location internalId="15">
                 <platformCore:name xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com">FGI - SM</platformCore:name>
              </tranSales:location>
              <tranSales:onHand>0.0</tranSales:onHand>
              <tranSales:quantity>2.0</tranSales:quantity>
              <tranSales:unitsDisplay>EA</tranSales:unitsDisplay>
         <tranSales:serialNumbers>13SM35260022 13SM35260023</tranSales:serialNumbers>
              <tranSales:item internalId="7932">
                 <platformCore:name xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com">NX-3150-128GB</platformCore:name>
              </tranSales:item>
              <tranSales:orderLine>1</tranSales:orderLine>
              <tranSales:customFieldList>
                 <platformCore:Item_Number xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com" internalId="custcol9" xsi:type="platformCore:StringCustomFieldRef">
                    <platformCore:value>NX-3150-128GB</platformCore:value>
                 </platformCore:Item_Number>
              </tranSales:customFieldList>
           </tranSales:item>
           <tranSales:item>
              <tranSales:itemReceive>true</tranSales:itemReceive>
              <tranSales:itemName>NX-3250-128GB</tranSales:itemName>
              <tranSales:description>NX-3250, 2 Nodes, 128GB Mem</tranSales:description>
              <tranSales:location internalId="15">
                 <platformCore:name xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com">FGI - SM</platformCore:name>
              </tranSales:location>
              <tranSales:onHand>0.0</tranSales:onHand>
              <tranSales:quantity>1.0</tranSales:quantity>
              <tranSales:unitsDisplay>EA</tranSales:unitsDisplay>
         <tranSales:serialNumbers>13SM35260020</tranSales:serialNumbers>
              <tranSales:item internalId="7948">
                 <platformCore:name xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com">NX-3250-128GB</platformCore:name>
              </tranSales:item>
              <tranSales:orderLine>2</tranSales:orderLine>
              <tranSales:customFieldList>
                 <platformCore:Item_Number xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com" internalId="custcol9" xsi:type="platformCore:StringCustomFieldRef">
                    <platformCore:value>NX-3250-128GB</platformCore:value>
                 </platformCore:Item_Number>
              </tranSales:customFieldList>
           </tranSales:item>
           <tranSales:item>
              <tranSales:itemReceive>true</tranSales:itemReceive>
              <tranSales:itemName>NOS-3.0.4.1</tranSales:itemName>
              <tranSales:description>SW, NOS Version 3.0.4.1</tranSales:description>
              <tranSales:location internalId="15">
                 <platformCore:name xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com">FGI - SM</platformCore:name>
              </tranSales:location>
              <tranSales:onHand>-1.0</tranSales:onHand>
              <tranSales:quantity>3.0</tranSales:quantity>
              <tranSales:unitsDisplay>EA</tranSales:unitsDisplay>
              <tranSales:item internalId="8942">
                 <platformCore:name xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com">NOS-3.0.4.1</platformCore:name>
              </tranSales:item>
              <tranSales:orderLine>3</tranSales:orderLine>
              <tranSales:customFieldList>
                 <platformCore:Item_Number xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com" internalId="custcol9" xsi:type="platformCore:StringCustomFieldRef">
                    <platformCore:value>NOS-3.0.4.1</platformCore:value>
                 </platformCore:Item_Number>
              </tranSales:customFieldList>
           </tranSales:item>
        </tranSales:itemList>
        <tranSales:customFieldList>
           <platformCore:customField xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com" internalId="custbody1" xsi:type="platformCore:SelectCustomFieldRef">
              <platformCore:value internalId="1" typeId="1"></platformCore:value>
           </platformCore:customField>
           <platformCore:customField xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com" internalId="custbody16" xsi:type="platformCore:StringCustomFieldRef">
              <platformCore:value>FCA Destination</platformCore:value>
           </platformCore:customField>
           <platformCore:SFDC_Opportunity_ID xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com" internalId="custbody9" xsi:type="platformCore:StringCustomFieldRef">
              <platformCore:value>0066000000QDy4y</platformCore:value>
           </platformCore:SFDC_Opportunity_ID>
           <platformCore:Created_From_Substring xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com" internalId="custbody_celigo_created_from" xsi:type="platformCore:StringCustomFieldRef">
              <platformCore:value>SO-000467</platformCore:value>
           </platformCore:Created_From_Substring>
           <platformCore:Customer_PO xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com" internalId="custbody_celigo_customer_po" xsi:type="platformCore:StringCustomFieldRef">
              <platformCore:value>BPO130675</platformCore:value>
           </platformCore:Customer_PO>
           <platformCore:NS_SFDC_Celigo_Item_Fulfillment__Update xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com" internalId="custbody_celigo_ns_sfdc_itm_fmt_sync_c" xsi:type="platformCore:BooleanCustomFieldRef">
              <platformCore:value>false</platformCore:value>
           </platformCore:NS_SFDC_Celigo_Item_Fulfillment__Update>
           <platformCore:Sync_Record_Ctrl xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com" internalId="custbody_celigo_rts_sync_record_ctrl" xsi:type="platformCore:BooleanCustomFieldRef">
              <platformCore:value>false</platformCore:value>
           </platformCore:Sync_Record_Ctrl>
           <platformCore:Celigo_Update xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com" internalId="custbody_celigo_rts_update" xsi:type="platformCore:BooleanCustomFieldRef">
              <platformCore:value>false</platformCore:value>
           </platformCore:Celigo_Update>
           <platformCore:Service_Item_Text xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com" internalId="custbody_celigo_service_item" xsi:type="platformCore:StringCustomFieldRef">
              <platformCore:value>S-PLAT-3150-1YR</platformCore:value>
           </platformCore:Service_Item_Text>
           <platformCore:Created_From_Transaction_Number xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com" internalId="custbody_created_from_tran_number" xsi:type="platformCore:StringCustomFieldRef">
              <platformCore:value>ERROR: Field Not Found</platformCore:value>
           </platformCore:Created_From_Transaction_Number>
           <platformCore:Customer_PO__ xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com" internalId="custbody_customer_po_num" xsi:type="platformCore:StringCustomFieldRef">
              <platformCore:value>BPO130675</platformCore:value>
           </platformCore:Customer_PO__>
           <platformCore:customField xmlns:platformCore="urn:core_2013_1.platform.webservices.netsuite.com" internalId="custbody_ship_customer" xsi:type="platformCore:SelectCustomFieldRef">
              <platformCore:value internalId="86418" typeId="-2"></platformCore:value>
           </platformCore:customField>
        </tranSales:customFieldList>
     </tranSales:ItemFulfillment>

      
And Here is My Groovy Code:
      
     import org.jdom.input.SAXBuilder;
     import org.jdom.Document;
     import org.jdom.Element;
     import org.jdom.xpath.XPath;
     import org.jdom.output.XMLOutputter;
      
     // Set the full path to the XML element containing the values to split.  How can I do this with namespaces?
     String xpathElementToSplit = "/itemFulfillment/itemList/item/serialNumbers";
      
     // Set the delimiter character separating the values.
     String delimiter = " ";
      
     // Loop through the Process Documents
     for ( int i = 0; i < dataContext.getDataCount(); i++ ) {
      
          InputStream is = dataContext.getStream(i);
          Properties props = dataContext.getProperties(i);
      
          // Build XML Document
          SAXBuilder builder = new SAXBuilder();
          Document doc = builder.build(is);
      
     //Need XPath Query with namespace
          XPath x = XPath.newInstance(xpathElementToSplit);
      
      
          // Select multiple nodes and loop through them
          myElements = x.selectNodes(doc);
      
          for (Element myElement : myElements) {
      
             // Get the element name & value
             String elementName = myElement.getName();
             String elementValue = myElement.getText();
      
             // Get parent element and remove current element
             Element parentElement = myElement.getParent();
             myElement.detach();
      
             // Loop through parts and add new Elements to parent
             String[] parts = elementValue.split(delimiter);
             for (int j=0; j<parts.length; j++) {
      
                newElement = new Element(elementName).addContent(parts[j]);
                parentElement.addContent(newElement);
      
             }
      
          }
      
      
          XMLOutputter outputter = new XMLOutputter();
          is = new ByteArrayInputStream(outputter.outputString(doc).getBytes("UTF-8"));
      
          dataContext.storeStream(is, props);
      
     }
      

Outcomes