Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

XML and MySQL

Posted on 2011-03-21
1
Medium Priority
?
581 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 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

A new era in Cloud training has arrived.

A day that will go down in Cloud history.. But are you ready for it? Will you accept this Cloud challenge?

Question has a verified solution.

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

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 this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

705 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