CODEWITHSUNDEEP
sqlxmlsqlite
Jin
Jin

Reputation: 6145

Import data into SQLite from XML

I am trying to import data stored in a XML file into my SQLite database. The insert query looks something like the following:

INSERT INTO table1 VALUES (rank, name)
SELECT X.record.query('rank').value('.', 'INT'),
       X.record.query('name').value('.', 'VARCHAR(30)')
FROM (
  SELECT CAST(x AS XML)
  FROM OPENROWSET(BULK 'data.xml', SINGLE_BLOB) AS T(x)
     ) AS T(x)
CROSS APPLY x.nodes('data/record') AS X(record);

The XML data looks like this:

<data>
    <record>
        <rank>1</rank>
        <name>One</name>
    </record>
    <record>
        <rank>2</rank>
        <name>Two</name>
    </record>
    ...
</data>

However, I am getting "SQL command not properly ended at the outer SELECT statement, and I can't figure out why. Any ideas?

Upvotes: 2

Views: 3306

Answers (1)

Himanshu
Himanshu

Reputation: 2454

You seem to be using SQL Server's SQL dialect with SQLite which won't work. You should convert your xml to SQLite's SQL dialect before you start talking to it. More specifically, convert your xml to SQL that SQLite knows using Python/C#/XSLT etc instead of directly within SQL.

Upvotes: 2

Related Questions