Home » SQL & PL/SQL » SQL & PL/SQL » Import XML file into XMLType Column (Database 11.2.0.2.0)
Import XML file into XMLType Column [message #675554] Fri, 05 April 2019 10:26 Go to next message
ator
Messages: 43
Registered: March 2009
Member
Hello,

i looked for the solution everywhere but i could not find anything.

We received from different source file xml and we load the file in a XMLType column as follow.

UPDATE TAB_X
SET   XMLTYPE_COLUMN = XMLType(bfilename('DIRECTORY', 'FILE.XML'),
                                                 nls_charset_id('AL32UTF8'));

The procedure works fine until there are empty tags (<tag />) in the file and it throw ORA-30625: method dispatch on NULL SELF argument is disallowed.
Right now i delete the empty tags and read the file but there is a way to avoid having the oracle error and load the file?.

Thank you for your help and i apologize if somebody else has already asked but i did not found it.

Ator

Report message to a moderator

Re: Import XML file into XMLType Column [message #675555 is a reply to message #675554] Fri, 05 April 2019 11:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Can you post an XML that raises this error so we can search.

Report message to a moderator

Re: Import XML file into XMLType Column [message #675556 is a reply to message #675555] Fri, 05 April 2019 11:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

It works for me:
SQL> host type c:\x0.txt
<?xml version="1.0" ?>
<test>
<tag />
</test>

SQL> select xmltype(bfilename('MY_DIR','x0.txt'),nls_charset_id('WE8MSWIN1252')) from dual;
XMLTYPE(BFILENAME('MY_DIR','X0.TXT'),NLS_CHARSET_ID('WE8MSWIN1252'))
--------------------------------------------------------------------------------------------------
<?xml version="1.0"?>
<test>
  <tag/>
</test>

1 row selected.

SQL> @v

Oracle version: 11.2.0.4.181016 EE

Report message to a moderator

Re: Import XML file into XMLType Column [message #675581 is a reply to message #675556] Mon, 08 April 2019 00:48 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
You gave only small information about the XML and the PROCEDURE...
Have a look at:
https://community.oracle.com/message/14430089#14430089
https://community.oracle.com/message/13859303#13859303

Report message to a moderator

Re: Import XML file into XMLType Column [message #675592 is a reply to message #675554] Mon, 08 April 2019 04:09 Go to previous messageGo to next message
ator
Messages: 43
Registered: March 2009
Member
Hello,

thank you for your reply, in attachment you can find a file that gives me error, of course i had to replace all the info for privacy reason (i had to rename it to txt but is a xml file).

In the file there are three empty branches, if i remove them it will load the file without problem.

Here is the complete version of the DB, hopefully it can help Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production.

Thank you again,

Ator
  • Attachment: example.txt
    (Size: 3.80KB, Downloaded 2414 times)

Report message to a moderator

Re: Import XML file into XMLType Column [message #675597 is a reply to message #675592] Mon, 08 April 2019 07:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

It works for me with EE:
SQL> select xmltype(bfilename('DATA_PUMP_DIR','example.txt'),nls_charset_id('WE8MSWIN1252')) from dual;
XMLTYPE(BFILENAME('DATA_PUMP_DIR','EXAMPLE.TXT'),NLS_CHARSET_ID('WE8MSWIN1252'))
------------------------------------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="utf-8"?>
<document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http
://www.eschkg.ch/schema/2.1">
  <envelope>
    <transactionInfo>
      <version>2.1.01</version>
      <usage>production</usage>
    </transactionInfo>
    <sender>
      <dateSent>2019-04-04</dateSent>
      <senderId>aaa</senderId>
      <senderName>aaa</senderName>
      <senderContact>aaa</senderContact>
      <senderPhone>aaa</senderPhone>
      <senderFax>aaa</senderFax>
    </sender>
    <receiver>
      <receiverId>aaa</receiverId>
    </receiver>
  </envelope>
  <SC>
    <receiverRefData>aaa</receiverRefData>
    <caseNumber>aaa</caseNumber>
    <numIssued>1</numIssued>
    <creditor>
      <principal>
        <particular>
          <company>
            <name>aaa</name>
            <contactPerson />
          </company>
        </particular>
        <address>
          <zip>aaa</zip>
          <city>aaa</city>
          <country>aaa</country>
        </address>
      </principal>
      <representative>
        <particular>
          <company>
            <name>aaa</name>
            <nameAddon>aaa</nameAddon>
            <contactPerson />
          </company>
        </particular>
        <address>
          <street1>aaa</street1>
          <buildingNo>aa</buildingNo>
          <zip>aa</zip>
          <city>aa</city>
          <country>aa</country>
        </address>
      </representative>
    </creditor>
    <debtor>
      <principal>
        <particular>
          <physicalPerson>
            <gender>F</gender>
            <lastName>aa</lastName>
            <firstNames>aa</firstNames>
            <dateOfBirth>aa</dateOfBirth>
          </physicalPerson>
        </particular>
        <address>
          <street1>aa</street1>
          <buildingNo>aa</buildingNo>
          <zip>aa</zip>
          <city>aa</city>
          <country>CH</country>
        </address>
        <actorId>aa</actorId>
        <actorIdOffice>aa</actorIdOffice>
      </principal>
    </debtor>
    <officeData>
      <officeId>1</officeId>
      <officeName>aa</officeName>
      <officeAddress>
        <street1>aa</street1>
        <buildingNo>15</buildingNo>
        <poBox>aa</poBox>
        <zip>aa</zip>
        <city>aa</city>
        <country>CH</country>
      </officeAddress>
      <paymentContact>
        <bankAccount>
          <iban>aa</iban>
        </bankAccount>
      </paymentContact>
    </officeData>
    <summon>
      <claims>
        <principalClaim>
          <amount>1.00</amount>
          <reason>aa</reason>
          <interest>
            <interestRate>2.50000</interestRate>
            <interestDateFrom>aa</interestDateFrom>
          </interest>
        </principalClaim>
        <subsidiaryClaims>
          <subsidiaryClaim>
            <amount>50.00</amount>
            <reason>aa</reason>
          </subsidiaryClaim>
          <subsidiaryClaim>
            <amount>9.20</amount>
            <reason>aa</reason>
          </subsidiaryClaim>
        </subsidiaryClaims>
      </claims>
      <addressedToId>aa</addressedToId>
      <dateIssued>aa</dateIssued>
      <delivery>
        <deliveryDate>2019-03-11</deliveryDate>
        <deliveredBy>aa</deliveredBy>
        <alternativeRecipient>aa</alternativeRecipient>
      </delivery>
    </summon>
    <statusInfo>
      <status>0</status>
      <since>2019-03-21</since>
      <details />
    </statusInfo>
    <charges>0.00</charges>
    <externalDocuments>
      <externalDocument>
        <documentTitle>aa</documentTitle>
        <canonicalName>aa.pdf</canonicalName>
        <mimetype>application/pdf</mimetype>
      </externalDocument>
    </externalDocuments>
  </SC>
</document>

1 row selected.

SQL> @v

Oracle version: 12.2.0.1.181016 EE
You have to show us as I did.

Report message to a moderator

Re: Import XML file into XMLType Column [message #675601 is a reply to message #675597] Mon, 08 April 2019 11:14 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Can confirm @Michel with 12.1.0.2 SE:
CREATE TABLE tab_x (XMLTYPE_COLUMN xmltype);

INSERT INTO tab_x VALUES (XMLTYPE('<tag/>'));

UPDATE TAB_X
   SET XMLTYPE_COLUMN = XMLType(bfilename('EXPORT_DIR', 'example.txt'), nls_charset_id('AL32UTF8'));
   
SELECT XMLQuery('$X//document/envelope/transactionInfo/version/text()' PASSING XMLTYPE_COLUMN AS x RETURNING CONTENT).getstringval() xv
  FROM TAB_X; 


XV                                                                              
--------------------------------------------------------------------------------
2.1.01      
                                                                    
1 row selected.

[Updated on: Mon, 08 April 2019 11:16]

Report message to a moderator

Re: Import XML file into XMLType Column [message #675606 is a reply to message #675554] Tue, 09 April 2019 08:28 Go to previous messageGo to next message
ator
Messages: 43
Registered: March 2009
Member
Hello,

thank you again for your help. I apologize but i found the cause of my error.
On the table I have a trigger that look for data inside the xml file and retrive the information and that was the cause of the error ORA-30625.

I didn't found earlier because, as always, oracle works in misterious way and i supposed that 
select Extract(XMLType(bfilename(gen_pak.FUN_Oracle_Dir('INBOX'), '4-479496-0_SC_4efafea4-7f85-4e71-94ac-eff88c34aca5.xml'),
                                                 nls_charset_id('AL32UTF8'))
                                                 ,'/document[1]/SC[1]/creditor[1]/representative[1]/particular[1]/company[1]/contactPerson'||'/text()','xmlns="http://www.eschkg.ch/schema/2.1"').GetStringVal()
from dual;

behaved in the same way as this 
declare
    appo_file xmltype;
    val varchar2(1000);
begin

    appo_file := XMLType(bfilename(gen_pak.FUN_Oracle_Dir('INBOX'), '4-479496-0_SC_4efafea4-7f85-4e71-94ac-eff88c34aca5.xml'),
                                                 nls_charset_id('AL32UTF8'));
    val := Appo_File.Extract('/document[1]/SC[1]/creditor[1]/representative[1]/particular[1]/company[1]/contactPerson'||'/text()','xmlns="http://www.eschkg.ch/schema/2.1"').GetStringVal();                                                  
end;

Unfortunatly the first return null and the second the exception.

I thank everyone again for your help.

Ator

Report message to a moderator

Re: Import XML file into XMLType Column [message #675609 is a reply to message #675606] Tue, 09 April 2019 09:11 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Thanks for giving feedback with your solution, but consider that EXTRACT is deprecated since ORACLE 11. Better use XLMQuery, XMLTable instead.

Report message to a moderator

Re: Import XML file into XMLType Column [message #675610 is a reply to message #675609] Tue, 09 April 2019 10:20 Go to previous message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

I back _jum to express our thanks for your feedback.

Report message to a moderator

Previous Topic: View my own queries
Next Topic: running balance query
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Apr 26 16:24:20 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.