Xml Sql

[shoes]

Any ideas how I would build a script to automatically pick up an xml document and populate an SQL database with the contents?

 

[tek-monkey]

Nope!

I ****** hate XML, but only cos I've never actually sat down to look at it.

 

[shoes]

Lol fair enough.

All I have is XML reports generated by a lighting control system, the data is structured enough to just import straight into SQL. What is really irritating is that the software generating the reports runs on SQL but due to patents still pending we cannot have access to the tables, so we have to output as xml and then report on it, my idea being to import into a new sql database and then use crystal (or similar) to number crunch and display some swanky graphical output.

Thats the plan anyway.

 

[Lucy]

Whilst we're on about SQL, back in the day when I wrote it every minute of every day for a living, I was able to perform an update statement like this:

UPDATE table a
set (column1,
column2,
column3) =
(select b.column1,
b.column2,
b.column3
from table2 b
where a.id=b.id)

And now it won't work. I'm not sure if it's simply the fact that Sybase isn't as good as Oracle, or I have a massive syntax error. Any ideas?

 

[shoes]

I am a novice at SQL but hat syntax looks pretty sound to me. Do you not have to qualify each statement with a ; in order to execute it?

I presume that you want to copy data from table a to table b when the primary key is matched in both records?

 

[Lucy]

Yea, and that's all one statement, so doesn't need qualifying. I've just spoken to my Solutions Manager, and he's confused by it too, so is off to speak to Sybase.

 

[tek-monkey]

I have no idea on sybase, but MSSQL, Oracle and Access all have their own flavour of SQL.

 

[shoes]

can i ask why you stacked a short statement onto so many lines or was that just for readability here?

My first guess would be extra spaces at the end of the lines which you cannot see. Failing that you'll need someone with some actual experience of SQL lol

 

[db]

can i ask why you stacked a short statement onto so many lines or was that just for readability here?

i tend to lay code out like that as well.. just makes it easy to read and troubleshoot.. though, as you say, if a weird ansi character gets in there somehow, which just looks like a space or line break, it can fubar the whole statement!

 

[Lucy]

exactly that, readability and troubleshooting ease. How I was taught and it's stayed with me I guess. Plus, as I'm trying to train some of my team to write it now and then, so I'm practising what I preach.

 

[shoes]

For anyone who cares, my mate clarkey solved this for me as follows:

<?php
require_once('db_connect.php');

$doc = new DOMDocument();
$xsl = new XSLTProcessor();

$doc->load('2.xslt');
$xsl->importStyleSheet($doc);

$filename = date('omd') . "LampPercentReport.xml";
echo $filename;
$doc->load($filename);
$output = $xsl->transformToXML($doc);
file_put_contents('output.php', $output);

include('output.php');

if(mysql_errno() == 0) {
	echo 'Database insert succesful';
} else {
	echo mysql_errno();
}
?>

And 2.xlst referred to is this:

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="text" />
  <xsl:template match="/">
  <xsl:text>&lt;?php</xsl:text>
  	  <xsl:variable name="Date"><xsl:value-of select="Average_Dim_Levels/Date"/></xsl:variable>
    <xsl:for-each select="Average_Dim_Levels/Floor">
		<xsl:variable name="Floor"><xsl:value-of select="Floor"/></xsl:variable>
		<xsl:variable name="FloorID"><xsl:value-of select="FloorID"/></xsl:variable>
			<xsl:for-each select="Channel">
$sql = 'INSERT INTO channel(datestamp, floor, floorid, output, location, rating, onmins, avdimlevel) 
VALUES ("<xsl:value-of select="$Date"/>", "<xsl:value-of select="$Floor"/>", <xsl:value-of select="$FloorID"/>, "<xsl:value-of select="Output"/>", "<xsl:value-of select="Location"/>", <xsl:value-of select="Rating"/>, <xsl:value-of select="OnMins"/>, <xsl:value-of select="AvgDimLevel"/>)'; <xsl:text> 
</xsl:text>
$result = mysql_query($sql); <xsl:text>
</xsl:text>
 mysql_error();<xsl:text>
</xsl:text>
			</xsl:for-each>
	</xsl:for-each>
	<xsl:text>?&gt;</xsl:text>
  </xsl:template>
</xsl:stylesheet>

QED... lol