Importing XML Data To A Database

8

The Above Listing Shows The Content of The Student.xml File That Stares Student Information, Such As Name, AGE, Address, And Standard.

You NEED TO CREATE A TABLE IN THE DATABASE THATE CAN Store The Data Imported from the student.xml file.

Listing 8-9 Shows How To Create The Student Table In The Information Database:

Listing 8-9: Creating The Student Table

Use information

Mysql> Create Table Student

->

-> Name varchar (30) Not null,

-> AGE INTEGER NOT NULL,

-> Address varchar (30) Not null,

-> Standard Integer

->);

The Above Listing Creates The Student Table, In Which You Can Insert Data from The Student.xml File.

Listing 8-10 Shows How To Import The Student.xml File Into A Database, by Parsing The Student.xml File Using The Sax Approach:

Listing 8-10: Parsing the student.xml file using sax

$ CTAG = "";

// array to hold the value for the sql stat.

$ VALUES = array ();

$ Elements = Array ("Name", "Age", "Address", "Standard");

// XML File to Parse

$ xmlfile = "student.xml";

// Database Parameters

$ hostname = "localhost";

$ usrname = "root";

$ Password = "root123";

$ Database = "Information";

$ TABLE = "student";

// Processes On Encountering a opening tag in the xml.function StartelementHandler ($ Parser, $ N1, $ Attributes)

{

Global $ CTAG;

$ CTAG = $ N1;

}

// processes on encountering a closing tag in the xml.

Function endelementhandler ($ Parser, $ N)

{

Global $ VALUES, $ CTAG;

// Import Database Link and Table Name.

Global $ Connection, $ TABLE

// if Ending tag

// Implies end of record.

IF (STRTOLOWER ($ N1) == "item")

{

// generating the query string.

$ query = "INSERT INTO STUDENT";

$ query. = "(Name, Age, Address, Standard";

$ query. = "VALUES". "/"); "/"); ";

// processing the query.

$ Result = mysql_query ($ query) or Die ("Error In Query: $ query.".

mysql_error ());

// Reset All INTERNAL Counters and Arrays.

$ VALUES = array ();

$ CTAG = "";

}

}

// processes on encountering a closing tag in the xml.

Function CharacterDataHandler ($ PARSER, $ DATA)

{

Global $ CTAG, $ VALUES, $ Elements;

// Lowercase Tag Name

$ ctag = strtolower ($ CTAG);

// Look for tag in $ elements [] array.

IF (In_Array ($ CTAG, $ Elements) && Trim ($ DATA)! = "" "$ VALUES [$ CTAG] = mysql_escape_string ($ data);

}

// Initializing The Sax Parser.

$ XML_PARSER = XML_PARSER_CREATE ();

// set Callback functions.

XML_SET_ELEMENT_HANDLER ($ XML_PARSER, "StartElementHandler", "endelementhandler");

XML_SET_CHARACTER_DATA_HANDLER ($ XML_PARSER, "CharacterDataHandler);

// Open connection to database.

$ connection = mysql_connect ($ Hostname, $ Username, $ Password) OR DIE ("Unable to connect!");

MySQL_SELECT_DB ($ Database) or Die ("Unable to select Database!"); // read XML File

IF ($ fp = fopen ($ XMLFILE, "R"))))))

{

DIE ("File I / O Error: $ XMLFile");

}

// Parse XML

While ($ DATA = FREAD ($ FP, 4096)))

{

// Error Handler

IF (! XML_PARSE ($ XML_PARSER, $ DATA, Feof ($ fp)))

{

$ error_code = XML_GET_ERROR_CODE ($ XML_PARSER);

"" XML Parser Error (Error Code ". $ Error_code."): ".

XML_ERROR_STRING ($ Error_code). "
Error Occurred At line".

XML_GET_CURRENT_LINE_NUMBER ($ XML_PARSER));

}

}

?>

The Above Listing Shows How To Parse The Student.xml File Using The Sax Approach. In the Above Listing:

The instance of the SAX parser is initialized using the xml_parser_create () function, and is configured to call various functions, such as startElemenHandler () and endElementHandler (). The startElemenHandler () function executes when a starting tag is processed in the XML document, And the endelementhandler () Function Executes by Closing Tag in The XML Document IS Processed.

You need to pass the tag name and the parser as arguments to the tag handler functions, such as startElemenHandler () and endElementHandler (). The characterDataHandler () function executes when the character data in the XML document is processed. You need to specify the Character Data (CDATA) Text As Arguments In The CharacterDataHandler () Function.

The SAX parser retrieves and stores data from the student.xml file in the associative array, $ values. The SAX parser retrieves data after finding character data having element name, such as name, age, address, and standard. The SAX parser creates a Query string from the value. Obtained from the $ values ​​array.

You Can Also Parse The XML Document Using The Dom Approach.Listing 8-11 Shows How To Parse The XML Document Using DOM:

Listing 8-11: Parsing XML Document Using DOM

$ XML_FILE = "Student.xml";

$ doc = xmldocfile ($ XML_FILE);

$ Name = array ();

$ AGE = array ();

$ address = array ();

$ standard = array ();

$ root = $ doc-> root ();

$ nodes = $ root-> children ();

$ a = 0;

$ b = 0;

$ C = 0;

For ($ x = 0; $ x

{

IF ($ NODES [$ x] -> type == XML_Element_Node)

{

$ text = $ nodes [$ x] -> children ();

For ($ I = 0; $ i

{

$ TEMP = $ TEXT [$ I] -> Children ();

For ($ j = 0; $ j

{

Echo $ TEMP [$ J] -> Content;

}

}

}

}

?>

The Above Listing Shows How To Parse The Student.xml File Using The Doom Approach.

Closing The Connection To a Database After Importing Data

You NEED To Close The Database Connection After Importing The XML Document In The Database. The code to close the database connection is:

XML_Parser_Free ($ XML_PARSER);

MySQL_Close ($ connection);

The above code shows how to close the connection to the database using the mysql_close () function. The xml_parser_free () function frees the parser and returns true if $ xml_parser is valid otherwise returns the value, False.

Alternative method to import XML Documents

Using PHP, you can import XML documents in a database without specifying the table name, field names, and values ​​in the SQL statement. For example, you want to import the datastudent.xml file into a table, stud.

Listing 8-12 shows the contents of the datastudent.xml file:

Listing 8-12: Contents of DataStudent.xml File

12