XML and MySQL

All,

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,
Content_XML   TEXT,
INDEX   LinksTo_Index (LinksTo_i),
PRIMARY KEY    (Index_i)
) TYPE=InnoDB;

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"?>
<currentProject>
      <projectid>1</projectid>
      <projectType>my</projectType>
      <userid>1</userid>
      <author>Jim Henchkie</author>
      <reviewtype>peer</reviewtype>
      <title>Entering the Chinese market</title>
      <created>15.11.2010</created>
      <lastedited>30.12.2010</lastedited>
      <projectHTML><![CDATA[
           <div id="documentContent">
                          </div>
      ]]>
      </projectHTML>
</currentProject>

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(
Content      TEXT,
Idx      INT
) RETURNS INT
BEGIN
INSERT INTO ProjectsXMLTable (LinksTo_i, Content_XML)
      VALUES (Idx, Content);
SELECT Index_i FROM ProjectsXMLTable WHERE LinksTo_i = Idx INTO @ContentIdx_i;
RETURN @ContentIdx_i;
END

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?
GroganJAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
GroganJ,

This maybe a helpful resource for you given what you are working on:
A MySQL Tidbit: In-line XML Parsing - http:A_3574.html

However, I would like you to look at examples of setting a variable to an XML string I have there as I suspect you are pretty much where you need to be, but just need to use single quotes instead of double ones for MySQL string.  That allows your strings to have double quotes within them.

i.e.,
set @xml = '<?xml version="1.0" encoding="UTF-8"?>
<currentProject>
      <projectid>1</projectid>
      <projectType>my</projectType>
      <userid>1</userid>
      <author>Jim Henchkie</author>
      <reviewtype>peer</reviewtype>
      <title>Entering the Chinese market</title>
      <created>15.11.2010</created>
      <lastedited>30.12.2010</lastedited>
      <projectHTML><![CDATA[
           <div id="documentContent">
                          </div>
      ]]>
      </projectHTML>
</currentProject>';

Open in new window


Regards,

Kevin
0
All Courses

From novice to tech pro — start learning today.