To get Tilde separated Tags and Values in a XML file

prashanth7582 · Jul 29, 2010#12010-07-29T15:21+00:00

Dear All,

I would like to get Tilde separated list of Tags and values in a valid xml file.

Input :
<Person>
<Name>Prashanth</Name>
<Place>Bangalore</Place>
</Person>

OutPut :
Tags : Name~Place
Values : Prashanth~Bangalore

I tried many ways but in vain..I can do above in a excel file but would love to have it in UltraEdit.

Kindly advise.

With Regards
Prashanth

Mofi · Jul 30, 2010#22010-07-30T05:24+00:00

I have my doubts that your XML file really looks like your posted example and therefore the following UltraEdit regular expression Replace All command really works for your XML file having DOS line terminators. However, it works on your example and converts it to the output you want.

Find What: %[ ^t]++<*>[ ^t]++^p[ ^t]++<^(*^)>^(*^)</*>[ ^t]++^p[ ^t]++<^(*^)>^(*^)</*>[ ^t]++^p[ ^t]++</*>[ ^t]++$
Replace With: ^1~^3^p^2~^4

If you want to understand that not really difficult UE regular expression search and replace strings see in help of UltraEdit on page Regular Expressions (Legacy) the first table.

prashanth7582 · Jul 30, 2010#32010-07-30T05:42+00:00

Mofi,

Thanks for your reply. However the below isn't dynamic. If I have 3 elements in my xml file, I need to add [ ^t]++<^(*^)>^(*^)</*>[ ^t]++ for every new element.

Is there any method which is dynamic enough?

With Regards
Prashanth

Mofi · Jul 30, 2010#42010-07-30T15:18+00:00

Today morning I thought about a script collecting the data line by line in two string variables for every section running in a loop for every section. But now in the late afternoon I had an idea how to do this reformatting job completely unusal and 15 minutes later I had a working macro. The macro property Continue if search string not found must be checked for the following UltraEdit macro.

InsertMode
ColumnModeOff
HexOff
UltraEditReOn
Bottom
IfColNumGt 1
InsertLine
IfColNumGt 1
DeleteToStartofLine
EndIf
EndIf
Top
TrimTrailingSpaces
Find RegExp "%[ ^t]+"
Replace All ""
Find RegExp "%<[~/>^p]+>^p"
Replace All ""
SelectAll
Clipboard 9
Copy
EndSelect
Top
Find RegExp "%<*>*</^(*^)>^p"
Replace All "^1~"
Find RegExp "~</*>$"
Replace All ""
ColumnModeOn
ColumnInsert "!"
Top
ColumnInsertNum 1 1 LeadingZero
ColumnModeOff
Bottom
DeleteToStartofLine
SelectAll
Clipboard 8
Copy
Clipboard 9
Paste
ClearClipboard
Clipboard 8
Top
Find RegExp "%<*>^(*^)</*>^p"
Replace All "^1~"
Find RegExp "~</*>$"
Replace All ""
ColumnModeOn
ColumnInsert "#"
Top
ColumnInsertNum 1 1 LeadingZero
ColumnModeOff
Bottom
DeleteToStartofLine
Paste
ClearClipboard
Clipboard 0
SortAsc 1 -1 0 0 0 0 0 0
Find RegExp "%[0-9]+[!#]"
Replace All ""

This macro converts something like

<Person>
<Name>Prashanth</Name>
<Place>Bangalore</Place>
</Person>
<Dummy>
<Tag1>Value of Tag 1</Tag1>
<Tag2>Value of Tag 2</Tag2>
<Tag3>Value of Tag 3</Tag3>
</Dummy>

into

Name~Place
Prashanth~Bangalore
Tag1~Tag2~Tag3
Value of Tag 1~Value of Tag 2~Value of Tag 3

jorrasdk · Aug 03, 2010#52010-08-03T06:50+00:00

I just thought I would have a go at a quick and dirty script

The script below uses ECMAScript for XML (E4X) to change the xml into a XML object and the traverses the subnodes until simple nodes are found and then create a tilde separated list for simple nodes on the same level. It changes

Code: Select all

<FirstSetOfTags>
  <Person>
    <Name>Prashanth</Name>
    <Place>Bangalore</Place>
    <Country>India</Country>
  </Person>
  <Person>
    <Name>Meena</Name>
    <Place>Bangalore</Place>
    <Country>India</Country>
  </Person>
</FirstSetOfTags>
<Dummy>
  <Tag1>Value of Tag 1</Tag1>
  <Tag2>Value of Tag 2</Tag2>
  <Tag3>Value of Tag 3</Tag3>
</Dummy>

Into

Code: Select all

Name~Place~Country
Prashanth~Bangalore~India
Meena~Bangalore~India
Tag1~Tag2~Tag3
Value of Tag 1~Value of Tag 2~Value of Tag 3

Here is the script. Some comments are added. Not much error checking are added.

Code: Select all

// This script uses ECMAScript for XML (E4X) which is supported by the
// javascript engine in UltraEdit.
// Continue reading at: https://developer.mozilla.org/en/E4X

UltraEdit.activeDocument.selectAll(); /* Select whole xml document */
var sel = UltraEdit.activeDocument.selection; /* get selection into variable */
var oldHeader = ""; /* Only write header when it changes */
var outputBuffer = ""; /* Collect output so just writing to screen once */
var NEWLINE = "\r\n"; /* DOS newline to be used */
var SEPARATOR = "~"; /* tilde to be used as separator */
processXml(sel);
if(outputBuffer!="") UltraEdit.activeDocument.write(outputBuffer);

function processXml(xmlStr) {
  var xmlNode;
  try {
    xmlNode = new XML ( xmlStr ); /* try converting into XML object */
  }
  catch (err) { /* failed - most likely missing root tag */
    try {
      xmlNode = new XML ("<root>"+xmlStr+"</root>"); /* add a "dummy" root */
    }
    catch (err2) { /* still errors - write first error to output Window */
      UltraEdit.outputWindow.write("XML error: "+err.toString());
      return; /* quit */
    }
  }
  traverseSubnodes(xmlNode,1); /* run through XML tree */
}

function traverseSubnodes(xmlNode,level) {
  var subNodes = xmlNode.*; /* Get subnodes */

  var header = ""; /* collect headers as tag names */
  var values = ""; /* collect values of tags */
  for (i in subNodes) { /* run through subnodes */
    if(subNodes[i].nodeKind()=="element") {
      if(subNodes[i].hasComplexContent()) { /* complex - go down one level */
        traverseSubnodes(subNodes[i], level + 1);
      }
      else {
        header = header + (header==""?"":SEPARATOR) + subNodes[i].localName(); /* tag name */
        values = values + (values==""?"":SEPARATOR) + subNodes[i].toString();  /* tag value */
      }
    }
  }
  if(header!="") { /* only write header when it changes */
    if(header!=oldHeader) outputBuffer += header + NEWLINE;
    oldHeader=header;
  }
  if(values!="") outputBuffer += values + NEWLINE; /* output values */
}

Have fun changing this to fit your purpose

prashanth7582 · Aug 04, 2010#62010-08-04T10:02+00:00

Hi Mofi/Jorrasdk,

Thanks for your cool ideas. Both are working fine.

With Regards
Prashanth