How to parse this XML file into a MySQL table?

Question

I am trying to parse through an XML file structured like below. What I am trying to achieve is to convert this into a table format to be used in MySQL. So for example, in this case I'd have a table like this with a column format with the following example rows:

name | industry_permid | trbc_code | mnemonic
Juvenile Products & Accessories | 4294951612 | 5320501015 | NULL
Life & Health Insurance | 4294952862 | 55301030| LINS

My XML file:

<conceptSet>
    <concept>
      <conceptId qcode="B:1389" /> 
      <type qcode="cptType:2" /> 
      <sameAs qcode="P:4294951612" /> 
      <name role="nameRole:main" xml:lang="en">Juvenile Products & Accessories</name> 
      <broader qcode="B:199" /> 
      <rtr:stage current="stg:live" /> 
      <sameAs qcode="TRBC-2012-Hierarchical-ID:5320501015" /> 
    </concept>
    <concept>
      <conceptId qcode="B:139" /> 
      <type qcode="cptType:2" /> 
      <sameAs qcode="P:4294952862" /> 
      <name role="nameRole:mnemonic" xml:lang="en">LINS</name> 
      <name role="nameRole:main" xml:lang="en">Life & Health Insurance</name> 
      <broader qcode="B:136" /> 
      <rtr:stage current="stg:live" /> 
      <sameAs qcode="TRBC-2012-Hierarchical-ID:55301030" /> 
    </concept>
</conceptSet>

The problem is whenever I try to access elements in this XML tree, I'm only getting the ones with the name tags. I can't figure out how to access the elements without any tags like the qcodes and stuff. I'm using the default Java XML parser if that's any help.

This is the code that I have so far which just prints out null every time I try and get an attribute.

    File mapping = new File("blah.xml");
    DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
    DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
    Document doc = dBuilder.parse(mapping);
    doc.getDocumentElement().normalize();

    NodeList nodeList = doc.getElementsByTagName("concept");

    for (int i = 0; i < nodeList.getLength(); i++) {
        System.out.println(nodeList.item(i).getAttributes().getNamedItem("qcode"));
    }

Community · Accepted Answer · 2017-05-23 11:49:30Z

1

In your for-loop, nodeList.item(i) is a concept element. So you're trying to retrieve the qcode attribute from the concept element, which it doesn't have.

You can iterate over the child nodes of the concept element to get the elements you need, f.e.:

for (int i = 0; i < nodeList.getLength(); i++) {
    NodeList children = nodeList.item(i).getChildNodes();
    for (int j = 0; j < children.getLength(); j ++) {
        System.out.println(children.item(i).getAttributes().getNamedItem("qcode"));
    }
}

Or you can retrieve the nodes you need directly using XPath, see this answer, for example.

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Oct 14, 2013 at 20:10

Torious

3,43420 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to parse this XML file into a MySQL table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related