Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2015
    Posts
    3

    Unanswered: unwanted results when mapping xml fields to mysql

    Newbie here and I have a painful xml file that has 38 fields. I'll list a few below;

    Code:
    <shoes>
    <type>men</type>
    <num>13</num>
    <dept>2</dept>
    <city>la</city>
    <heading>nike</heading>
    <desc>this is the top model</desc>
    </shoes>
    The only fields I need are city, heading and desc. The other pain is the table I need to insert these into as it has around 22 fields and very moody. So when I try;


    Code:
    LOAD DATA INFILE 'file.xml' //I cannot get it to accept LOAD XML 
    INTO TABLE `myTable` 
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\r\n'
    IGNORE 1 LINES
    
    (
    heading,descr,city
    )
    I get hundreds of rows and have to delete empty-rows, rows with a number, rows with junk data, and when I do get a row I can use, it only has the desc and no heading or city. If use just one field; desc, it kind of works but skips the city and heading. When I try including all 22 fields from myTable I get an empty set. So the issue is that I can't map these fields for some reason. I almost think myTable would take the data if I could define the fields such as;

    Code:
    city='city',descr='descr',heading='heading'
    I've tried converting to JSON and other formats hoping for a better mapping without avail. I could change the xml file if I find a code that would work. For instance, I could just delete fields or whatever might work. But the myTable is wrapped with a php code and the fields etc can't be altered.
    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you tried LOAD XML
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2015
    Posts
    3
    Yes, as indicated and commented // in my LOAD DATA line I cannot get myTable to accept LOAD XML. I wished I could

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Its a while since Ive used load xml,....
    What error message did you get?

    If yoy cant get load xml to work, then the only other way i can think of would be to pricess the file as say a PHP batch job and ibsert the rows that way. But load xml is the way to go.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2015
    Posts
    3
    When trying LOAD XML I get an empty set. So as you've indicated my best bet would be to figure out why it's not taking xml, which I can't. Can I alter the xml file somehow to make it work? I tried to get rid of junk data in my xml file and it helped when I tried LOAD DATA but still an empty set with LOAD XML.

    I'm also new to php so writing a script might be an issue but I'm going to work on that.

    Thanks for your help

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    No
    I think your best option is to use loadxml.

    If you cannot get that to work then write a pre processor in what ever tool you have, be that PERL, PHP, VBA, Java or whatever.

    If you are uploading the file and getting no results then there must be errors in the file, and somewhere there must be an error log.

    How and where are you invoking the load xml command?. Loadxml is the tool that should do the job..

    If you can provide an edited cutdown version of your xml file then id suggest attaching it to this thread
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •