Home » SQL & PL/SQL » SQL & PL/SQL » Querying XML
Querying XML [message #252737] Thu, 19 July 2007 18:02 Go to next message
lowcash75
Messages: 67
Registered: February 2006
Member
I have a clob column in a table that contains an xml document. Example of the document.

<my.test.one.vo>
<id>1</id>
<type>New</type>
<acctNumber>111</acctNumber>
</my.test.one.vo>

I need a query to select just the acctNumber from the xml document.

Thanks!
Re: Querying XML [message #252771 is a reply to message #252737] Thu, 19 July 2007 23:15 Go to previous message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 20 00:13:55 2007

column acctNumber format a20

select
extractvalue(
xmltype(
'<my.test.one.vo>
<id>1</id>
<type>New</type>
<acctNumber>111</acctNumber>
</my.test.one.vo>'),
'/my.test.one.vo/acctNumber' )
as acctNumber
from dual ;

ACCTNUMBER
--------------------
111

-- or --

select
xmltype(
'<my.test.one.vo>
<id>1</id>
<type>New</type>
<acctNumber>111</acctNumber>
</my.test.one.vo>').extract('/my.test.one.vo/acctNumber/text()')
as acctNumber
from dual ;

ACCTNUMBER
--------------------
111


--
Joe Fuda
SQL Snippets

Previous Topic: cross tab query for last 12 months data
Next Topic: Problem with Fetching the data to table
Goto Forum:
  


Current Time: Tue Dec 06 16:24:23 CST 2016

Total time taken to generate the page: 0.10071 seconds