XML and MySQL
Posted on 2011-03-21
I am having some problems loading raw XML directly into a mySQL database and I'm hoping you can help.
I have a table defined as follows:
CREATE TABLE ProjectsXMLTable
Index_i INT NOT NULL AUTO_INCREMENT,
LinksTo_i INT NOT NULL,
INDEX LinksTo_Index (LinksTo_i),
PRIMARY KEY (Index_i)
where Content_XML is the raw XML data.
Here is a sample XML file that I want to load into the table ...
<?xml version="1.0" encoding="UTF-8"?>
<title>Entering the Chinese market</title>
the file is actually much bigger, but I've chopped out a lot for convenience here.
I have been trying to load the XML into the table via a stored proc ...
CREATE FUNCTION AddProjectContent(
) RETURNS INT
INSERT INTO ProjectsXMLTable (LinksTo_i, Content_XML)
VALUES (Idx, Content);
SELECT Index_i FROM ProjectsXMLTable WHERE LinksTo_i = Idx INTO @ContentIdx_i;
In the command line I use the following:
SET @Content = "<here I insert the XML string above>";
CALL AddProjectContent(@Content, 1);
but it gives me an error. It looks like it doesn't like the double quotes in the XML string.
So, the question is ... how do I get a piece of XML into the table that I've got?