-2

I want to get the value of the parent child node in XML but the value of the childnode is overriding it because they're of the same name

How do I make it pick the parent node value without the childnode value overriding it?

I'm using LOAD XML LOCAL INFILE

LOAD XML LOCAL INFILE 'person.xml'
INTO TABLE document
ROWS IDENTIFIED BY '<document>';

This is the sample XML data

<document>
      <name>John</name>
      <age>19</age>
      <street>
                <name>Johnson Street</name>
      </street>
</document>

Instead of the name tag under document tag to print John it's printing Johnson Street

ken4ward
  • 2,246
  • 5
  • 49
  • 89
  • rename the second to fullname, and don't use duplicate names for different hierarchy sections – nbk Jan 14 '21 at 20:56
  • Is there anyway to rename it programmatically without affecting the others? The file is a large file of almost 40Mb I think. – ken4ward Jan 15 '21 at 00:21
  • see if the prigram that generates the xml can be changed, else if you god thousands if xml files you have to write a simple program – nbk Jan 16 '21 at 15:55

1 Answers1

1

It should be doable using xpath:

SET @xml = '<document>
      <name>John</name>
      <age>19</age>
      <street>
                <name>Johnson Street</name>
      </street>
</document>
';
SET @i =1;
SELECT @i, ExtractValue(@xml, './/street/name');

You can try it here.

Jack Fleeting
  • 24,385
  • 6
  • 23
  • 45