Home » Developer & Programmer » JDeveloper, Java & XML » Extracting values from XML's (Oracle, 11g, Windows 7)
Extracting values from XML's [message #594321] Tue, 27 August 2013 07:24 Go to next message
ho2512
Messages: 7
Registered: August 2013
Junior Member
Hi,

I am having two XML's in my table.

XML1 =
<?xml version="1.0" encoding="UTF-8" ?>
<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#"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.twiststandards.org/3.1/ElectronicBilling ../schemas/TWIST3.1.Billing.TWISTMsgElectronicBilling.200609.xsd"
xsi:type="ElectronicBillingMsg">
<messageId>TWISTMSG</messageId>
</Twist>


XML2=
<?xml version="1.0" encoding="UTF-8" ?>
<Twist xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.twiststandards.org/3.1/ElectronicBilling ../schemas/TWIST3.1.Billing.TWISTMsgElectronicBilling.200609.xsd"
xsi:type="ElectronicBillingMsg">
<messageId>TWISTMSG</messageId>
</Twist>

I am using the below query to extract value from XML.

SELECT extractvalue(xml_column, '/Twist/messageId') MsgId FROM mytab;

When i run this query on XML2, it gives the output.
But when the same query is fired on XML1, its not returning the message id.

Can anyone please let me know the reason.

Thanks.
Re: Extracting values from XML's [message #594355 is a reply to message #594321] Tue, 27 August 2013 16:15 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Hi,

Yes, I can try.

I will ask you to provide us ddl to create mytab, and insert statements to insert xml1 and xml2 values in it.

Also please adhere to forum guidelines:

OraFAQ Forum Guidelines

Regards,
Manu
Re: Extracting values from XML's [message #594357 is a reply to message #594355] Tue, 27 August 2013 17:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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.

Re: Extracting values from XML's [message #594521 is a reply to message #594357] Thu, 29 August 2013 07:57 Go to previous message
ho2512
Messages: 7
Registered: August 2013
Junior Member
Thank You Very Much Barbara.
Previous Topic: AQ and JMS
Next Topic: Problem in J Dev - OAF -- Tree
Goto Forum:
  


Current Time: Thu Mar 28 14:37:06 CDT 2024