Solved

XML and MySQL

Posted on 2011-03-21
1
572 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
Comment Utility
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now