Script to process xml file to get new layout

cgcamal · 10-05-2011, 04:10 AM

Hi everybody,

I want to change the structure of a xml file.

Maybe someone could help me with this.

I have this infile:

Code:

<?xml version="1.0" encoding="utf-8"?>
<cases>
  <file name="Reference 19762">
    <case>
      <name>CaseX - exp</name>
      <number>No. 3 Div. 870</number>
      <citation>271 Bypl. 44; 122 So. 2d 119; 2005 Bypl. MOPPE 405</citation>
      <date>July 17, 2005</date>
      <judges>Peter, Ely, Mark.</judges>
    </case>
    <case>
      <name>Comp and Indfi</name>
      <number>No. 3 Div. 887</number>
      <citation>271 Bypl. 70; 122 So. 2d 360; 2005 Bypl. MOPPE 421</citation>
      <date>July 17, 2005</date>
      <judges>Mary, Peter, Ely, Perry, Mark.</judges>
    </case>
  </file>
</cases>

and the output should be:

Code:

<Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="3" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="60" ss:DefaultRowHeight="15">
   <Column ss:Width="81.75"/>
   <Column ss:Width="75.75"/>
   <Column ss:Width="67.5"/>
   <Column ss:Width="238.5"/>
   <Column ss:Width="62.25"/>
   <Column ss:Width="141.75"/>
   <Row>
    <Cell><Data ss:Type="String">name</Data></Cell>
    <Cell><Data ss:Type="String">name2</Data></Cell>
    <Cell><Data ss:Type="String">number</Data></Cell>
    <Cell><Data ss:Type="String">citation</Data></Cell>
    <Cell><Data ss:Type="String">date</Data></Cell>
    <Cell><Data ss:Type="String">judges</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Reference 19762</Data></Cell>
    <Cell><Data ss:Type="String">CaseX - exp</Data></Cell>
    <Cell><Data ss:Type="String">No. 3 Div. 870</Data></Cell>
    <Cell><Data ss:Type="String">271 Bypl. 44; 122 So. 2d 119; 2005 Bypl. MOPPE 405</Data></Cell>
    <Cell><Data ss:Type="String">July 17, 2005</Data></Cell>
    <Cell><Data ss:Type="String">Peter, Ely, Mark.</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Reference 19762</Data></Cell>
    <Cell><Data ss:Type="String">Comp and Indfi</Data></Cell>
    <Cell><Data ss:Type="String">No. 3 Div. 887</Data></Cell>
    <Cell><Data ss:Type="String">271 Bypl. 70; 122 So. 2d 360; 2005 Bypl. MOPPE 421</Data></Cell>
    <Cell><Data ss:Type="String">July 17, 2005</Data></Cell>
    <Cell><Data ss:Type="String">Mary, Peter, Ely, Perry, Mark.</Data></Cell>
   </Row>
  </Table>

As you can see, in infile there are 2 blocks of "cases" (it could be more than 2 "cases" blocks. e.g 5, 7, 8 etc.).

Then, in the output the "Row" blocks should be obtained as follow:

Block 1:

Code:

<Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="3" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="60" ss:DefaultRowHeight="15">

Variables here are in red:

ExpandedRowCount = Number of "Cases" blocks + 1 = 2 + 1 = 3
ExpandedColumnCount="6" (is always equal to 6)

Block 2 (the first "Row" block for headers):

Code:

   <Row>
    <Cell><Data ss:Type="String">name</Data></Cell>
    <Cell><Data ss:Type="String">name2</Data></Cell>
    <Cell><Data ss:Type="String">number</Data></Cell>
    <Cell><Data ss:Type="String">citation</Data></Cell>
    <Cell><Data ss:Type="String">date</Data></Cell>
    <Cell><Data ss:Type="String">judges</Data></Cell>
   </Row>

This block is always the same text.

Blocks 3,4,...,N ("Row" blocks that are not for headers):

Code:

   <Row>
    <Cell><Data ss:Type="String">Reference 19762</Data></Cell>
    <Cell><Data ss:Type="String">CaseX - exp</Data></Cell>
    <Cell><Data ss:Type="String">No. 3 Div. 870</Data></Cell>
    <Cell><Data ss:Type="String">271 Bypl. 44; 122 So. 2d 119; 2005 Bypl. MOPPE 405</Data></Cell>
    <Cell><Data ss:Type="String">July 17, 2005</Data></Cell>
    <Cell><Data ss:Type="String">Peter, Ely, Mark.</Data></Cell>
   </Row>

The first value, in red, is get from the line "<file name="Reference 19762">" in infile that only appears once.
The next rows for this block, values in green, are taken in the same order that they appear in "Cases" block

I can do some basic sed or awk replacements individually, but I don't know how to get a script to do all
things needed as described above.

I hope someone could help me.

Many thanks in advance for any help.

Regards.

paulsm4 · 10-05-2011, 09:49 PM

In general, this is the kind of thing XSL was invented to do:

http://www.ibm.com/developerworks/xm..._TACT=105AGX59

I would suggest one of two things:

1. XSLT approach:
a) Install Xalan on your PC (it should be available from your distro's package manager)
b) Work through the tutorial on the above link
c) See if it looks like it might be a good match

2. Non-XSLT apprach:
a) Pick a scripting language. Any language: Perl, Python, Java, etc.
b) Pick the level you want to work at (use XSLT to read/write, use a DOM to read, but write directly, read and write directly)
c) Post back if you have any questions

PS:
Xalan and XSLT can easily be "scripted" from the Linux shell.

'Hope that helps!

cgcamal · 10-06-2011, 04:32 PM

Hi paulsm4,

Many thanks for reply. I didn't know about that tool. I'll need to read how to use that tool because I don't now a language as java, perl, etc.

But many thanks again for your answer and give share that option.

Regards.

Proud · 10-06-2011, 04:44 PM

Seconding that XML to XML transformation is the point of XLS(T), which is also XML itself. It should be a no-brainer if you understand the XML you're using, no programming language required as long as it can handle the specifics.

cgcamal · 10-06-2011, 07:55 PM

Hi Proud,

Thanks for the reference.

Do you now if with XLST is possible do parsing of the xml file, not only change the structure?

Because in other file I have, not only I need to change the structure of the xml, but delete repeted names and all its associated values must be in the same block.

Is possible to do batch process?

Regards.

Proud · 10-07-2011, 05:01 AM

I last touched XLS in uni, but I believe the contents/value of an element can be searched and manipulated as easily as any attribute or tag name.

Your specific manipulation might be classed under a distinct/choose/coalesce operator/instruction. A quick google suggests a common approach might be what is called the Muenchian Method, or for use with XSLT 2.0 perhaps use the <xsl:for-each-group> instruction.

There also seems to be quite a few results at places like stackoverflow.com for more specific problems when using XLS to transform XML into Excel XML stylesheets, which I think you're trying to do.

As for batches of transformations, again as Paulsm4 points out, an implementation such as Xalan can be easily scripted with some shell programming, such as Bash.

cgcamal · 10-07-2011, 09:30 PM

Thanks for answer Proud,

I've been seeing that, some examples. I'll read more to learn about and apply it, it looks
something similar to what I want it could be done with those methods.

Thanks again to both.

Regards

aspire1 · 10-08-2011, 06:06 PM

..and just to point out xsltproc is probably already installed on your system which takes a stylesheet and applies it to your original xml.