XML parsing to a SQL code

Question

I'll get folders that contain XML code. I need to put all that code into a database so I need an algorithm that parses XML into SQL.

private static void parseXml(String xml) {
   Document doc = Jsoup.parse(xml);
   StringBuilder queryBuilder;
   StringBuilder columnNames;
   StringBuilder values;

for (Element row : doc.select("row")) {   
    // Start the query   
    queryBuilder = new StringBuilder("insert into customer(");
    columnNames = new StringBuilder();
    values = new StringBuilder();

    for (int x = 0; x < row.children().size(); x++) {

        // Append the column name and it's value 
        columnNames.append(row.children().get(x).tagName());
        values.append(row.children().get(x).text());

        if (x != row.children().size() - 1) {
            // If this is not the last item, append a comma
            columnNames.append(",");
            values.append(",");
        }
        else {
            // Otherwise, add the closing paranthesis
            columnNames.append(")");
            values.append(")");
        }                                
    }

    // Add the column names and values to the query
    queryBuilder.append(columnNames);
    queryBuilder.append(" values(");
    queryBuilder.append(values);

    // Print the query
    System.out.println(queryBuilder);
}

}

INPUT:

<Customers>
   <row> 
      <CustId>1</CustId>
      <Name>Woodworks</Name>
      <City>Baltimore</City>
   </row>
   <row> 
      <CustId>2</CustId>
      <Name>Software Solutions</Name>
      <City>Boston</City>
   </row>
   <row> 
      <CustId>3</CustId>
      <Name>Food Fantasies</Name>
      <City>New York</City>
   </row>
</Customers>

OUTPUT:

insert into customer(custid,name,city) values(1,Woodworks,Baltimore)
insert into customer(custid,name,city) values(2,Software Solutions,Boston)
insert into customer(custid,name,city) values(3,Food Fantasies,New York)

The problem with this code is hardcoded to work just for that format. I need an algorithm that is generic so it can run any xml code and extract data. So far I don't know the format of xml files i ll get.

Can you help me to build an algorithm that can give me SQL statements for any XML file? Thank you.

you need at least some "ground rules" for that format. If I give you `` what do you do with that?? — , Aug 06 '17 at 14:58
I see your point. So I guess I should see how xml file looks like before coding the parser. — Radu, Aug 06 '17 at 15:01
My app should get xml code (it will describe the products of different markets) and I need to put those detail into my database. There will be a lot of markets and I believe that i can't "hardcode" an algorithm as the one above. — Radu, Aug 06 '17 at 15:04
Tip: You should take a look at XSLt. XSL can transform XML into any other format (XML, CSV, text). Easy to do and available in any language or system command line tools. — Stephane Lallemagne, Aug 07 '17 at 10:25
When you need some dynamic sql in mysql, you can use [prepared statements](https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html) for that. Here is an [example](https://stackoverflow.com/a/16359654/7564182), how powerful the prepared statements can be. — Myonara, Aug 07 '17 at 14:43
The only parts of your method that are really hard coded are the table name (`"customer"`) and the elements you are selecting (`"row"`). Pass these into the method and it should be more reusable - and change it to use prepared statements — Java Devil, Aug 08 '17 at 03:54

score 0 · Answer 1 · answered Aug 14 '18 at 00:12

It'll be impossible to insert an XML document's data into a table without knowing what the schema is, unless you plan on creating a new table every time you get an XML document with a new schema. You may have tables with schema A and schema B set up, but if you get and XML file with schema C, it won't fit into either table, so constructing an INSERT off of that won't get you anywhere.

If you have a range of schemas the XML file could be, I would set up some sort of test in your Java that determines the schema (maybe using a set of XSD files) and selects the appropriate table to dump the data into, then builds and executes the SQL statement based on that.

If you really do want to be able to intake ANY XML file and drop it into a database, you might want to check out this StackOverflow question. Once you have an XSD from whatever XML file you receive, you should be able to parse that out in Java and generate the SQL needed to build a table that fits it, and then parse the XML file and drop it in there. Honestly, I would question why you don't know the format of the files coming to you and see if you can instead nail down the formats and go with the first option.

XML parsing to a SQL code

1 Answers1