XML parse to create SQL script

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Az
Forum Newbie
Posts: 4
Joined: Fri Apr 08, 2005 5:35 am

XML parse to create SQL script

Post by Az »

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:

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 "&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:

Code: Select all

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

The output right now:

Code: Select all

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

Code: Select all

CREATE TABLE &#1111;dbo].snCrocodile (
         &#1111;case_number] &#1111;int] NOT NULL,
         &#1111;respondent ID]&#1111;character] 
         &#1111;Date of visit]&#1111;character] 
         &#1111;Frequency of visit]&#1111;single] 
         &#1111;Adults]&#1111;quantity] 
         &#1111;Children]&#1111;quantity] 
         &#1111;Items ordered]&#1111;multiple] 
         &#1111;Other items ordered]&#1111;character] 
         &#1111;Amount spent] 
         &#1111;Speed of service]&#1111;single] 
         &#1111;Cleanliness]&#1111;single] 
         &#1111;Parking]&#1111;single] 
         &#1111;Quality of food]&#1111;single] 
         &#1111;Choice of food]&#1111;single] 
         &#1111;Overall Performance]&#1111;quantity]
         &#1111;Comments]&#1111;character] 
         &#1111;Age]&#1111;single] 
         &#1111;Gender]&#1111;single] 
         &#1111;Zipcode/postcode] &#1111;character] 
         &#1111;Scoring in percentages]&#1111;quantity] 
    ) ON &#1111;PRIMARY]
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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

instead of directly echoing out... store the attribute data... and then use it with the element data..

eg:

Code: Select all

$attribute = '';

// callback function when a tag is opened
function startElement($parser, $name, $attrs)
{
  global $attribute; 
  $attribute = 'foo';
}

// callback function when reading data between opening and closing tag
function inElement($parser, $data)
{
  global $attribute;

  // use $attribute
}
Az
Forum Newbie
Posts: 4
Joined: Fri Apr 08, 2005 5:35 am

Post by Az »

Thanks, I will give it a go.

Do you see any other glaring mistakes in my code or ways to improve on it?
hedge
Forum Contributor
Posts: 234
Joined: Fri Aug 30, 2002 10:19 am
Location: Calgary, AB, Canada

Post by hedge »

I've done something similar but I used an xsl template and did an xsl transform on it to convert the xml to sql, much easier IMHO.
Az
Forum Newbie
Posts: 4
Joined: Fri Apr 08, 2005 5:35 am

Post by Az »

Interesting.
Any chance of some sample code?

Thanks,
~Az
Az
Forum Newbie
Posts: 4
Joined: Fri Apr 08, 2005 5:35 am

Post by Az »

I just found the XSLT book I have.
I will take a look through that, so sample code may not be needed.

Thanks for the guidance.
Post Reply