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  |
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 #557870 is a reply to message #557826] |
Sat, 16 June 2012 12:42  |
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 #557878 is a reply to message #557872] |
Sat, 16 June 2012 14:42 |
 |
Barbara Boehmer
Messages: 9104
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 #557904 is a reply to message #557902] |
Sun, 17 June 2012 11:25 |
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 |
|
Barbara Boehmer
Messages: 9104
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 |
|
Barbara Boehmer
Messages: 9104
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 #557912 is a reply to message #557911] |
Sun, 17 June 2012 13:39 |
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 #557914 is a reply to message #557913] |
Sun, 17 June 2012 13:48 |
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 |
|
Barbara Boehmer
Messages: 9104
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 |
|
Barbara Boehmer
Messages: 9104
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 |
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 #557919 is a reply to message #557918] |
Sun, 17 June 2012 14:31 |
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 |
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 |
|
Barbara Boehmer
Messages: 9104
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 |
|
Barbara Boehmer
Messages: 9104
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 |
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.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Feb 09 09:34:27 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
