Solved

XML and MySQL

Posted on 2011-03-21
1
577 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:GroganJ
1 Comment
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 35184098
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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question