XML parse to create SQL script
Posted: Fri Apr 08, 2005 5:43 am
Hi,
I am attempting to parse the below triple-s XML document into a SQL query and have hit a stumbling 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, I am new to XML.
Thanks,
The php code:
The XML doc:
The output right now:
I am trying to achieve:
I will get the proper field type in there, after I am able to get the attribute after the data.
Any suggestions/critiques would be greatly appreciated.
Thanks in advance.
~Az
I am attempting to parse the below triple-s XML document into a SQL query and have hit a stumbling 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, I am new to XML.
Thanks,
The php code:
Code: Select all
<?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']}", ;
switch($variable_type)
{
case "single":
case "quantity":
case "multiple":
case "character":
echo " [$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 " [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:
Code: Select all
<?xml version="e;1.0"e; encoding = "e;Windows-1252"e;?>
<!DOCTYPE sss PUBLIC "e;-//triple-s//DTD Survey Interchange v1.2//EN"e; "e;http://www.triple-s.org/dtd/sss_v12.dtd"e;>
<sss version="e;1.2"e;>
<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="e;V"e;>
<variable ident="e;1"e; type="e;logical"e;>
<name>F.1</name>
<label>Unless Q4=8</label>
<position start="e;1"e;/>
</variable>
<variable ident="e;2"e; type="e;character"e;>
<name>ID.user</name>
<label>respondent ID</label>
<position start="e;2"e; finish="e;51"e;/>
<size>50</size>
</variable>
<variable ident="e;3"e; type="e;character"e;>
<name>Q1</name>
<label>Date of visit</label>
<position start="e;52"e; finish="e;71"e;/>
<size>20</size>
</variable>
<variable ident="e;4"e; type="e;single"e;>
<name>Q2</name>
<label>Frequency of visit</label>
<position start="e;72"e;/>
<values>
<value code="e;1"e;>Daily</value>
<value code="e;2"e;>Twice a week</value>
<value code="e;3"e;>Weekly</value>
<value code="e;4"e;>Monthly</value>
</values>
</variable>
<variable ident="e;5"e; type="e;quantity"e;>
<name>Q3.a</name>
<label>Adults</label>
<position start="e;73"e; finish="e;74"e;/>
<values>
<range from="e;0"e; to="e;99"e;/>
</values>
</variable>
<variable ident="e;6"e; type="e;quantity"e;>
<name>Q3b</name>
<label>Children</label>
<position start="e;75"e; finish="e;76"e;/>
<values>
<range from="e;0"e; to="e;99"e;/>
</values>
</variable>
<variable ident="e;7"e; type="e;multiple"e;>
<name>Q4</name>
<label>Items ordered</label>
<position start="e;77"e; finish="e;84"e;/>
<values>
<value code="e;1"e;>Hamburger</value>
<value code="e;2"e;>French fries</value>
<value code="e;3"e;>Pizza</value>
<value code="e;4"e;>Salad</value>
<value code="e;5"e;>Ice cream</value>
<value code="e;6"e;>Coffee/tea</value>
<value code="e;7"e;>Soft drink</value>
<value code="e;8"e;>Other</value>
</values>
</variable>
<variable ident="e;8"e; type="e;character"e;>
<name>Q4a</name>
<label>Other items ordered</label>
<position start="e;85"e; finish="e;114"e;/>
<filter>F.1</filter>
<size>30</size>
</variable>
<variable ident="e;9"e; type="e;quantity"e;>
<name>Q5</name>
<label>Amount spent</label>
<position start="e;115"e; finish="e;122"e;/>
<values>
<range from="e;-9999.99"e; to="e;99999.99"e;/>
</values>
</variable>
<variable ident="e;10"e; type="e;single"e;>
<name>Q6.a</name>
<label>Speed of service</label>
<position start="e;123"e;/>
<values>
<value code="e;1"e;>Very Good</value>
<value code="e;2"e;>Good</value>
<value code="e;3"e;>OK</value>
<value code="e;4"e;>Poor</value>
<value code="e;5"e;>Very poor </value>
</values>
</variable>
<variable ident="e;11"e; type="e;single"e;>
<name>Q6b</name>
<label>Cleanliness</label>
<position start="e;124"e;/>
<values>
<value code="e;1"e;>Very Good</value>
<value code="e;2"e;>Good</value>
<value code="e;3"e;>OK</value>
<value code="e;4"e;>Poor</value>
<value code="e;5"e;>Very poor </value>
</values>
</variable>
<variable ident="e;12"e; type="e;single"e;>
<name>Q6c</name>
<label>Parking</label>
<position start="e;125"e;/>
<values>
<value code="e;1"e;>Very Good</value>
<value code="e;2"e;>Good</value>
<value code="e;3"e;>OK</value>
<value code="e;4"e;>Poor</value>
<value code="e;5"e;>Very poor </value>
</values>
</variable>
<variable ident="e;13"e; type="e;single"e;>
<name>Q6d</name>
<label>Quality of food</label>
<position start="e;126"e;/>
<values>
<value code="e;1"e;>Very Good</value>
<value code="e;2"e;>Good</value>
<value code="e;3"e;>OK</value>
<value code="e;4"e;>Poor</value>
<value code="e;5"e;>Very poor </value>
</values>
</variable>
<variable ident="e;14"e; type="e;single"e;>
<name>Q6e</name>
<label>Choice of food</label>
<position start="e;127"e;/>
<values>
<value code="e;1"e;>Very Good</value>
<value code="e;2"e;>Good</value>
<value code="e;3"e;>OK</value>
<value code="e;4"e;>Poor</value>
<value code="e;5"e;>Very poor </value>
</values>
</variable>
<variable ident="e;15"e; type="e;quantity"e;>
<name>Q7</name>
<label>Overall Performance</label>
<position start="e;128"e; finish="e;129"e;/>
<values>
<range from="e;0"e; to="e;99"e;/>
</values>
</variable>
<variable ident="e;16"e; type="e;character"e;>
<name>Q8</name>
<label>Comments</label>
<position start="e;130"e; finish="e;280"e;/>
<size>151</size>
</variable>
<variable ident="e;17"e; type="e;single"e;>
<name>Q9</name>
<label>Age</label>
<position start="e;281"e;/>
<values>
<value code="e;1"e;>Under 18</value>
<value code="e;2"e;>18-24</value>
<value code="e;3"e;>25-34</value>
<value code="e;4"e;>35-44</value>
<value code="e;5"e;>45-54</value>
<value code="e;6"e;>55+</value>
</values>
</variable>
<variable ident="e;18"e; type="e;single"e;>
<name>Q10</name>
<label>Gender</label>
<position start="e;282"e;/>
<values>
<value code="e;1"e;>Male</value>
<value code="e;2"e;>Female</value>
</values>
</variable>
<variable ident="e;19"e; type="e;character"e;>
<name>Q11</name>
<label>Zipcode/postcode</label>
<position start="e;283"e; finish="e;292"e;/>
<size>10</size>
</variable>
<variable ident="e;20"e; type="e;quantity"e;>
<name>v1</name>
<label>Scoring in percentages</label>
<position start="e;293"e; finish="e;300"e;/>
<values>
<range from="e;-9999999"e; to="e;99999999"e;/>
</values>
</variable>
</record>
</survey>
</sss>The output right now:
Code: Select all
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]Code: Select all
CREATE TABLE їdbo].snCrocodile (
їcase_number] їint] NOT NULL,
їrespondent ID]їcharacter]
їDate of visit]їcharacter]
їFrequency of visit]їsingle]
їAdults]їquantity]
їChildren]їquantity]
їItems ordered]їmultiple]
їOther items ordered]їcharacter]
їAmount spent]
їSpeed of service]їsingle]
їCleanliness]їsingle]
їParking]їsingle]
їQuality of food]їsingle]
їChoice of food]їsingle]
їOverall Performance]їquantity]
їComments]їcharacter]
їAge]їsingle]
їGender]їsingle]
їZipcode/postcode] їcharacter]
їScoring in percentages]їquantity]
) ON їPRIMARY]Any suggestions/critiques would be greatly appreciated.
Thanks in advance.
~Az