Solved

XML and MySQL

Posted on 2011-03-21
1
576 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
Creating and Managing Databases with phpMyAdmin in cPanel.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

860 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