parse xml to store data in mysql

Question

I need to store xml string in mysql database. I have multiple records like this

<Table1><c1><![CDATA[0001]]></c1><c2></c2><c3></c3><c4></c4><c5></c5><c6></c6><c7></c7><c8></c8><c9><![CDATA[na80]]></c9><c10><![CDATA[pp61]]></c10><c11><![CDATA[uc131]]></c11><c12><![CDATA]></c12><c13><![CDATA[سکول]]></c13><c14><![CDATA[25-May-13 12:00:00 AM]]></c14><c15><![CDATA[True]]></c15><c16></c16><c17><![CDATA[1]]></c17><c18><![CDATA[1867]]></c18><c19><![CDATA[0]]></c19><c20><![CDATA[1867]]></c20><c21></c21></Table1>

What you want to do with this xml. Please mention it clearly. Do you want to store each xml tag value in different column? — kinshuk4, Oct 07 '15 at 07:09
Don't know how MySQL deals with this, but one of your CDATA sections misses the inner []... SQL Server throws an error... — Shnugo, Oct 07 '15 at 07:18
What does "multiple records" mean? If "Table1" is your root tag, there should be kind of a row tag too? — Shnugo, Oct 07 '15 at 07:20
Yes @kinshuk4 I have multiple entries each separating from each other by
tag. I need to insert all the data in my xml to my database on server side. — Kamran, Oct 07 '15 at 09:47
@Shnugo each row is separated from other by tag and is the column number cdata is data what is encloses inside that column — Kamran, Oct 07 '15 at 09:50
What general-purpose language do you use? PHP? ASP? Perl? Python? You can easily transform XML with XSLT, then import cleaned format using MySQL's [LOAD XML](https://dev.mysql.com/doc/refman/5.5/en/load-xml.html) query. — Parfait, Oct 07 '15 at 13:58

score 0 · Answer 1 · answered Oct 07 '15 at 11:01

your question is still not clear...

This is how to pick the values in SQL Server, I don't know the syntax in MySQL but it will be similar.

Change the datatypes (varchar(max)) to any needed type.

DECLARE @xml XML=
'<Table1><c1><![CDATA[0001]]></c1><c2></c2><c3></c3><c4></c4><c5></c5><c6></c6><c7></c7><c8></c8><c9><![CDATA[na80]]></c9><c10><![CDATA[pp61]]></c10><c11><![CDATA[uc131]]></c11><c12><![CDATA[]]></c12><c13><![CDATA[سکول]]></c13><c14><![CDATA[25-May-13 12:00:00 AM]]></c14><c15><![CDATA[True]]></c15><c16></c16><c17><![CDATA[1]]></c17><c18><![CDATA[1867]]></c18><c19><![CDATA[0]]></c19><c20><![CDATA[1867]]></c20><c21></c21></Table1>';
SELECT x.y.value('c1[1]','varchar(max)') AS c1
      ,x.y.value('c2[1]','varchar(max)') AS c2
      ,x.y.value('c3[1]','varchar(max)') AS c3
      ,x.y.value('c4[1]','varchar(max)') AS c4
      ,x.y.value('c5[1]','varchar(max)') AS c5
      ,x.y.value('c6[1]','varchar(max)') AS c6
      ,x.y.value('c7[1]','varchar(max)') AS c7
      ,x.y.value('c8[1]','varchar(max)') AS c8
      ,x.y.value('c9[1]','varchar(max)') AS c9
      ,x.y.value('c10[1]','varchar(max)') AS c10
      ,x.y.value('c11[1]','varchar(max)') AS c11
      ,x.y.value('c12[1]','varchar(max)') AS c12
      ,x.y.value('c13[1]','varchar(max)') AS c13
      ,x.y.value('c14[1]','varchar(max)') AS c14
      ,x.y.value('c15[1]','varchar(max)') AS c15
      ,x.y.value('c16[1]','varchar(max)') AS c16
      ,x.y.value('c17[1]','varchar(max)') AS c17
      ,x.y.value('c18[1]','varchar(max)') AS c18
      ,x.y.value('c19[1]','varchar(max)') AS c19
      ,x.y.value('c20[1]','varchar(max)') AS c20
      ,x.y.value('c21[1]','varchar(max)') AS c21

FROM @xml.nodes('/Table1') AS x(y);

Exactly i have done it the same way as you `SELECT data.value('c1[1]', 'varchar(4)'), data.value('c2[1]', 'nVARCHAR (max)'), data.value('c3[1]', 'nVARCHAR (max)'), data.value('c4[1]', 'nVARCHAR (max)'), data.value('c5[1]', 'nVARCHAR (max)'), data.value('c6[1]', 'nVARCHAR (max)'), data.value('c7[1]', 'nVARCHAR (max)'), data.value('c8[1]', 'nVARCHAR (max)'), FROM @XML_SIDetails.nodes('/Table1') AS ParamValues(data)` Now i need to do the same in mysql but the syntax for mysql is not clear to me. — Kamran, Oct 07 '15 at 12:36
@Kamran, I do not work with MySQL. What I found on quick research is this: http://stackoverflow.com/q/11281436/5089204. Hope this helps you... Please vote up if my answer could help you, thx! — Shnugo, Oct 07 '15 at 15:47

parse xml to store data in mysql

1 Answers1