XML Parse to create SQL script

[Azazeal]

Hi Folks,
I am attempting to parse an XML document in order to create either MSSQL or MySQL scripts to create a table and fields.

The XML:

<?xml version="1.0" encoding = "Windows-1252"?>
<!DOCTYPE sss PUBLIC "-//triple-s//DTD Survey Interchange v1.2//EN" "http://www.triple-s.org/dtd/sss_v12.dtd">
<sss version="1.2">
	<date>03/30/2005</date>
	<time>22:37</time>
	<origin>SNAP v.8.00.170 for Windows</origin>
	<user>99999S/999</user>
	<survey>
		<name>snCrocodile</name>
		<title>Customer Satisfaction Survey</title>
		<record ident="V">

		<variable ident="2" type="character">
			<name>ID.user</name>
			<label>Name of remote interviewer or respondent ID</label>
			<position start="2" finish="51"/>
			<size>50</size>
		</variable>

		<variable ident="3" type="character">
			<name>A1</name>
			<label>Date of visit</label>
			<position start="52" finish="71"/>
			<size>20</size>
		</variable>

		<variable ident="4" type="single">
			<name>A2</name>
			<label>Frequency of visit</label>
			<position start="72"/>
			<values>
				<value code="1">Daily</value>
				<value code="2">Twice a week</value>
				<value code="3">Weekly</value>
				<value code="4">Monthly</value>
			</values>
		</variable>

		<variable ident="5" type="quantity">
			<name>A3a</name>
			<label>Adults</label>
			<position start="73" finish="74"/>
			<values>
				<range from="0" to="99"/>
			</values>
		</variable>

		<variable ident="7" type="multiple">
			<name>B1</name>
			<label>Items ordered</label>
			<position start="77" finish="84"/>
			<values>
				<value code="1">Hamburger</value>
				<value code="2">French fries</value>
				<value code="3">Pizza</value>
				<value code="4">Salad</value>
				<value code="5">Ice cream</value>
				<value code="6">Coffee/tea</value>
				<value code="7">Soft drink</value>
				<value code="8">Other</value>
			</values>
		</variable>

		<variable ident="9" type="quantity">
			<name>B2</name>
			<label>Amount spent</label>
			<position start="115" finish="122"/>
			<values>
				<range from="-9999.99" to="99999.99"/>
			</values>
		</variable>

	</record>
</survey>
</sss>

The PHP code, so far:

<?php
$file	=	"croc.sss";

$currentTag	=	"";
$type		=	"";			//use this later for finding type of field to create
$code		=	"";			//use this later for multiple response 
$range_to	=	"";			//use this later for finding field length on quantity data
$size		=	"";			//use this later for field length on character data

// Set array for for holding variable information
$variable_data	=	array();

//Finds elements in XML document and their attributes
//Need to use this function to get values of attributes 
//and pass them into the global variables. Need to use substr() to strip out attribute name.

function start_element($parser, $name, $attrs)
{
	global	$currentTag;
	global	$type;
	global	$code;
	global	$range_to;
	global	$size;

$currentTag	=	$name;
switch($name)
{
	case	"VARIABLE":
		$type	=	substr("$name{$attrs['TYPE']}",8);
	break;
}
}

//Ends element tags, clears data
function end_element($parser, $name)
{
	global $currentTag;
	global $type;
	global $code;
	global $variable_data;
	$currentTag	=	"";
	$type		=	"";
	$code		=	"";
	$variable_data	=	"";
}

//Display and assign data
function character_data($parser, $data)
{
	global $currentTag;
	global $variable_data;
	global $value_data;
	global $type;
	global $code;

switch($currentTag)
{
	case "TITLE":
		$variable_data['title']	=	$data;
		$title	=	$data;
	break;

	case "NAME":
		$variable_data['name']	=	$data;
		$var_label	=	$data;
	break;

	case "LABEL":
		$variable_data['label']	=	$data;
		$var_label	=	$data;
	break;

	case "VALUE":
		$variable_data['code']	=	$data;
	echo $data;
	break;
}

}
$parser	=	xml_parser_create();
xml_set_element_handler($parser, 'start_element', 'end_element');
xml_set_character_data_handler($parser, 'character_data');

$fp	=	fopen($file, 'r') or die("Cant open file");

while($data	=	fread($fp, 4096))
{
	xml_parse($parser, $data, feof($fp)) or die(sprintf('XML error: %s at line %d', xml_error_string(xml_get_error_code($parser)), xml_get_current_line_number($parser)));	
}



xml_parser_free($parser);
?>

This is my first time in working with the XML parser and I having a bit of a hard time.

The hard part right now is getting the <values>
<value code="1">
<value code="2">

type elements to count the number of value elements.

The end goal would give me a script:
Database: snCrocodile
Table: Customer Satisfaction Survey

Fields:
<type> would be the field type
<label> would create the field names.

Depending on the type, the field length would get created by <size>,
count of <value code...> or <range to...>

Does anyone have any suggestions with this and any critiques with already written code?

Thanks,
~Az

[Azazeal]

Hi,
I have made some major updates to the code and have hit a stumping block.
Any help would be greatly appreciated.

I am able to get the data and attributes I need, for now.
However, when outputting the results, the attribute always comes before the data.

Is there a way to have to the attribute data come after the element data.

Apologies if I am using the wrong terms, but again, I am new to XML.

Thanks,

The php code:

<?php

//parser .2
$variable_data	=	array();
$current_tag		=	"";
$variable_label		=	"";
$variable_type		=	"";

function start_element($parser, $element_name, $element_attrs)
	{
		global $current_tag, $variable_label, $variable_type;

	$current_tag	=	$element_name;

	switch($element_name)
		{
			case "VARIABLE":
				$variable_type	=	substr("$element_name{$element_attrs['TYPE']}", 8);
				switch($variable_type)
					{
						case "single":
						case "quantity":
						case "multiple":
						case "character":
						echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[$variable_type] ";
						break;
					}
			break;
		}

}

function end_element($parser, $element_name)
	{
		global $current_tag, $variable_type;

	$current_tag	=	"";
	$variable_type	=	"";
}

function character_data($parser, $data)
	{

	global $current_tag, $variable_label;

	switch($current_tag)
	{
		case "NAME":
		$variable_data['name']	=	$data;
		$survey_title	=	$data;
			if(substr($survey_title, 0 ,2)=="sn")
				{
					echo "CREATE TABLE [dbo].$survey_title (";
					echo "<BR>";
					echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[case_number] [int] NOT NULL,";
					echo "<BR>";
				}
		break;

		case "LABEL":
		$variable_data['label']	=	$data;
		$variable_label	=	$data;
			if(substr($variable_label, 0, 6)!=="Unless")
				{
					echo "[$variable_label] <BR>";
				}
		break;
	}
}

$parser	=	xml_parser_create();
xml_set_element_handler($parser, 'start_element', 'end_element');
xml_set_character_data_handler($parser, 'character_data');

$fp	=	fopen('croc2.sss', 'r') or die('Cannot open file');

while($data	=	fread($fp, 4096))
	{

	xml_parse($parser, $data, feof($fp)) or die(sprintf('XML error: %s at line %d',
	xml_error_string(xml_get_error_code($parser)),
	xml_get_current_line_number($parser)));

}
xml_parser_free($parser);

echo ") ON [PRIMARY]";
?>

The XML doc:

<?xml version="1.0" encoding = "Windows-1252"?>
<!DOCTYPE sss PUBLIC "-//triple-s//DTD Survey Interchange v1.2//EN" "http://www.triple-s.org/dtd/sss_v12.dtd">
<sss version="1.2">
	<date>04/03/2005</date>
	<time>05:17</time>
	<origin>SNAP v.8.00.200 for Windows</origin>
	<user>99999V/001</user>
	<survey>
		<name>snCrocodile</name>
		<title>Customer Satisfaction Survey</title>
		<record ident="V">
			<variable ident="1" type="logical">
				<name>F.1</name>
				<label>Unless Q4=8</label>
				<position start="1"/>
			</variable>
			<variable ident="2" type="character">
				<name>ID.user</name>
				<label>respondent ID</label>
				<position start="2" finish="51"/>
				<size>50</size>
			</variable>
			<variable ident="3" type="character">
				<name>Q1</name>
				<label>Date of visit</label>
				<position start="52" finish="71"/>
				<size>20</size>
			</variable>
			<variable ident="4" type="single">
				<name>Q2</name>
				<label>Frequency of visit</label>
				<position start="72"/>
				<values>
					<value code="1">Daily</value>
					<value code="2">Twice a week</value>
					<value code="3">Weekly</value>
					<value code="4">Monthly</value>
				</values>
			</variable>
			<variable ident="5" type="quantity">
				<name>Q3.a</name>
				<label>Adults</label>
				<position start="73" finish="74"/>
				<values>
					<range from="0" to="99"/>
				</values>
			</variable>
			<variable ident="6" type="quantity">
				<name>Q3b</name>
				<label>Children</label>
				<position start="75" finish="76"/>
				<values>
					<range from="0" to="99"/>
				</values>
			</variable>
			<variable ident="7" type="multiple">
				<name>Q4</name>
				<label>Items ordered</label>
				<position start="77" finish="84"/>
				<values>
					<value code="1">Hamburger</value>
					<value code="2">French fries</value>
					<value code="3">Pizza</value>
					<value code="4">Salad</value>
					<value code="5">Ice cream</value>
					<value code="6">Coffee/tea</value>
					<value code="7">Soft drink</value>
					<value code="8">Other</value>
				</values>
			</variable>
			<variable ident="8" type="character">
				<name>Q4a</name>
				<label>Other items ordered</label>
				<position start="85" finish="114"/>
				<filter>F.1</filter>
				<size>30</size>
			</variable>
			<variable ident="9" type="quantity">
				<name>Q5</name>
				<label>Amount spent</label>
				<position start="115" finish="122"/>
				<values>
					<range from="-9999.99" to="99999.99"/>
				</values>
			</variable>
			<variable ident="10" type="single">
				<name>Q6.a</name>
				<label>Speed of service</label>
				<position start="123"/>
				<values>
					<value code="1">Very Good</value>
					<value code="2">Good</value>
					<value code="3">OK</value>
					<value code="4">Poor</value>
					<value code="5">Very poor </value>
				</values>
			</variable>
			<variable ident="11" type="single">
				<name>Q6b</name>
				<label>Cleanliness</label>
				<position start="124"/>
				<values>
					<value code="1">Very Good</value>
					<value code="2">Good</value>
					<value code="3">OK</value>
					<value code="4">Poor</value>
					<value code="5">Very poor </value>
				</values>
			</variable>
			<variable ident="12" type="single">
				<name>Q6c</name>
				<label>Parking</label>
				<position start="125"/>
				<values>
					<value code="1">Very Good</value>
					<value code="2">Good</value>
					<value code="3">OK</value>
					<value code="4">Poor</value>
					<value code="5">Very poor </value>
				</values>
			</variable>
			<variable ident="13" type="single">
				<name>Q6d</name>
				<label>Quality of food</label>
				<position start="126"/>
				<values>
					<value code="1">Very Good</value>
					<value code="2">Good</value>
					<value code="3">OK</value>
					<value code="4">Poor</value>
					<value code="5">Very poor </value>
				</values>
			</variable>
			<variable ident="14" type="single">
				<name>Q6e</name>
				<label>Choice of food</label>
				<position start="127"/>
				<values>
					<value code="1">Very Good</value>
					<value code="2">Good</value>
					<value code="3">OK</value>
					<value code="4">Poor</value>
					<value code="5">Very poor </value>
				</values>
			</variable>
			<variable ident="15" type="quantity">
				<name>Q7</name>
				<label>Overall Performance</label>
				<position start="128" finish="129"/>
				<values>
					<range from="0" to="99"/>
				</values>
			</variable>
			<variable ident="16" type="character">
				<name>Q8</name>
				<label>Comments</label>
				<position start="130" finish="280"/>
				<size>151</size>
			</variable>
			<variable ident="17" type="single">
				<name>Q9</name>
				<label>Age</label>
				<position start="281"/>
				<values>
					<value code="1">Under 18</value>
					<value code="2">18-24</value>
					<value code="3">25-34</value>
					<value code="4">35-44</value>
					<value code="5">45-54</value>
					<value code="6">55+</value>
				</values>
			</variable>
			<variable ident="18" type="single">
				<name>Q10</name>
				<label>Gender</label>
				<position start="282"/>
				<values>
					<value code="1">Male</value>
					<value code="2">Female</value>
				</values>
			</variable>
			<variable ident="19" type="character">
				<name>Q11</name>
				<label>Zipcode/postcode</label>
				<position start="283" finish="292"/>
				<size>10</size>
			</variable>
			<variable ident="20" type="quantity">
				<name>v1</name>
				<label>Scoring in percentages</label>
				<position start="293" finish="300"/>
				<values>
					<range from="-9999999" to="99999999"/>
				</values>
			</variable>
		</record>
	</survey>
</sss>

The output right now:

CREATE TABLE [dbo].snCrocodile (
     [case_number] [int] NOT NULL,
     [character] [respondent ID]
     [character] [Date of visit]
     [single] [Frequency of visit]
     [quantity] [Adults]
     [quantity] [Children]
     [multiple] [Items ordered]
     [character] [Other items ordered]
     [quantity] [Amount spent]
     [single] [Speed of service]
     [single] [Cleanliness]
     [single] [Parking]
     [single] [Quality of food]
     [single] [Choice of food]
     [quantity] [Overall Performance]
     [character] [Comments]
     [single] [Age]
     [single] [Gender]
     [character] [Zipcode/postcode]
     [quantity] [Scoring in percentages]
) ON [PRIMARY]

Thanks in advance.
~Az