Home » Developer & Programmer » JDeveloper, Java & XML » XML Parsing in PL/SQL without any DBMS_XML pacakges (9.1)  () 1 Vote
XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557799] Fri, 15 June 2012 16:08 Go to next message
raghx
Messages: 35
Registered: March 2006
Location: Bangalore
Member
Hi All,
Given a scenario that i dont have access to any Oracle XML related pacakges, however I have to pull the data from the following xml.
Eg: The alternativeIdentifier.Name should be a column and the value should be the data for the column. Same applies to other tags under characteristics tag.

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
    xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
    xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
    xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"
   xmlns="[url]http://application/StatusNotification[/url]">
<SOAP-ENV:Body>
<StatusNotification>
    <StatusNotificationMessage>
        <sourceSystem>
            <sourceSystemType>Somewhere</sourceSystemType>
            <sourceSystemId>MySystem</sourceSystemId>
        </sourceSystem>
        <alternativeIdentifiers>
            <alternativeIdentifier>
                <name>employeeNumber</name>
                <value>1234567890</value>
            </alternativeIdentifier>
            <alternativeIdentifier>
                <name>departmentNumber</name>
                <value>12345678</value>
            </alternativeIdentifier>
        </alternativeIdentifiers>
        <characteristics>
            <characteristic>
                <name>swipeDateTime</name>
                <value>20120613123625</value>
            </characteristic>
            <characteristic>
                <name>role</name>
                <value>MAN</value>
            </characteristic>
        </characteristics>
    </StatusNotificationMessage>
</StatusNotification>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope> 


Please help


[mod-edit: code tags added by bb]

[Updated on: Sat, 16 June 2012 13:18] by Moderator

Report message to a moderator

Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557802 is a reply to message #557799] Fri, 15 June 2012 17:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Given a scenario that i dont have access to any Oracle XML related pacakges,
>however I have to pull the data from the following xml.
Now that you have ruled out the easy solution, please share with us to what exactly you do have access.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557826 is a reply to message #557802] Sat, 16 June 2012 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
... and post the result you want from the data you gave.

Regards
Michel
Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557870 is a reply to message #557826] Sat, 16 June 2012 12:42 Go to previous messageGo to next message
raghx
Messages: 35
Registered: March 2006
Location: Bangalore
Member
The result should be as
1) There is a staging table with columns (employeeNumber, departmentNumber, swipeDateTime, role) the values should be inserted into this table
----------------------------------------------------------
employeeNumber|departmentNumber|swipeDateTime |role
----------------------------------------------------------
1234567890 |12345678 |13/06/2012 12:36:25|MAN
----------------------------------------------------------
Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557872 is a reply to message #557870] Sat, 16 June 2012 13:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can do it using INSTR and SUBSTR along with CASE.

Regards
Michel
Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557878 is a reply to message #557872] Sat, 16 June 2012 14:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You said that you do not have access to Oracle XML related packages, such as DBMS_XML. Why is that? Do you have access to functions like EXTRACT, and XMLSEQUENCE, and EXTRACT_VALUE? If so, then you can do something like below.

SCOTT@orcl_11gR2> CREATE TABLE staging
  2    ("employeeNumber"    NUMBER,
  3  	"departmentNumber"  NUMBER,
  4  	"swipeDateTime"     DATE,
  5  	"role"		    VARCHAR2(4))
  6  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO staging
  2    ("employeeNumber", "departmentNumber", "swipeDateTime", "role")
  3  WITH
  4    xml_data AS
  5  	 (SELECT XMLTYPE
  6  		   ('<?xml version="1.0" encoding="UTF-8"?>
  7  		       <SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
  8  					  xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
  9  					  xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
 10  					  xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"
 11  					  xmlns="[url]http://application/StatusNotification[/url]">
 12  			 <SOAP-ENV:Body>
 13  			   <StatusNotification>
 14  			     <StatusNotificationMessage>
 15  			       <sourceSystem>
 16  				 <sourceSystemType>Somewhere</sourceSystemType>
 17  				 <sourceSystemId>MySystem</sourceSystemId>
 18  			       </sourceSystem>
 19  			       <alternativeIdentifiers>
 20  				 <alternativeIdentifier>
 21  				   <name>employeeNumber</name>
 22  				   <value>1234567890</value>
 23  				 </alternativeIdentifier>
 24  				 <alternativeIdentifier>
 25  				   <name>departmentNumber</name>
 26  				   <value>12345678</value>
 27  				 </alternativeIdentifier>
 28  			       </alternativeIdentifiers>
 29  			       <characteristics>
 30  				 <characteristic>
 31  				   <name>swipeDateTime</name>
 32  				   <value>20120613123625</value>
 33  				 </characteristic>
 34  				 <characteristic>
 35  				   <name>role</name>
 36  				   <value>MAN</value>
 37  				 </characteristic>
 38  			       </characteristics>
 39  			     </StatusNotificationMessage>
 40  			   </StatusNotification>
 41  			 </SOAP-ENV:Body>
 42  		       </SOAP-ENV:Envelope>') xml_col
 43  	  FROM	 DUAL),
 44    alternativeIdentifiers AS
 45  	 (SELECT EXTRACTVALUE (VALUE (x), '//name',
 46  		   'xmlns="[url]http://application/StatusNotification[/url]"') name,
 47  		 EXTRACTVALUE (VALUE (x), '//value',
 48  		   'xmlns="[url]http://application/StatusNotification[/url]"') value
 49  	  FROM	 xml_data t,
 50  		 TABLE
 51  		   (XMLSEQUENCE
 52  		     (EXTRACT
 53  		       (t.xml_col,
 54  			'//alternativeIdentifier',
 55  			'xmlns="[url]http://application/StatusNotification[/url]"'))) x),
 56    characteristics AS
 57  	 (SELECT EXTRACTVALUE (VALUE (y), '//name',
 58  		   'xmlns="[url]http://application/StatusNotification[/url]"') name,
 59  		 EXTRACTVALUE (VALUE (y), '//value',
 60  		   'xmlns="[url]http://application/StatusNotification[/url]"') value
 61  	  FROM	 xml_data t,
 62  		 TABLE
 63  		   (XMLSEQUENCE
 64  		     (EXTRACT
 65  		       (t.xml_col,
 66  			'//characteristic',
 67  			'xmlns="[url]http://application/StatusNotification[/url]"'))) y),
 68    all_names_and_values AS
 69  	 (SELECT * FROM alternativeidentifiers
 70  	  UNION ALL
 71  	  SELECT * FROM characteristics)
 72  SELECT MAX (DECODE (name, 'employeeNumber',   value)),
 73  	    MAX (DECODE (name, 'departmentNumber', value)),
 74  	    MAX (DECODE (name, 'swipeDateTime',    TO_DATE (value, 'YYYYMMDDHH24MISS'))),
 75  	    MAX (DECODE (name, 'role',		   value))
 76  FROM   all_names_and_values
 77  /

1 row created.

SCOTT@orcl_11gR2> SELECT * FROM staging
  2  /

employeeNumber departmentNumber swipeDate role
-------------- ---------------- --------- ----
    1234567890         12345678 13-JUN-12 MAN

1 row selected.

Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557887 is a reply to message #557878] Sun, 17 June 2012 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe 9.1 actually means 9.0.1 and XML% and EXTRACT_VALUE SQL functions do not exist.

Regards
Michel
Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557889 is a reply to message #557887] Sun, 17 June 2012 02:31 Go to previous messageGo to next message
raghx
Messages: 35
Registered: March 2006
Location: Bangalore
Member
I have access to EXTRACT, and XMLSEQUENCE, and EXTRACT_VALUE. What I mention about no access are for below one. Since XDB is not installed on our server, i have requested my Sysdba which would be unlikely due to business reasons however I have a challege to provide solution to this change, its inevitable yet should be made Sad
1. PL/SQL DOM API for XMLType (DBMS_XMLDOM)
2. PL/SQL Parser API for XMLType (DBMS_XMLPARSER)
3. PL/SQL XSLT Processor for XMLType (DBMS_XSLPROCESSOR)

I'll try this code and get back to you guys, my apologies about the version info. Below is my oracle version:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production

TNS for Solaris: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557891 is a reply to message #557889] Sun, 17 June 2012 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Shoud be better if you posted this at the beginning. Remember it for your next questions and always do it.

Regards
Michel
Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557900 is a reply to message #557891] Sun, 17 June 2012 08:44 Go to previous messageGo to next message
raghx
Messages: 35
Registered: March 2006
Location: Bangalore
Member
Getting ORA-22905: cannot access rows from a non-nested table item
Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557902 is a reply to message #557900] Sun, 17 June 2012 10:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Please post a copy and paste of a run of everything you did from SQL*Plus, in the same manner that I did, including line numbers and error message.

[Updated on: Sun, 17 June 2012 10:41]

Report message to a moderator

Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557904 is a reply to message #557902] Sun, 17 June 2012 11:25 Go to previous messageGo to next message
raghx
Messages: 35
Registered: March 2006
Location: Bangalore
Member
SQL> CREATE TABLE staging
  2        ("employeeNumber"    NUMBER,
  3       "departmentNumber"  NUMBER,
  4       "swipeDateTime"     DATE,
  5       "role"      VARCHAR2(4))
  6   /
 
SQL> INSERT INTO staging
  2        ("employeeNumber", "departmentNumber", "swipeDateTime", "role")
  3      WITH
  4        xml_data AS
  5        (SELECT XMLTYPE
  6           ('<?xml version="1.0" encoding="UTF-8"?>
  7               <SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
  8             xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
  9             xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
 10            xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"
 11            xmlns="[url]http://application/StatusNotification[/url]">
 12         <SOAP-ENV:Body>
 13           <StatusNotification>
 14             <StatusNotificationMessage>
 15               <sourceSystem>
 16          <sourceSystemType>Somewhere</sourceSystemType>
 17          <sourceSystemId>MySystem</sourceSystemId>
 18               </sourceSystem>
 19               <alternativeIdentifiers>
 20          <alternativeIdentifier>
 21            <name>employeeNumber</name>
 22            <value>1234567890</value>
 23          </alternativeIdentifier>
 24          <alternativeIdentifier>
 25            <name>departmentNumber</name>
 26            <value>12345678</value>
 27          </alternativeIdentifier>
 28               </alternativeIdentifiers>
 29               <characteristics>
 30          <characteristic>
 31            <name>swipeDateTime</name>
 32            <value>20120613123625</value>
 33          </characteristic>
 34          <characteristic>
 35            <name>role</name>
 36            <value>MAN</value>
 37          </characteristic>
 38               </characteristics>
 39             </StatusNotificationMessage>
 40           </StatusNotification>
 41         </SOAP-ENV:Body>
 42              </SOAP-ENV:Envelope>') xml_col
 43        FROM  DUAL),
 44       alternativeIdentifiers AS
 45       (SELECT EXTRACTVALUE (VALUE (x), '//name',
 46          'xmlns="[url]http://application/StatusNotification[/url]"') name,
 47        EXTRACTVALUE (VALUE (x), '//value',
 48          'xmlns="[url]http://application/StatusNotification[/url]"') value
 49        FROM  xml_data t,
 50        TABLE
 51          (XMLSEQUENCE
 52            (EXTRACT
 53              (t.xml_col,
 54        '//alternativeIdentifier',
 55        'xmlns="[url]http://application/StatusNotification[/url]"'))) x),
 56       characteristics AS
 57       (SELECT EXTRACTVALUE (VALUE (y), '//name',
 58          'xmlns="[url]http://application/StatusNotification[/url]"') name,
 59        EXTRACTVALUE (VALUE (y), '//value',
 60          'xmlns="[url]http://application/StatusNotification[/url]"') value
 61        FROM  xml_data t,
 62        TABLE
 63          (XMLSEQUENCE
 64            (EXTRACT
 65              (t.xml_col,
 66        '//characteristic',
 67        'xmlns="[url]http://application/StatusNotification[/url]"'))) y),
 68       all_names_and_values AS
 69       (SELECT * FROM alternativeidentifiers
 70        UNION ALL
 71        SELECT * FROM characteristics)
 72     SELECT MAX (DECODE (name, 'employeeNumber',   value)),
 73          MAX (DECODE (name, 'departmentNumber', value)),
 74          MAX (DECODE (name, 'swipeDateTime',    TO_DATE (value, 'YYYYMMDDHH24MISS'))),
 75          MAX (DECODE (name, 'role',     value))
 76     FROM   all_names_and_values
 77   /
           xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
                                *
ERROR at line 9:
ORA-22905: cannot access rows from a non-nested table item 

[Updated on: Sun, 17 June 2012 11:27]

Report message to a moderator

Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557906 is a reply to message #557904] Sun, 17 June 2012 12:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Since it works for me and not for you, I have to assume that it is due to differences in our systems, probably the limitations of your outdated version. You should upgrade to a currently supported version if you can. Since I cannot reproduce the problem on my system, I will have to rely on your to do any testing. In order to narrow down the cause of the problem, so that we can try to find a workaround, please try running the following query that eliminates the namespace and soap stuff from the data and query and let us know if that inserts the data or produces an error.

INSERT INTO staging 
  ("employeeNumber", "departmentNumber", "swipeDateTime", "role")
WITH 
  xml_data AS
    (SELECT XMLTYPE 
              ('<?xml version="1.0" encoding="UTF-8"?>
                      <StatusNotification>
                        <StatusNotificationMessage>
                          <sourceSystem>
                            <sourceSystemType>Somewhere</sourceSystemType>
                            <sourceSystemId>MySystem</sourceSystemId>
                          </sourceSystem>
                          <alternativeIdentifiers>
                            <alternativeIdentifier>
                              <name>employeeNumber</name>
                              <value>1234567890</value>
                            </alternativeIdentifier>
                            <alternativeIdentifier>
                              <name>departmentNumber</name>
                              <value>12345678</value>
                            </alternativeIdentifier>
                          </alternativeIdentifiers>
                          <characteristics>
                            <characteristic>
                              <name>swipeDateTime</name>
                              <value>20120613123625</value>
                            </characteristic>
                            <characteristic>
                              <name>role</name>
                              <value>MAN</value>
                            </characteristic>
                          </characteristics>
                        </StatusNotificationMessage>
                      </StatusNotification>') xml_col
     FROM   DUAL),
  alternativeIdentifiers AS
    (SELECT EXTRACTVALUE (VALUE (x), '//name') name,
            EXTRACTVALUE (VALUE (x), '//value') value
     FROM   xml_data t,
            TABLE 
              (XMLSEQUENCE 
                (EXTRACT 
                  (t.xml_col, 
                   '//alternativeIdentifier'))) x),
  characteristics AS
    (SELECT EXTRACTVALUE (VALUE (y), '//name') name,
            EXTRACTVALUE (VALUE (y), '//value') value
     FROM   xml_data t,
            TABLE 
              (XMLSEQUENCE 
                (EXTRACT 
                  (t.xml_col, 
                   '//characteristic'))) y),
  all_names_and_values AS
    (SELECT * FROM alternativeidentifiers
     UNION ALL
     SELECT * FROM characteristics)
SELECT MAX (DECODE (name, 'employeeNumber',   value)),
       MAX (DECODE (name, 'departmentNumber', value)),
       MAX (DECODE (name, 'swipeDateTime',    TO_DATE (value, 'YYYYMMDDHH24MISS'))),
       MAX (DECODE (name, 'role',             value))
FROM   all_names_and_values
/

Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557907 is a reply to message #557906] Sun, 17 June 2012 12:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I found a reference to Oracle bug 4187886, that was fixed in Oracle version 10.2.0.1, that produces the ORA-22905 error and seems to suggest that the following alternative syntax may work in prior versions. It also works on my system. Please test it on yours and let us know.

INSERT INTO staging 
  ("employeeNumber", "departmentNumber", "swipeDateTime", "role")
SELECT MAX (DECODE (name, 'employeeNumber',   value)),
       MAX (DECODE (name, 'departmentNumber', value)),
       MAX (DECODE (name, 'swipeDateTime',    TO_DATE (value, 'YYYYMMDDHH24MISS'))),
       MAX (DECODE (name, 'role',             value))
FROM  (SELECT * 
       FROM   (SELECT EXTRACTVALUE (VALUE (x), '//name', 
                        'xmlns="[url]http://application/StatusNotification[/url]"') name,
                      EXTRACTVALUE (VALUE (x), '//value', 
                        'xmlns="[url]http://application/StatusNotification[/url]"') value
               FROM   TABLE 
                        (XMLSEQUENCE 
                          (EXTRACT 
                            (XMLTYPE 
              ('<?xml version="1.0" encoding="UTF-8"?>
                  <SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
                                     xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
                                     xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
                                     xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"
                                     xmlns="[url]http://application/StatusNotification[/url]">
                    <SOAP-ENV:Body>                  
                      <StatusNotification>
                        <StatusNotificationMessage>
                          <sourceSystem>
                            <sourceSystemType>Somewhere</sourceSystemType>
                            <sourceSystemId>MySystem</sourceSystemId>
                          </sourceSystem>
                          <alternativeIdentifiers>
                            <alternativeIdentifier>
                              <name>employeeNumber</name>
                              <value>1234567890</value>
                            </alternativeIdentifier>
                            <alternativeIdentifier>
                              <name>departmentNumber</name>
                              <value>12345678</value>
                            </alternativeIdentifier>
                          </alternativeIdentifiers>
                          <characteristics>
                            <characteristic>
                              <name>swipeDateTime</name>
                              <value>20120613123625</value>
                            </characteristic>
                            <characteristic>
                              <name>role</name>
                              <value>MAN</value>
                            </characteristic>
                          </characteristics>
                        </StatusNotificationMessage>
                      </StatusNotification>
                    </SOAP-ENV:Body>
                  </SOAP-ENV:Envelope>'), 
                             '//alternativeIdentifier',
                             'xmlns="[url]http://application/StatusNotification[/url]"'))) x)
               UNION ALL
               SELECT * 
               FROM   (SELECT EXTRACTVALUE (VALUE (y), '//name', 
                                'xmlns="[url]http://application/StatusNotification[/url]"') name,
                              EXTRACTVALUE (VALUE (y), '//value', 
                                'xmlns="[url]http://application/StatusNotification[/url]"') value
                       FROM   TABLE 
                                (XMLSEQUENCE 
                                  (EXTRACT 
                                    (XMLTYPE 
              ('<?xml version="1.0" encoding="UTF-8"?>
                  <SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
                                     xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
                                     xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
                                     xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"
                                     xmlns="[url]http://application/StatusNotification[/url]">
                    <SOAP-ENV:Body>                  
                      <StatusNotification>
                        <StatusNotificationMessage>
                          <sourceSystem>
                            <sourceSystemType>Somewhere</sourceSystemType>
                            <sourceSystemId>MySystem</sourceSystemId>
                          </sourceSystem>
                          <alternativeIdentifiers>
                            <alternativeIdentifier>
                              <name>employeeNumber</name>
                              <value>1234567890</value>
                            </alternativeIdentifier>
                            <alternativeIdentifier>
                              <name>departmentNumber</name>
                              <value>12345678</value>
                            </alternativeIdentifier>
                          </alternativeIdentifiers>
                          <characteristics>
                            <characteristic>
                              <name>swipeDateTime</name>
                              <value>20120613123625</value>
                            </characteristic>
                            <characteristic>
                              <name>role</name>
                              <value>MAN</value>
                            </characteristic>
                          </characteristics>
                        </StatusNotificationMessage>
                      </StatusNotification>
                    </SOAP-ENV:Body>
                  </SOAP-ENV:Envelope>'), 
                                     '//characteristic', 
                                     'xmlns="[url]http://application/StatusNotification[/url]"'))) y))
/

Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557910 is a reply to message #557907] Sun, 17 June 2012 13:29 Go to previous messageGo to next message
raghx
Messages: 35
Registered: March 2006
Location: Bangalore
Member
Thanks Barbara,
This time i got the following:

'xmlns="http://application/StatusNotification"') value
*
ERROR at line 11:
ORA-22905: cannot access rows from a non-nested table item
Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557911 is a reply to message #557910] Sun, 17 June 2012 13:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
What about the query without the namespaces and soap stuff? Did that one insert the data or raise an error?

Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557912 is a reply to message #557911] Sun, 17 June 2012 13:39 Go to previous messageGo to next message
raghx
Messages: 35
Registered: March 2006
Location: Bangalore
Member
Same error but on other place...

SQL> INSERT INTO staging
  2    ("employeeNumber", "departmentNumber", "swipeDateTime", "role")
  3  WITH
  4    xml_data AS
  5      (SELECT XMLTYPE
  6                ('<?xml version="1.0" encoding="UTF-8"?>
  7                        <StatusNotification>
  8                          <StatusNotificationMessage>
  9                            <sourceSystem>
 10                              <sourceSystemType>Somewhere</sourceSystemType>
 11                              <sourceSystemId>MySystem</sourceSystemId>
 12                            </sourceSystem>
 13                            <alternativeIdentifiers>
 14                              <alternativeIdentifier>
 15                                <name>employeeNumber</name>
 16                                <value>1234567890</value>
 17                              </alternativeIdentifier>
 18                              <alternativeIdentifier>
 19                                <name>departmentNumber</name>
 20                                <value>12345678</value>
 21                              </alternativeIdentifier>
 22                            </alternativeIdentifiers>
 23                            <characteristics>
 24                              <characteristic>
 25                                <name>swipeDateTime</name>
 26                                <value>20120613123625</value>
 27                              </characteristic>
 28                              <characteristic>
 29                                <name>role</name>
 30                                <value>MAN</value>
 31                              </characteristic>
 32                            </characteristics>
 33                          </StatusNotificationMessage>
 34                        </StatusNotification>') xml_col
 35       FROM   DUAL),
 36    alternativeIdentifiers AS
 37      (SELECT EXTRACTVALUE (VALUE (x), '//name') name,
 38              EXTRACTVALUE (VALUE (x), '//value') value
 39       FROM   xml_data t,
 40              TABLE
 41                (XMLSEQUENCE
 42                  (EXTRACT
 43                    (t.xml_col,
 44                     '//alternativeIdentifier'))) x),
 45    characteristics AS
 46      (SELECT EXTRACTVALUE (VALUE (y), '//name') name,
 47              EXTRACTVALUE (VALUE (y), '//value') value
 48       FROM   xml_data t,
 49              TABLE
 50                (XMLSEQUENCE
 51                  (EXTRACT
 52                    (t.xml_col,
 53                     '//characteristic'))) y),
 54    all_names_and_values AS
 55      (SELECT * FROM alternativeidentifiers
 56       UNION ALL
 57       SELECT * FROM characteristics)
 58  SELECT MAX (DECODE (name, 'employeeNumber',   value)),
 59         MAX (DECODE (name, 'departmentNumber', value)),
 60         MAX (DECODE (name, 'swipeDateTime',    TO_DATE (value, 'YYYYMMDDHH24MISS'))),
 61         MAX (DECODE (name, 'role',             value))
 62  FROM   all_names_and_values
 63  /
                            <sourceSystemType>Somewhere</sourceSystemType>
                                        *
ERROR at line 10:
ORA-22905: cannot access rows from a non-nested table item 

SQL> 

Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557913 is a reply to message #557912] Sun, 17 June 2012 13:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Can you run any of the select statements without the insert?
Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557914 is a reply to message #557913] Sun, 17 June 2012 13:48 Go to previous messageGo to next message
raghx
Messages: 35
Registered: March 2006
Location: Bangalore
Member
SQL> SELECT XMLTYPE
  2                    ('<?xml version="1.0" encoding="UTF-8"?>
  3                            <StatusNotification>
  4                              <StatusNotificationMessage>
  5                                <sourceSystem>
  6                                 <sourceSystemType>Somewhere</sourceSystemType>
  7                                 <sourceSystemId>MySystem</sourceSystemId>
  8                               </sourceSystem>
  9                               <alternativeIdentifiers>
 10                                 <alternativeIdentifier>
 11                                   <name>employeeNumber</name>
 12                                   <value>1234567890</value>
 13                                 </alternativeIdentifier>
 14                                 <alternativeIdentifier>
 15                                   <name>departmentNumber</name>
 16                                   <value>12345678</value>
 17                                 </alternativeIdentifier>
 18                               </alternativeIdentifiers>
 19                               <characteristics>
 20                                 <characteristic>
 21                                   <name>swipeDateTime</name>
 22                                   <value>20120613123625</value>
 23                                 </characteristic>
 24                                 <characteristic>
 25                                   <name>role</name>
 26                                   <value>MAN</value>
 27                                 </characteristic>
 28                               </characteristics>
 29                             </StatusNotificationMessage>
 30                           </StatusNotification>') xml_col
 31          FROM   DUAL),
 32       alternativeIdentifiers AS
 33         (SELECT EXTRACTVALUE (VALUE (x), '//name') name,
 34                 EXTRACTVALUE (VALUE (x), '//value') value
 35          FROM   xml_data t,
 36                 TABLE
 37                   (XMLSEQUENCE
 38                     (EXTRACT
 39                       (t.xml_col,
 40                        '//alternativeIdentifier'))) x),
 41       characteristics AS
 42         (SELECT EXTRACTVALUE (VALUE (y), '//name') name,
 43                 EXTRACTVALUE (VALUE (y), '//value') value
 44          FROM   xml_data t,
 45                 TABLE
 46                   (XMLSEQUENCE
 47                     (EXTRACT
 48                       (t.xml_col,
 49                        '//characteristic'))) y),
 50       all_names_and_values AS
 51         (SELECT * FROM alternativeidentifiers
 52          UNION ALL
 53          SELECT * FROM characteristics)
 54     SELECT MAX (DECODE (name, 'employeeNumber',   value)),
 55            MAX (DECODE (name, 'departmentNumber', value)),
 56            MAX (DECODE (name, 'swipeDateTime',    TO_DATE (value, 'YYYYMMDDHH24MISS'))),
 57            MAX (DECODE (name, 'role',             value))
 58     FROM   all_names_and_values
 59  /
                          <StatusNotification>
*
ERROR at line 3:
ORA-00933: SQL command not properly ended 
Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557915 is a reply to message #557914] Sun, 17 June 2012 13:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You left out the first with clause, but please try this one. If the whole insert and select does not work, then please try just the select.

INSERT INTO staging 
  ("employeeNumber", "departmentNumber", "swipeDateTime", "role")
SELECT MAX (DECODE (name, 'employeeNumber',   value)),
       MAX (DECODE (name, 'departmentNumber', value)),
       MAX (DECODE (name, 'swipeDateTime',    TO_DATE (value, 'YYYYMMDDHH24MISS'))),
       MAX (DECODE (name, 'role',             value))
FROM  (SELECT * 
       FROM   (SELECT EXTRACTVALUE (x.t_val, '//name', 
                        'xmlns="[url]http://application/StatusNotification[/url]"') name,
                      EXTRACTVALUE (x.t_val, '//value', 
                        'xmlns="[url]http://application/StatusNotification[/url]"') value
               FROM   (SELECT VALUE (t) t_val
                       FROM
                       TABLE 
                        (XMLSEQUENCE 
                          (EXTRACT 
                            (XMLTYPE 
              ('<?xml version="1.0" encoding="UTF-8"?>
                  <SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
                                     xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
                                     xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
                                     xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"
                                     xmlns="[url]http://application/StatusNotification[/url]">
                    <SOAP-ENV:Body>                  
                      <StatusNotification>
                        <StatusNotificationMessage>
                          <sourceSystem>
                            <sourceSystemType>Somewhere</sourceSystemType>
                            <sourceSystemId>MySystem</sourceSystemId>
                          </sourceSystem>
                          <alternativeIdentifiers>
                            <alternativeIdentifier>
                              <name>employeeNumber</name>
                              <value>1234567890</value>
                            </alternativeIdentifier>
                            <alternativeIdentifier>
                              <name>departmentNumber</name>
                              <value>12345678</value>
                            </alternativeIdentifier>
                          </alternativeIdentifiers>
                          <characteristics>
                            <characteristic>
                              <name>swipeDateTime</name>
                              <value>20120613123625</value>
                            </characteristic>
                            <characteristic>
                              <name>role</name>
                              <value>MAN</value>
                            </characteristic>
                          </characteristics>
                        </StatusNotificationMessage>
                      </StatusNotification>
                    </SOAP-ENV:Body>
                  </SOAP-ENV:Envelope>'), 
                             '//alternativeIdentifier',
                             'xmlns="[url]http://application/StatusNotification[/url]"'))) t) x) 
               UNION ALL
               SELECT * 
               FROM   (SELECT EXTRACTVALUE (y.t_val, '//name', 
                                'xmlns="[url]http://application/StatusNotification[/url]"') name,
                              EXTRACTVALUE (y.t_val, '//value', 
                                'xmlns="[url]http://application/StatusNotification[/url]"') value
                       FROM   (SELECT VALUE (t) t_val
                               FROM   TABLE 
                                (XMLSEQUENCE 
                                  (EXTRACT 
                                    (XMLTYPE 
              ('<?xml version="1.0" encoding="UTF-8"?>
                  <SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
                                     xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
                                     xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
                                     xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"
                                     xmlns="[url]http://application/StatusNotification[/url]">
                    <SOAP-ENV:Body>                  
                      <StatusNotification>
                        <StatusNotificationMessage>
                          <sourceSystem>
                            <sourceSystemType>Somewhere</sourceSystemType>
                            <sourceSystemId>MySystem</sourceSystemId>
                          </sourceSystem>
                          <alternativeIdentifiers>
                            <alternativeIdentifier>
                              <name>employeeNumber</name>
                              <value>1234567890</value>
                            </alternativeIdentifier>
                            <alternativeIdentifier>
                              <name>departmentNumber</name>
                              <value>12345678</value>
                            </alternativeIdentifier>
                          </alternativeIdentifiers>
                          <characteristics>
                            <characteristic>
                              <name>swipeDateTime</name>
                              <value>20120613123625</value>
                            </characteristic>
                            <characteristic>
                              <name>role</name>
                              <value>MAN</value>
                            </characteristic>
                          </characteristics>
                        </StatusNotificationMessage>
                      </StatusNotification>
                    </SOAP-ENV:Body>
                  </SOAP-ENV:Envelope>'), 
                                     '//characteristic', 
                                     'xmlns="[url]http://application/StatusNotification[/url]"'))) t) y))
/

Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557916 is a reply to message #557915] Sun, 17 June 2012 14:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Here is another one for you to test as an insert and if that fails, then just the select. All of these that I am providing work on my system. I am trying to eliminate things that might cause the problem associated with the bug in your version.

INSERT INTO staging 
  ("employeeNumber", "departmentNumber", "swipeDateTime", "role")
SELECT MAX (DECODE (name, 'employeeNumber',   value)),
       MAX (DECODE (name, 'departmentNumber', value)),
       MAX (DECODE (name, 'swipeDateTime',    TO_DATE (value, 'YYYYMMDDHH24MISS'))),
       MAX (DECODE (name, 'role',             value))
FROM  (SELECT EXTRACTVALUE (x.t_val, '//name', 
                'xmlns="[url]http://application/StatusNotification[/url]"') name,
              EXTRACTVALUE (x.t_val, '//value', 
                 'xmlns="[url]http://application/StatusNotification[/url]"') value
       FROM   (SELECT VALUE (t) t_val
               FROM   TABLE 
                        (XMLSEQUENCE 
                          (EXTRACT 
                            (XMLTYPE 
              ('<?xml version="1.0" encoding="UTF-8"?>
                  <SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
                                     xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
                                     xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
                                     xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"
                                     xmlns="[url]http://application/StatusNotification[/url]">
                    <SOAP-ENV:Body>                  
                      <StatusNotification>
                        <StatusNotificationMessage>
                          <sourceSystem>
                            <sourceSystemType>Somewhere</sourceSystemType>
                            <sourceSystemId>MySystem</sourceSystemId>
                          </sourceSystem>
                          <alternativeIdentifiers>
                            <alternativeIdentifier>
                              <name>employeeNumber</name>
                              <value>1234567890</value>
                            </alternativeIdentifier>
                            <alternativeIdentifier>
                              <name>departmentNumber</name>
                              <value>12345678</value>
                            </alternativeIdentifier>
                          </alternativeIdentifiers>
                          <characteristics>
                            <characteristic>
                              <name>swipeDateTime</name>
                              <value>20120613123625</value>
                            </characteristic>
                            <characteristic>
                              <name>role</name>
                              <value>MAN</value>
                            </characteristic>
                          </characteristics>
                        </StatusNotificationMessage>
                      </StatusNotification>
                    </SOAP-ENV:Body>
                  </SOAP-ENV:Envelope>'), 
                             '//alternativeIdentifier',
                             'xmlns="[url]http://application/StatusNotification[/url]"'))) t) x
       UNION ALL
       SELECT EXTRACTVALUE (y.t_val, '//name', 
                'xmlns="[url]http://application/StatusNotification[/url]"') name,
              EXTRACTVALUE (y.t_val, '//value', 
                'xmlns="[url]http://application/StatusNotification[/url]"') value
       FROM   (SELECT VALUE (t) t_val
               FROM   TABLE 
                        (XMLSEQUENCE 
                          (EXTRACT 
                            (XMLTYPE 
              ('<?xml version="1.0" encoding="UTF-8"?>
                  <SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
                                     xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
                                     xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
                                     xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"
                                     xmlns="[url]http://application/StatusNotification[/url]">
                    <SOAP-ENV:Body>                  
                      <StatusNotification>
                        <StatusNotificationMessage>
                          <sourceSystem>
                            <sourceSystemType>Somewhere</sourceSystemType>
                            <sourceSystemId>MySystem</sourceSystemId>
                          </sourceSystem>
                          <alternativeIdentifiers>
                            <alternativeIdentifier>
                              <name>employeeNumber</name>
                              <value>1234567890</value>
                            </alternativeIdentifier>
                            <alternativeIdentifier>
                              <name>departmentNumber</name>
                              <value>12345678</value>
                            </alternativeIdentifier>
                          </alternativeIdentifiers>
                          <characteristics>
                            <characteristic>
                              <name>swipeDateTime</name>
                              <value>20120613123625</value>
                            </characteristic>
                            <characteristic>
                              <name>role</name>
                              <value>MAN</value>
                            </characteristic>
                          </characteristics>
                        </StatusNotificationMessage>
                      </StatusNotification>
                    </SOAP-ENV:Body>
                  </SOAP-ENV:Envelope>'), 
                                     '//characteristic', 
                                     'xmlns="[url]http://application/StatusNotification[/url]"'))) t) y)
/

Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557917 is a reply to message #557916] Sun, 17 June 2012 14:17 Go to previous messageGo to next message
raghx
Messages: 35
Registered: March 2006
Location: Bangalore
Member
SQL> @"C:\xmlins.sql";
SQL> INSERT INTO staging
  2    ("employeeNumber", "departmentNumber", "swipeDateTime", "role")
  3  SELECT MAX (DECODE (name, 'employeeNumber',   value)),
  4  	    MAX (DECODE (name, 'departmentNumber', value)),
  5  	    MAX (DECODE (name, 'swipeDateTime',    TO_DATE (value, 'YYYYMMDDHH24MISS'))),
  6  	    MAX (DECODE (name, 'role',		   value))
  7  FROM  (SELECT *
  8  	    FROM   (SELECT EXTRACTVALUE (x.t_val, '//name',
  9  			     'xmlns="[url]http://application/StatusNotification[/url]"') name,
 10  			   EXTRACTVALUE (x.t_val, '//value',
 11  			     'xmlns="[url]http://application/StatusNotification[/url]"') value
 12  		    FROM   (SELECT VALUE (t) t_val
 13  			    FROM
 14  			    TABLE
 15  			     (XMLSEQUENCE
 16  			       (EXTRACT
 17  				 (XMLTYPE
 18  		   ('<?xml version="1.0" encoding="UTF-8"?>
 19  		       <SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
 20  					  xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
 21  					  xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
 22  					  xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"
 23  					  xmlns="[url]http://application/StatusNotification[/url]">
 24  			 <SOAP-ENV:Body>
 25  			   <StatusNotification>
 26  			     <StatusNotificationMessage>
 27  			       <sourceSystem>
 28  				 <sourceSystemType>Somewhere</sourceSystemType>
 29  				 <sourceSystemId>MySystem</sourceSystemId>
 30  			       </sourceSystem>
 31  			       <alternativeIdentifiers>
 32  				 <alternativeIdentifier>
 33  				   <name>employeeNumber</name>
 34  				   <value>1234567890</value>
 35  				 </alternativeIdentifier>
 36  				 <alternativeIdentifier>
 37  				   <name>departmentNumber</name>
 38  				   <value>12345678</value>
 39  				 </alternativeIdentifier>
 40  			       </alternativeIdentifiers>
 41  			       <characteristics>
 42  				 <characteristic>
 43  				   <name>swipeDateTime</name>
 44  				   <value>20120613123625</value>
 45  				 </characteristic>
 46  				 <characteristic>
 47  				   <name>role</name>
 48  				   <value>MAN</value>
 49  				 </characteristic>
 50  			       </characteristics>
 51  			     </StatusNotificationMessage>
 52  			   </StatusNotification>
 53  			 </SOAP-ENV:Body>
 54  		       </SOAP-ENV:Envelope>'),
 55  				  '//alternativeIdentifier',
 56  				  'xmlns="[url]http://application/StatusNotification[/url]"'))) t) x)
 57  		    UNION ALL
 58  		    SELECT *
 59  		    FROM   (SELECT EXTRACTVALUE (y.t_val, '//name',
 60  				     'xmlns="[url]http://application/StatusNotification[/url]"') name,
 61  				   EXTRACTVALUE (y.t_val, '//value',
 62  				     'xmlns="[url]http://application/StatusNotification[/url]"') value
 63  			    FROM   (SELECT VALUE (t) t_val
 64  				    FROM   TABLE
 65  				     (XMLSEQUENCE
 66  				       (EXTRACT
 67  					 (XMLTYPE
 68  		   ('<?xml version="1.0" encoding="UTF-8"?>
 69  		       <SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
 70  					  xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
 71  					  xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
 72  					  xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"
 73  					  xmlns="[url]http://application/StatusNotification[/url]">
 74  			 <SOAP-ENV:Body>
 75  			   <StatusNotification>
 76  			     <StatusNotificationMessage>
 77  			       <sourceSystem>
 78  				 <sourceSystemType>Somewhere</sourceSystemType>
 79  				 <sourceSystemId>MySystem</sourceSystemId>
 80  			       </sourceSystem>
 81  			       <alternativeIdentifiers>
 82  				 <alternativeIdentifier>
 83  				   <name>employeeNumber</name>
 84  				   <value>1234567890</value>
 85  				 </alternativeIdentifier>
 86  				 <alternativeIdentifier>
 87  				   <name>departmentNumber</name>
 88  				   <value>12345678</value>
 89  				 </alternativeIdentifier>
 90  			       </alternativeIdentifiers>
 91  			       <characteristics>
 92  				 <characteristic>
 93  				   <name>swipeDateTime</name>
 94  				   <value>20120613123625</value>
 95  				 </characteristic>
 96  				 <characteristic>
 97  				   <name>role</name>
 98  				   <value>MAN</value>
 99  				 </characteristic>
100  			       </characteristics>
101  			     </StatusNotificationMessage>
102  			   </StatusNotification>
103  			 </SOAP-ENV:Body>
104  		       </SOAP-ENV:Envelope>'),
105  					  '//characteristic',
106  					  'xmlns="[url]http://application/StatusNotification[/url]"'))) t) y))
107  /
                        'xmlns="[url]http://application/StatusNotification[/url]"') value
                                                                                *
ERROR at line 11:
ORA-22905: cannot access rows from a non-nested table item 


SQL> spool off;
Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557918 is a reply to message #557917] Sun, 17 June 2012 14:22 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
And what about just the select, without the insert, on that last one?
Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557919 is a reply to message #557918] Sun, 17 June 2012 14:31 Go to previous messageGo to next message
raghx
Messages: 35
Registered: March 2006
Location: Bangalore
Member
SQL> @"C:\xmlins1.sql";
SQL> SELECT MAX (DECODE (name, 'employeeNumber',   value)),
  2  	    MAX (DECODE (name, 'departmentNumber', value)),
  3  	    MAX (DECODE (name, 'swipeDateTime',    TO_DATE (value, 'YYYYMMDDHH24MISS'))),
  4  	    MAX (DECODE (name, 'role',		   value))
  5  FROM  (SELECT *
  6  	    FROM   (SELECT EXTRACTVALUE (x.t_val, '//name',
  7  			     'xmlns="[url]http://application/StatusNotification[/url]"') name,
  8  			   EXTRACTVALUE (x.t_val, '//value',
  9  			     'xmlns="[url]http://application/StatusNotification[/url]"') value
 10  		    FROM   (SELECT VALUE (t) t_val
 11  			    FROM
 12  			    TABLE
 13  			     (XMLSEQUENCE
 14  			       (EXTRACT
 15  				 (XMLTYPE
 16  		   ('<?xml version="1.0" encoding="UTF-8"?>
 17  		       <SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
 18  					  xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
 19  					  xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
 20  					  xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"
 21  					  xmlns="[url]http://application/StatusNotification[/url]">
 22  			 <SOAP-ENV:Body>
 23  			   <StatusNotification>
 24  			     <StatusNotificationMessage>
 25  			       <sourceSystem>
 26  				 <sourceSystemType>Somewhere</sourceSystemType>
 27  				 <sourceSystemId>MySystem</sourceSystemId>
 28  			       </sourceSystem>
 29  			       <alternativeIdentifiers>
 30  				 <alternativeIdentifier>
 31  				   <name>employeeNumber</name>
 32  				   <value>1234567890</value>
 33  				 </alternativeIdentifier>
 34  				 <alternativeIdentifier>
 35  				   <name>departmentNumber</name>
 36  				   <value>12345678</value>
 37  				 </alternativeIdentifier>
 38  			       </alternativeIdentifiers>
 39  			       <characteristics>
 40  				 <characteristic>
 41  				   <name>swipeDateTime</name>
 42  				   <value>20120613123625</value>
 43  				 </characteristic>
 44  				 <characteristic>
 45  				   <name>role</name>
 46  				   <value>MAN</value>
 47  				 </characteristic>
 48  			       </characteristics>
 49  			     </StatusNotificationMessage>
 50  			   </StatusNotification>
 51  			 </SOAP-ENV:Body>
 52  		       </SOAP-ENV:Envelope>'),
 53  				  '//alternativeIdentifier',
 54  				  'xmlns="[url]http://application/StatusNotification[/url]"'))) t) x)
 55  		    UNION ALL
 56  		    SELECT *
 57  		    FROM   (SELECT EXTRACTVALUE (y.t_val, '//name',
 58  				     'xmlns="[url]http://application/StatusNotification[/url]"') name,
 59  				   EXTRACTVALUE (y.t_val, '//value',
 60  				     'xmlns="[url]http://application/StatusNotification[/url]"') value
 61  			    FROM   (SELECT VALUE (t) t_val
 62  				    FROM   TABLE
 63  				     (XMLSEQUENCE
 64  				       (EXTRACT
 65  					 (XMLTYPE
 66  		   ('<?xml version="1.0" encoding="UTF-8"?>
 67  		       <SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
 68  					  xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
 69  					  xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
 70  					  xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"
 71  					  xmlns="[url]http://application/StatusNotification[/url]">
 72  			 <SOAP-ENV:Body>
 73  			   <StatusNotification>
 74  			     <StatusNotificationMessage>
 75  			       <sourceSystem>
 76  				 <sourceSystemType>Somewhere</sourceSystemType>
 77  				 <sourceSystemId>MySystem</sourceSystemId>
 78  			       </sourceSystem>
 79  			       <alternativeIdentifiers>
 80  				 <alternativeIdentifier>
 81  				   <name>employeeNumber</name>
 82  				   <value>1234567890</value>
 83  				 </alternativeIdentifier>
 84  				 <alternativeIdentifier>
 85  				   <name>departmentNumber</name>
 86  				   <value>12345678</value>
 87  				 </alternativeIdentifier>
 88  			       </alternativeIdentifiers>
 89  			       <characteristics>
 90  				 <characteristic>
 91  				   <name>swipeDateTime</name>
 92  				   <value>20120613123625</value>
 93  				 </characteristic>
 94  				 <characteristic>
 95  				   <name>role</name>
 96  				   <value>MAN</value>
 97  				 </characteristic>
 98  			       </characteristics>
 99  			     </StatusNotificationMessage>
100  			   </StatusNotification>
101  			 </SOAP-ENV:Body>
102  		       </SOAP-ENV:Envelope>'),
103  					  '//characteristic',
104  					  'xmlns="[url]http://application/StatusNotification[/url]"'))) t) y))
105  /

MAX(DECODE(NAME,'EMPLOYEENUMBE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
--------------------------------
MAX(DECODE(NAME,'DEPARTMENTNUM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
--------------------------------
MAX(DECOD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
---------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
MAX(DECODE(NAME,'ROLE',VALUE))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
--------------------------------
1234567890                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
12345678                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
13-JUN-12                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
MAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

1 row selected.

SQL> spool off;
Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557920 is a reply to message #557919] Sun, 17 June 2012 14:45 Go to previous messageGo to next message
raghx
Messages: 35
Registered: March 2006
Location: Bangalore
Member
here is the result set: The earlier one is not formatted correctly
employeeNumber                                                                   departmentNumber                                                                 SwipeTimeStamp role
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------- --------------------------------------------------------------------------------
1234567890                                                                       12345678                                                                         13/06/2012 12: MAN

Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557921 is a reply to message #557919] Sun, 17 June 2012 14:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
So, the select works without the insert. I suspect that any of the selects that I posted would work without the inserts.
The bug seems to be only associated with inserting. I have tried all of the variations that I can think of to get around
the problem in SQL. The only other thing that I can think of is to assign the values to PL/SQL variables, then try to
insert those. Please try the method below.

DECLARE
  v_empnum             NUMBER;
  v_deptnum            NUMBER;
  v_swipe              DATE;
  v_role               VARCHAR2(4);
BEGIN
  SELECT MAX (DECODE (name, 'employeeNumber',   value)),
         MAX (DECODE (name, 'departmentNumber', value)),
         MAX (DECODE (name, 'swipeDateTime',    TO_DATE (value, 'YYYYMMDDHH24MISS'))),
         MAX (DECODE (name, 'role',             value))
  INTO   v_empnum, v_deptnum, v_swipe, v_role
  FROM  (SELECT EXTRACTVALUE (x.t_val, '//name', 
                  'xmlns="[url]http://application/StatusNotification[/url]"') name,
                EXTRACTVALUE (x.t_val, '//value', 
                   'xmlns="[url]http://application/StatusNotification[/url]"') value
         FROM   (SELECT VALUE (t) t_val
                 FROM   TABLE 
                          (XMLSEQUENCE 
                            (EXTRACT 
                              (XMLTYPE 
              ('<?xml version="1.0" encoding="UTF-8"?>
                  <SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
                                     xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
                                     xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
                                     xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"
                                     xmlns="[url]http://application/StatusNotification[/url]">
                    <SOAP-ENV:Body>                  
                      <StatusNotification>
                        <StatusNotificationMessage>
                          <sourceSystem>
                            <sourceSystemType>Somewhere</sourceSystemType>
                            <sourceSystemId>MySystem</sourceSystemId>
                          </sourceSystem>
                          <alternativeIdentifiers>
                            <alternativeIdentifier>
                              <name>employeeNumber</name>
                              <value>1234567890</value>
                            </alternativeIdentifier>
                            <alternativeIdentifier>
                              <name>departmentNumber</name>
                              <value>12345678</value>
                            </alternativeIdentifier>
                          </alternativeIdentifiers>
                          <characteristics>
                            <characteristic>
                              <name>swipeDateTime</name>
                              <value>20120613123625</value>
                            </characteristic>
                            <characteristic>
                              <name>role</name>
                              <value>MAN</value>
                            </characteristic>
                          </characteristics>
                        </StatusNotificationMessage>
                      </StatusNotification>
                    </SOAP-ENV:Body>
                  </SOAP-ENV:Envelope>'), 
                             '//alternativeIdentifier',
                             'xmlns="[url]http://application/StatusNotification[/url]"'))) t) x
         UNION ALL
         SELECT EXTRACTVALUE (y.t_val, '//name', 
                  'xmlns="[url]http://application/StatusNotification[/url]"') name,
                EXTRACTVALUE (y.t_val, '//value', 
                  'xmlns="[url]http://application/StatusNotification[/url]"') value
         FROM   (SELECT VALUE (t) t_val
                 FROM   TABLE 
                          (XMLSEQUENCE 
                            (EXTRACT 
                              (XMLTYPE 
              ('<?xml version="1.0" encoding="UTF-8"?>
                  <SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
                                     xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
                                     xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
                                     xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"
                                     xmlns="[url]http://application/StatusNotification[/url]">
                    <SOAP-ENV:Body>                  
                      <StatusNotification>
                        <StatusNotificationMessage>
                          <sourceSystem>
                            <sourceSystemType>Somewhere</sourceSystemType>
                            <sourceSystemId>MySystem</sourceSystemId>
                          </sourceSystem>
                          <alternativeIdentifiers>
                            <alternativeIdentifier>
                              <name>employeeNumber</name>
                              <value>1234567890</value>
                            </alternativeIdentifier>
                            <alternativeIdentifier>
                              <name>departmentNumber</name>
                              <value>12345678</value>
                            </alternativeIdentifier>
                          </alternativeIdentifiers>
                          <characteristics>
                            <characteristic>
                              <name>swipeDateTime</name>
                              <value>20120613123625</value>
                            </characteristic>
                            <characteristic>
                              <name>role</name>
                              <value>MAN</value>
                            </characteristic>
                          </characteristics>
                        </StatusNotificationMessage>
                      </StatusNotification>
                    </SOAP-ENV:Body>
                  </SOAP-ENV:Envelope>'), 
                                     '//characteristic', 
                                     'xmlns="[url]http://application/StatusNotification[/url]"'))) t) y);
  INSERT INTO staging 
    ("employeeNumber", "departmentNumber", "swipeDateTime", "role")
  VALUES (v_empnum, v_deptnum, v_swipe, v_role);
END;
/
SELECT * FROM staging
/



Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557922 is a reply to message #557921] Sun, 17 June 2012 15:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Here is one more possible workaround. Like all the others it works on my system. I have been trying
to come as close as I can to syntax that others encountering the same problem due to the bug in your
version have used as a workaround successfully. I will be logging off now and will probably be back
in several hours. In the meanwhile, there may be others than can help you. Jum is especially good
with XML problems. You can also search the internet for workarounds for the bug.

VARIABLE xmltext VARCHAR2(4000)
BEGIN
  :xmltext := 
    '<?xml version="1.0" encoding="UTF-8"?>
                  <SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
                                     xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
                                     xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
                                     xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"
                                     xmlns="[url]http://application/StatusNotification[/url]">
                    <SOAP-ENV:Body>                  
                      <StatusNotification>
                        <StatusNotificationMessage>
                          <sourceSystem>
                            <sourceSystemType>Somewhere</sourceSystemType>
                            <sourceSystemId>MySystem</sourceSystemId>
                          </sourceSystem>
                          <alternativeIdentifiers>
                            <alternativeIdentifier>
                              <name>employeeNumber</name>
                              <value>1234567890</value>
                            </alternativeIdentifier>
                            <alternativeIdentifier>
                              <name>departmentNumber</name>
                              <value>12345678</value>
                            </alternativeIdentifier>
                          </alternativeIdentifiers>
                          <characteristics>
                            <characteristic>
                              <name>swipeDateTime</name>
                              <value>20120613123625</value>
                            </characteristic>
                            <characteristic>
                              <name>role</name>
                              <value>MAN</value>
                            </characteristic>
                          </characteristics>
                        </StatusNotificationMessage>
                      </StatusNotification>
                    </SOAP-ENV:Body>
                  </SOAP-ENV:Envelope>';
END;
/
DECLARE
  xmldata XMLTYPE := XMLTYPE (:xmltext);
  CURSOR getstaging (xmldat XMLTYPE) IS
  SELECT MAX (DECODE (name, 'employeeNumber',   value)) empnum,
         MAX (DECODE (name, 'departmentNumber', value)) deptnum,
         MAX (DECODE (name, 'swipeDateTime',    TO_DATE (value, 'YYYYMMDDHH24MISS'))) swipe,
         MAX (DECODE (name, 'role',             value)) role
  FROM  (SELECT EXTRACTVALUE (VALUE (t), '//name', 
                  'xmlns="[url]http://application/StatusNotification[/url]"') name,
                EXTRACTVALUE (VALUE (t), '//value', 
                   'xmlns="[url]http://application/StatusNotification[/url]"') value
         FROM   TABLE 
                  (XMLSEQUENCE 
                    (EXTRACT 
                      (xmldat, 
                       '//alternativeIdentifier',
                       'xmlns="[url]http://application/StatusNotification[/url]"'))) t
         UNION ALL
         SELECT EXTRACTVALUE (VALUE (t), '//name', 
                  'xmlns="[url]http://application/StatusNotification[/url]"') name,
                EXTRACTVALUE (VALUE (t), '//value', 
                  'xmlns="[url]http://application/StatusNotification[/url]"') value
         FROM   TABLE 
                  (XMLSEQUENCE 
                    (EXTRACT 
                      (xmldat,
                       '//characteristic', 
                       'xmlns="[url]http://application/StatusNotification[/url]"'))) t) y;
BEGIN
  FOR r IN getstaging (xmldata) LOOP
    INSERT INTO staging 
      ("employeeNumber", "departmentNumber", "swipeDateTime", "role")
    VALUES (r.empnum, r.deptnum, r.swipe, r.role);
  END LOOP;
END;
/
SELECT * FROM staging
/

Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557961 is a reply to message #557922] Mon, 18 June 2012 05:20 Go to previous messageGo to next message
raghx
Messages: 35
Registered: March 2006
Location: Bangalore
Member
It worked, but the time part was missed. Please see
SQL> VARIABLE xmltext VARCHAR2(4000)
SQL> BEGIN
  2    :xmltext :=
  3  	 '<?xml version="1.0" encoding="UTF-8"?>
  4  		       <SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
  5  					  xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
  6  					  xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
  7  					  xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"
  8  					  xmlns="[url]http://application/StatusNotification[/url]">
  9  			 <SOAP-ENV:Body>
 10  			   <StatusNotification>
 11  			     <StatusNotificationMessage>
 12  			       <sourceSystem>
 13  				 <sourceSystemType>Somewhere</sourceSystemType>
 14  				 <sourceSystemId>MySystem</sourceSystemId>
 15  			       </sourceSystem>
 16  			       <alternativeIdentifiers>
 17  				 <alternativeIdentifier>
 18  				   <name>employeeNumber</name>
 19  				   <value>1234567890</value>
 20  				 </alternativeIdentifier>
 21  				 <alternativeIdentifier>
 22  				   <name>departmentNumber</name>
 23  				   <value>12345678</value>
 24  				 </alternativeIdentifier>
 25  			       </alternativeIdentifiers>
 26  			       <characteristics>
 27  				 <characteristic>
 28  				   <name>swipeDateTime</name>
 29  				   <value>20120613123625</value>
 30  				 </characteristic>
 31  				 <characteristic>
 32  				   <name>role</name>
 33  				   <value>MAN</value>
 34  				 </characteristic>
 35  			       </characteristics>
 36  			     </StatusNotificationMessage>
 37  			   </StatusNotification>
 38  			 </SOAP-ENV:Body>
 39  		       </SOAP-ENV:Envelope>';
 40  END;
 41  /

PL/SQL procedure successfully completed.

SQL> DECLARE
  2    xmldata XMLTYPE := XMLTYPE (:xmltext);
  3    CURSOR getstaging (xmldat XMLTYPE) IS
  4    SELECT MAX (DECODE (name, 'employeeNumber',   value)) empnum,
  5  	      MAX (DECODE (name, 'departmentNumber', value)) deptnum,
  6  	      MAX (DECODE (name, 'swipeDateTime',    TO_DATE (value, 'YYYYMMDDHH24MISS'))) swipe,
  7  	      MAX (DECODE (name, 'role',	     value)) role
  8    FROM  (SELECT EXTRACTVALUE (VALUE (t), '//name',
  9  		       'xmlns="[url]http://application/StatusNotification[/url]"') name,
 10  		     EXTRACTVALUE (VALUE (t), '//value',
 11  			'xmlns="[url]http://application/StatusNotification[/url]"') value
 12  	      FROM   TABLE
 13  		       (XMLSEQUENCE
 14  			 (EXTRACT
 15  			   (xmldat,
 16  			    '//alternativeIdentifier',
 17  			    'xmlns="[url]http://application/StatusNotification[/url]"'))) t
 18  	      UNION ALL
 19  	      SELECT EXTRACTVALUE (VALUE (t), '//name',
 20  		       'xmlns="[url]http://application/StatusNotification[/url]"') name,
 21  		     EXTRACTVALUE (VALUE (t), '//value',
 22  		       'xmlns="[url]http://application/StatusNotification[/url]"') value
 23  	      FROM   TABLE
 24  		       (XMLSEQUENCE
 25  			 (EXTRACT
 26  			   (xmldat,
 27  			    '//characteristic',
 28  			    'xmlns="[url]http://application/StatusNotification[/url]"'))) t) y;
 29  BEGIN
 30    FOR r IN getstaging (xmldata) LOOP
 31  	 INSERT INTO staging
 32  	   ("employeeNumber", "departmentNumber", "swipeDateTime", "role")
 33  	 VALUES (r.empnum, r.deptnum, r.swipe, r.role);
 34    END LOOP;
 35  END;
 36  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM staging
  2  /

employeeNumber departmentNumber swipeDate role                                  
-------------- ---------------- --------- ----                                  
    1234567890         12345678 13-JUN-12 MAN                                   

1 row selected.

Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #557980 is a reply to message #557961] Mon, 18 June 2012 07:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Dates are not stored in a particular format.
By default, dates are displayed according to your nls_date_format.
You can use TO_CHAR to display them in whatever format you want, as shown below.

SCOTT@orcl_11gR2> SELECT "employeeNumber", "departmentNumber",
  2  	    TO_CHAR ("swipeDateTime", 'DD/MM/YYYY HH24:MI:SS') "swipeDateTime",
  3  	    "role"
  4  FROM   staging
  5  /

employeeNumber departmentNumber swipeDateTime       role
-------------- ---------------- ------------------- ----
    1234567890         12345678 13/06/2012 12:36:25 MAN

1 row selected.

Re: XML Parsing in PL/SQL without any DBMS_XML pacakges [message #558290 is a reply to message #557980] Wed, 20 June 2012 11:49 Go to previous message
raghx
Messages: 35
Registered: March 2006
Location: Bangalore
Member
Thanks Barbara, All
It worked.

BTW, My admin is installing the XML_DOM now. Any tips/help on achieving the same using XML_DOM would be much helpful.

Thanks again Smile
Previous Topic: how to use sql to bypass xml special character? Please....:)
Next Topic: regarding xmltype as stored proc parameter
Goto Forum:
  


Current Time: Thu Mar 28 19:04:15 CDT 2024