Rename nodes with populated set

1

I am currently working with snowflake sql to query and return a data set. The data that gets returned separates the values into a series of data nodes all named “e” and the table columns into a set of rowtypes.

data

  <data>
  		<e>
  			<e><![CDATA[623]]></e>
  			<e><![CDATA[6478032]]></e>
  			<e><![CDATA[REPLACE - Commercial Service]]></e>
  			<e><![CDATA[18176]]></e>
  			<e><![CDATA[6473095]]></e>
  			<e><![CDATA[6478030]]></e>
  			<e><![CDATA[6478050]]></e>
  			<e><![CDATA[6478050]]></e>
  			<e><![CDATA[12161]]></e>
  			<e><![CDATA[Commercial Service]]></e>
  			<e><![CDATA[60587.529999999999]]></e>
  			<e><![CDATA[405 Powell Street, Waxahachie, TX 75165 USA]]></e>
  			<e><![CDATA[Commercial Service]]></e>
  			<e><![CDATA[15529]]></e>
  			<e><![CDATA[REPLACE]]></e>
  			<e><![CDATA[REPLACE - Commercial Service]]></e>
  			<e><![CDATA[REPLACE]]></e>
  			<e><![CDATA[Dallas-Ft. Worth TX]]></e>
                           </e>
  		<e>

column names

 <rowType>
  			<e>
  				<byteLength><![CDATA[16777216]]></byteLength>
  				<collation json_null="true"/>
  				<database><![CDATA[PRD_EDW]]></database>
  				<length><![CDATA[16777216]]></length>
  				<name><![CDATA[MSA_ID]]></name>
  				<nullable><![CDATA[true]]></nullable>
  				<precision json_null="true"/>
  				<scale json_null="true"/>
  				<schema><![CDATA[MART_SAGE_INTACCT]]></schema>
  				<table><![CDATA[INVOICE_ENRICHED]]></table>
  				<type><![CDATA[text]]></type>
  			</e>
  			<e>
  				<byteLength json_null="true"/>
  				<collation json_null="true"/>
  				<database><![CDATA[PRD_EDW]]></database>
  				<length json_null="true"/>
  				<name><![CDATA[INVOICE_NUMBER]]></name>
  				<nullable><![CDATA[false]]></nullable>
  				<precision><![CDATA[38]]></precision>
  				<scale><![CDATA[0]]></scale>
  				<schema><![CDATA[MART_SAGE_INTACCT]]></schema>
  				<table><![CDATA[INVOICE_ENRICHED]]></table>
  				<type><![CDATA[fixed]]></type>
  			</e>

ideally what i would like to do is take the name field from the rowtype and replace all the “e” value names in the data. Any ideas on an easy way to accomplish this?

2

Hi Brent,

There doesn’t seem to be a 1:1 correlation between the two sets, but if you could figure out which row is which name then you can do the renaming with inline Java JSP code, something like:

List<VTDElement> listRows = doc.selectNodes("data/e");
for (VTDElement elRow : listRows)
{
    List<VTDElement> listFields = elRow.selectNodes("e");
    for (VTDElement elField : listFields)
    {
        elField.setName("NEW-NAME-HERE");
    }
}

There is another approach, which is to use the Data Nodes - Rename Uniquely module which would just give all the repeated e nodes unique names with an incremented number. That would at least let you perform assembly logic on the data, if the sending system always sends the same set of nodes.

Let us know if you can’t figure out a needed solution!

Robert

3

i didnt copy the full data. there is a 1:1. There are 18 rowType records to match the 18 data points per data record. so the first rowtype record name would be the name of the first e value in the data record, 2nd is 2nd and so on (ie. MSA_ID = 623, INVOICE_NUMBER = 6478032, etc . i was thinking something like the name value pairs module could work but obviously those need to be paired together already. Rename unique module just appends a -1 to each to make it e-1, e-2, etc., which is a great start and i could say e-1 is always this but i was hoping to find a way to read from the rowtype records and rename the data dynamically. If i ever need to change my query, the data table changes, anything, then i need to remap the columns names. thoughts?

4

Given that there is indeed a 1:1 row correlation, then I would do it all in Java JSP code. Input the two data streams in, read the row names into an array, then in the inner loop fetch the name from the array by index.

5

That is what i assumed. always worth asking to see if a solution already exists

thanks