Re: Importing XML for Newbies

DaveK wrote:
After a five minute web search and SQL help search I don't see how I can avoid using SQL to build some sort of datbase just so I can address backup, security, etc. It looks like XPath and XQuery can do some searching, but the other items that I need to cover are not really addressed. This is an example of one event log item I want to store.

<CreateDate>7/31/2007</CreateDate>
<CreateTime>10:19:25</CreateTime>
<Logger>xxxxd7yrrt11</Logger>
<Events>
<Event>
<EventID>8001</EventID>
<Description>Login</Description>
<Category>Audit</Category>
<Source>LAN Client</Source>
<SubSource>xxxx_8</SubSource>
<UserName>test.name</UserName>
<UserID>347</UserID>
<Computer>xxx7YRRT11</Computer>
<Date>07/31/2007</Date>
<Time>10:19:49</Time>
<ObjectType>User</ObjectType>
<Details></Details>
</Event>

I have to admit I am not a convert to the XML world, but as I said, I am a newbie to it as well. In this case it just seems like a fancy way to eliminate delimited importing. The format is not likely to change.

AFAIK all database systems now offer some kind of "Import XML" plugin.
If yours doesn't, you'll need to turn the XML into CSV or whatever your
system consumes. The easiest way to do this is to write an XSLT script,
and I think there are several quoted or linked in Dave Pawson's XSL FAQ at http://www.dpawson.co.uk/xsl/

XML is just a fancy way of identifying information: you can see from the above example that it is much clearer in naming items and positioning them in the hierarchy than (for example) CSV. If the format is stable, then a little routine to run XSLT over the data and spit out CSV should do you just fine.

The following appears to work for the sample above (with the addition of the missing </Events> end-tag and the enclosing root element <data>...</data>):

<xsl:style*** xmlns:xsl="http://www.w3.org/1999/XSL/Transform";
version="1.0">

<xsl:output method="text"/>
<xsl:strip-space elements="*"/>

<xsl:template match="/">
<xsl:apply-templates select="data/CreateDate"/>
<xsl:apply-templates select="data/CreateTime"/>
<xsl:apply-templates select="data/Logger"/>
<xsl:apply-templates select="data/Events/Event/*"/>
</xsl:template>

<!-- fields that start a record -->

<xsl:template match="CreateDate|EventID">
<xsl:text>"</xsl:text>
<xsl:value-of select="."/>
</xsl:template>

<!-- fields that occur in mid-record -->

<xsl:template match="*">
<xsl:text>,"</xsl:text>
<xsl:value-of select="."/>
<xsl:text>"</xsl:text>
</xsl:template>

<!-- fields that end a record -->

<xsl:template match="Logger|Details">
<xsl:text>,"</xsl:text>
<xsl:value-of select="."/>
<xsl:text>"&#xa;</xsl:text>
</xsl:template>

</xsl:style***>

$ java -jar /usr/local/saxon/b8.5/saxon8.jar -o test.csv test.xml test.xsl
$ cat test.csv
"7/31/2007,"10:19:25","xxxxd7yrrt11"
"8001,"Login","Audit","LAN Client","xxxx_8","test.name","347","xxx7YRRT11","07/31/2007","10:19:49","User",""
$

This makes the assumption that your import routine can do something different with record #1...

///Peter
--
XML FAQ: http://xml.silmaril.ie/
.

Loading