Questions

XML: How to convert an XML file into a table?

Tags:
+
0 Votes
Locked

XML: How to convert an XML file into a table?

Locrian_Lyric
<Snapshot device="Device name" model="Model" version="1">

<node name="FOO">

<table name="Table1">

<row>
<column name="COL1">'+SYS'</column>
<column name="COL2">'1234'</column>
<column name="COL3">'+'</column>
<column name="COL4">0</column>
<column name="COL5">5</column>
<column name="COL6">50</column>
<column name="COL7">1</column>
<column name="COL8">1</column>
<column name="COL9">0</column>
<column name="COL10">0</column>
<column name="COL11">1</column>
<column name="COL12">0</column>
<column name="COL13">0</column>
<column name="COL14">1</column>
<column name="COL15">1</column>
<column name="COL16">0</column>
<column name="COL17">0</column>
<column name="COL18">0</column>
<column name="COL19">0</column>
<column name="COL20">1</column>
<column name="COL21">15</column>
<column name="COL22">1</column>
<column name="COL23">388</column>
<column name="COL24">2</column>
</row>
</table>
</node>
</Snapshot>

I have about 130K rows that I need to convert into a table.

I've tried Access, Excel and a few other applications to try to do a quick parse. XML is not my strong suit

I don't know how to turn this into a table that would be viewable. Any suggestions?
  • +
    0 Votes
    neilb@uk

    But I do know that Powershell will deal with XML.

    There is a command Import-CLIXML that will import a CLIXML file into PS objects that you can then export as csv or format into a table. I'm still getting to grips with PS and don't have much to do with XML anyway so I haven't used the command yet.

    Import-CLIXML file.xml | Format-table -auto

    or something like would do to start.

    I'll keep looking.

    Neil :)

    +
    0 Votes
    Kim SJ

    I'm not an expert, but here's a hint. Viewing in a browser using stylesheets is one way to go... (For some reason this works with IE, but not Firefox, so I've not got it quite right! Or maybe Firefox doesn't do this stuff yet... Anyone more of an expert than me?)

    Try adding

    <?xml-stylesheet href="style.xsl" type="text/xsl"?>

    (note that the "' rel="nofollow" ' seems to ve added to the line by the board software, and is not required.)

    to the start of the file. You can then create the style.xsl file to format it in html. For example...

    <?xml version='1.0'?>
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
    <xsl:template match="/">
    <html>
    <body>
    <table border="1">
    <tr>
    <th>Col 1</th>
    <th>Col 2</th>
    <th>Col 3</th>
    <th>Col 4</th>
    <th>Col 5</th>
    <th>Col 6</th>
    <th>Col 7</th>
    <th>Col 8</th>
    <th>Col 9</th>
    <th>Col 10</th>
    <th>Col 11</th>
    <th>Col 12</th>
    <th>Col 13</th>
    <th>Col 14</th>
    <th>Col 15</th>
    <th>Col 16</th>
    <th>Col 17</th>
    <th>Col 18</th>
    <th>Col 19</th>
    <th>Col 20</th>
    <th>Col 21</th>
    <th>Col 22</th>
    <th>Col 23</th>
    <th>Col 24</th>
    </tr>
    <xsl:for-each select="Snapshot/node/table/row">
    <tr>
    <xsl:for-each select="column">
    <td><xsl:value-of /></td>
    </xsl:for-each>
    </tr>
    </xsl:for-each>
    </table>
    </body>
    </html>
    </xsl:template>
    </xsl:stylesheet>

    Does this do what you want?

    K.

    +
    0 Votes
    compu-mechanic

    A simple style sheet would suffice.

    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="html" doctype-system="http://www.w3.org/TR/html4/strict.dtd" doctype-public="-//W3C//DTD HTML 4.01//EN" indent="yes"/>
    <xsl:template match="/">
    <html>
    <body>
    <!-- Assuming the content of each row is text and not an attribute of a tag-->
    <table>
    <xsl:for-each select="/trow/contents">
    <tr><td><xsl:value-of select="."/></td></tr>
    </xsl:for-each>
    </table>
    </body>
    </html>

    </xsl:template>

    </xsl:stylesheet>

    This should work if you change the path. You can also send it out as text and save it to file. It's not hard, look it up.

  • +
    0 Votes
    neilb@uk

    But I do know that Powershell will deal with XML.

    There is a command Import-CLIXML that will import a CLIXML file into PS objects that you can then export as csv or format into a table. I'm still getting to grips with PS and don't have much to do with XML anyway so I haven't used the command yet.

    Import-CLIXML file.xml | Format-table -auto

    or something like would do to start.

    I'll keep looking.

    Neil :)

    +
    0 Votes
    Kim SJ

    I'm not an expert, but here's a hint. Viewing in a browser using stylesheets is one way to go... (For some reason this works with IE, but not Firefox, so I've not got it quite right! Or maybe Firefox doesn't do this stuff yet... Anyone more of an expert than me?)

    Try adding

    <?xml-stylesheet href="style.xsl" type="text/xsl"?>

    (note that the "' rel="nofollow" ' seems to ve added to the line by the board software, and is not required.)

    to the start of the file. You can then create the style.xsl file to format it in html. For example...

    <?xml version='1.0'?>
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
    <xsl:template match="/">
    <html>
    <body>
    <table border="1">
    <tr>
    <th>Col 1</th>
    <th>Col 2</th>
    <th>Col 3</th>
    <th>Col 4</th>
    <th>Col 5</th>
    <th>Col 6</th>
    <th>Col 7</th>
    <th>Col 8</th>
    <th>Col 9</th>
    <th>Col 10</th>
    <th>Col 11</th>
    <th>Col 12</th>
    <th>Col 13</th>
    <th>Col 14</th>
    <th>Col 15</th>
    <th>Col 16</th>
    <th>Col 17</th>
    <th>Col 18</th>
    <th>Col 19</th>
    <th>Col 20</th>
    <th>Col 21</th>
    <th>Col 22</th>
    <th>Col 23</th>
    <th>Col 24</th>
    </tr>
    <xsl:for-each select="Snapshot/node/table/row">
    <tr>
    <xsl:for-each select="column">
    <td><xsl:value-of /></td>
    </xsl:for-each>
    </tr>
    </xsl:for-each>
    </table>
    </body>
    </html>
    </xsl:template>
    </xsl:stylesheet>

    Does this do what you want?

    K.

    +
    0 Votes
    compu-mechanic

    A simple style sheet would suffice.

    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="html" doctype-system="http://www.w3.org/TR/html4/strict.dtd" doctype-public="-//W3C//DTD HTML 4.01//EN" indent="yes"/>
    <xsl:template match="/">
    <html>
    <body>
    <!-- Assuming the content of each row is text and not an attribute of a tag-->
    <table>
    <xsl:for-each select="/trow/contents">
    <tr><td><xsl:value-of select="."/></td></tr>
    </xsl:for-each>
    </table>
    </body>
    </html>

    </xsl:template>

    </xsl:stylesheet>

    This should work if you change the path. You can also send it out as text and save it to file. It's not hard, look it up.