CODEWITHSUNDEEP
androidxmlsqliteimport
Surya
Surya

Reputation: 140

how to import data from xml file to sqlite database

I have two XML files for the android project first consist approx 550 records of districts in India and second file consists of approx 40 records of states of India

Now I want to import it in an SQLite database which consists of two fields first district and second state and each record from XML should insert to separate row of SQLite.

I googled but not found any solution can anyone suggest a way or any tool for doing it.

Upvotes: 1

Views: 10712

Answers (1)

qais
qais

Reputation: 1878

You probably have found a solution, but for others who may be looking for a similar solution.

Assuming you have data in XML as:

<rows>
    <row>
        <state>Mississippi</state>
        <district>Holmes</district>
    </row>

    <row>
        <state>Texas</state>
        <district>Cayuga ISD</district>
    </row>
    ...
</rows>

You can use this code that I used somewhere:

public class MySQLPullParser {

    private String currTag = null;  
    private boolean firstTag = true;

    public String parseXML(Context ctx) {
        try {
            StringBuilder sb = new StringBuilder(500);  // give sufficient length to start with
            sb.append("INSERT INTO " + yourTableName + " VALUES (");

            XmlPullParserFactory xppFactory = XmlPullParserFactory.newInstance();
            xppFactory.setNamespaceAware(true);
            XmlPullParser xpp = xppFactory.newPullParser();

            URL yourXmlPath = new URL(url);
            InputStream is = yourXmlPath.openConnection().getInputStream();

                    xpp.setInput(is,null);

                    int e = xpp.getEventType();
            while (e != XmlPullParser.END_DOCUMENT)
            {
                if(e == XmlPullParser.START_TAG) {
                        currTag = xpp.getName();
                }
                else if (e == XmlPullParser.END_TAG) {
                        currTag = null;
                }
                else if (e == XmlPullParser.TEXT) {
                    if ( currTag.equals("state") ) {    // first table column
                            if (firstTag)
                                sb.append( xmlText + "(" ); // for first row insert
                            else
                                sb.append( xmlText + ",(" );
                    }

                    else if ( currTagType.equals("district") ){
                        sb.append( "'" + xmlText + "')" );  // last table column should have a closing paran ")"
                    }
                }
                e = xpp.next();
            }

        }   catch (XmlPullParserException e) {
            e.printStackTrace();
        } catch (IOException e1) {
            e1.printStackTrace();
        }

        return sb.toString();
    }
}

parseXML() will return the SQL INSERT statement that you can execute:

MySQLPullParser spp = new MySQLPullParser();
db.execSQL( spp.parseXML(mContext) );

Upvotes: 4

Related Questions