Error during Insert entries from xml file to Oracle Database [closed]

Question

for a school project I've to add to my db some data found online. I've a huge xml file like this:

<?xml version="1.0" encoding="utf-8"?>
<users>
  <row Id="-1007" Reputation="1" CreationDate="2021-09-30T18:39:12.143" DisplayName="EX1" LastAccessDate="2021-09-30T18:39:12.143" AboutMe="..." Views="0" UpVotes="0" DownVotes="0" />
  <row Id="-1006" Reputation="1" CreationDate="2021-09-27T19:40:44.437" DisplayName="EX2" LastAccessDate="2021-09-27T19:40:44.437" AboutMe="..." Views="0" UpVotes="0" DownVotes="0" />
  <row Id="-1005" Reputation="1" CreationDate="2021-07-29T14:07:57.580" DisplayName="EX3" LastAccessDate="2021-07-29T14:07:57.580" AboutMe="..." Views="0" UpVotes="0" DownVotes="0" />
</users>

My table is:

CREATE TABLE USERS
(
 Id  NUMBER,
 Reputation  NUMBER,
 CreationDate    TIMESTAMP,
 DisplayName VARCHAR2(100) NOT NULL,
 LastAccessDate  TIMESTAMP,
 AboutMe VARCHAR2(500),
 Views   INTEGER DEFAULT 0,
 UpVotes INTEGER DEFAULT 0,
 DownVotes   INTEGER DEFAULT 0,   
 CONSTRAINT PK_USERS PRIMARY KEY(Id) 
);

So, I've tried to write an INSERT:

INSERT INTO USERS (Id, Reputation, CreationDate, DisplayName,LastAccessDate,AboutMe,Views,UpVotes,DownVotes)
SELECT MY_XML.row.query('Id').value('.', 'NUMBER'),
       MY_XML.row.query('Reputation').value('.', 'NUMBER'),
       MY_XML.row.query('CreationDate').value('.', 'TIMESTAMP'),
       MY_XML.row.query('DisplayName').value('.', 'VARCHAR2(100)'),
       MY_XML.row.query('LastAccessDate').value('.', 'TIMESTAMP'),
       MY_XML.row.query('AboutMe').value('.', 'VARCHAR2(500)'),
       MY_XML.row.query('Views').value('.', 'INTEGER'),
       MY_XML.row.query('UpVotes').value('.', 'INTEGER'),
       MY_XML.row.query('DownVotes').value('.', 'INTEGER')
  FROM (
        SELECT CAST(MY_XML AS xml)
          FROM OPENROWSET(BULK 'C:\Users.xml', SINGLE_BLOB) AS T(MY_XML)
        ) AS T(MY_XML)
  CROSS APPLY MY_XML.nodes('users/row') AS MY_XML (Customer);

But it gives me this error below

Error report -
SQL Error: ORA-01747: invalid user.table.column, table.column, or column specification
01747. 00000 -  "invalid user.table.column, table.column, or column specification"
*Cause:    
*Action:

It's my first time with this kind of things. How can I fix this?

Answer 1

You are attempting to use SQL Server syntax to parse an XML document in an Oracle database. This won't work. You will need to use Oracle syntax instead.

Also, Oracle can't read an arbitrary file off the filesystem just by passing the filename. Instead, you will need to create a directory somewhere on your machine (or use an existing directory), and then create an Oracle directory object corresponding to this filesystem directory. Oracle can only read files off the filesystem when given a directory object and a filename.

If your directory is C:\example, then you'll need to copy your users.xml file into C:\example and run something like:

CREATE DIRECTORY EXAMPLE_DIR AS 'C:\example`;

Once you've got this set up, something like the following should be able to read the data out of your XML file and into your table:

INSERT INTO USERS (Id, Reputation, CreationDate, DisplayName, LastAccessDate, AboutMe, Views, UpVotes, DownVotes)
SELECT Id, Reputation, TO_TIMESTAMP(CreationDateStr, 'YYYY-MM-DD"T"HH24:MI:SS.FF3'), DisplayName, TO_TIMESTAMP(LastAccessDateStr, 'YYYY-MM-DD"T"HH24:MI:SS.FF3'), AboutMe, Views, UpVotes, DownVotes
  FROM XMLTABLE(
        '/users/row'
        PASSING XMLTYPE(BFILENAME('EXAMPLE_DIR', 'users.xml'), NLS_CHARSET_ID('AL32UTF8'))
        COLUMNS
            Id INTEGER PATH '@Id',
            Reputation INTEGER PATH '@Reputation',
            CreationDateStr VARCHAR2(30 CHAR) PATH '@CreationDate',
            DisplayName VARCHAR2(100 CHAR) PATH '@DisplayName',
            LastAccessDateStr VARCHAR2(30 CHAR) PATH '@LastAccessDate',
            AboutMe VARCHAR2(100 CHAR) PATH '@AboutMe',
            Views VARCHAR2(100 CHAR) PATH '@Views',
            UpVotes VARCHAR2(100 CHAR) PATH '@UpVotes',
            DownVotes VARCHAR2(100 CHAR) PATH '@DownVotes'
    );

The expression '/users/row' selects all the rows from the file after it gets parsed as XML. Note we supply the name of the Oracle directory and the filename in the call to BFILENAME. Then in the COLUMNS clause we pull out the data of interest for each row element.

I found it easier to pull the dates out of the XMLTABLE call as strings and parse them as timestamps afterwards. There might be a way of parsing them to timestamps in XPath/XQuery, but the above appears to work.