|
|
|
|
| Re: Extracting values from XML's [message #594357 is a reply to message #594355] |
Tue, 27 August 2013 17:11   |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It looks like the problem is that one value has a namespace and the other does not, so I guess you have to use two separate queries and put them together, as demonstrated below. Perhaps somehow else knows of a more efficient method.
SCOTT@orcl12c> CREATE TABLE mytab
2 (xml_id NUMBER,
3 xml_column XMLTYPE)
4 /
Table created.
SCOTT@orcl12c> INSERT INTO mytab VALUES (1, XMLTYPE (
2 '<?xml version="1.0" encoding="UTF-8" ?>
3 <Twist xmlns="http://www.twiststandards.org/3.1/ElectronicBilling" xmlns:ns2="dsig:http://www.w3.org/2000/09/xmldsig#" xmlns:dsig="http://www.w3.org/2000/09/xmldsig#"
4 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
5 xsi:schemaLocation="http://www.twiststandards.org/3.1/ElectronicBilling ../schemas/TWIST3.1.Billing.TWISTMsgElectronicBilling.200609.xsd"
6 xsi:type="ElectronicBillingMsg">
7 <messageId>TWISTMSG</messageId>
8 </Twist>'
9 ))
10 /
1 row created.
SCOTT@orcl12c> INSERT INTO mytab VALUES (2, XMLTYPE (
2 '<?xml version="1.0" encoding="UTF-8" ?>
3 <Twist xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4 xsi:schemaLocation="http://www.twiststandards.org/3.1/ElectronicBilling ../schemas/TWIST3.1.Billing.TWISTMsgElectronicBilling.200609.xsd"
5 xsi:type="ElectronicBillingMsg">
6 <messageId>TWISTMSG</messageId>
7 </Twist>'
8 ))
9 /
1 row created.
SCOTT@orcl12c> COMMIT
2 /
Commit complete.
SCOTT@orcl12c> COLUMN msgid FORMAT A30
SCOTT@orcl12c> -- with namespace:
SCOTT@orcl12c> SELECT xml_id,
2 EXTRACTVALUE
3 (xml_column,
4 '/Twist/messageId',
5 'xmlns="http://www.twiststandards.org/3.1/ElectronicBilling"') MsgId
6 FROM mytab
7 /
XML_ID MSGID
---------- ------------------------------
1 TWISTMSG
2
2 rows selected.
SCOTT@orcl12c> -- without namespace:
SCOTT@orcl12c> SELECT xml_id,
2 EXTRACTVALUE (xml_column, '/Twist/messageId') MsgId
3 FROM mytab
4 /
XML_ID MSGID
---------- ------------------------------
1
2 TWISTMSG
2 rows selected.
SCOTT@orcl12c> -- both:
SCOTT@orcl12c> SELECT *
2 FROM (SELECT xml_id,
3 EXTRACTVALUE
4 (xml_column,
5 '/Twist/messageId',
6 'xmlns="http://www.twiststandards.org/3.1/ElectronicBilling"') MsgId
7 FROM mytab
8 UNION ALL
9 SELECT xml_id,
10 EXTRACTVALUE (xml_column, '/Twist/messageId') MsgId
11 FROM mytab)
12 WHERE MsgId IS NOT NULL
13 /
XML_ID MSGID
---------- ------------------------------
1 TWISTMSG
2 TWISTMSG
2 rows selected.
SCOTT@orcl12c> -- with namespace:
SCOTT@orcl12c> SELECT x.xml_id, t.msgid
2 FROM mytab x,
3 XMLTABLE
4 (XMLNAMESPACES (DEFAULT 'http://www.twiststandards.org/3.1/ElectronicBilling'),
5 'Twist'
6 PASSING x.xml_column
7 COLUMNS
8 MsgId VARCHAR2(30) PATH 'messageId') t
9 /
XML_ID MSGID
---------- ------------------------------
1 TWISTMSG
1 row selected.
SCOTT@orcl12c> -- without namespace:
SCOTT@orcl12c> SELECT x.xml_id, t.msgid
2 FROM mytab x,
3 XMLTABLE
4 ('Twist'
5 PASSING x.xml_column
6 COLUMNS
7 MsgId VARCHAR2(30) PATH 'messageId') t
8 /
XML_ID MSGID
---------- ------------------------------
2 TWISTMSG
1 row selected.
SCOTT@orcl12c> -- both:
SCOTT@orcl12c> SELECT x.xml_id, t.msgid
2 FROM mytab x,
3 XMLTABLE
4 (XMLNAMESPACES (DEFAULT 'http://www.twiststandards.org/3.1/ElectronicBilling'),
5 'Twist'
6 PASSING x.xml_column
7 COLUMNS
8 MsgId VARCHAR2(30) PATH 'messageId') t
9 UNION ALL
10 SELECT x.xml_id, t.msgid
11 FROM mytab x,
12 XMLTABLE
13 ('Twist'
14 PASSING x.xml_column
15 COLUMNS
16 MsgId VARCHAR2(30) PATH 'messageId') t
17 /
XML_ID MSGID
---------- ------------------------------
1 TWISTMSG
2 TWISTMSG
2 rows selected.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
