OraFAQ Forum: JDeveloper, Java & XML

Home » Developer & Programmer » JDeveloper, Java & XML » Select from XML (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Select from XML [message #558078]

Tue, 19 June 2012 05:20

bhiggin2
Messages: 3
Registered: June 2012
Location: Cork

Junior Member

I have a xml clob field in a table and I want to extract the value of the <SampleInternalId> into a new view and have each value for the <SampleInternalId> in its own row. I am using the following sql statement to extract the data

SELECT m.messagecontent.extract('//SampleData/Samples/Sample/SampleInternalId/text()') .getStringval() as "InternalID",
m.messagecontent.extract('//SampleData/SampleGroupId/text()') .getStringval() as "GroupID",
'SMPLGRID:' ||
m.messagecontent.extract('//SampleData/SampleGroupId/text()') .getStringval() as "Printdata",
m.messageidexternal,
m.messageidmes
FROM pasx.ifarchivedmessage m
WHERE m.messagetype = 'SLDT';

The issue I am having is the statement above is putting all the values for <SampleInternalId> into one field, e.g 232297232298232299. Is it possible to have 3 rows the Internal ID split out.

CLOB Field

<?xml version="1.0" encoding="UTF-8"?>
<SampleData>
	<MesRequestId>20120209122106507</MesRequestId>
	<Site>Test</Site>
	<SampleGroupId>Test-BULK-123579-BOBK8</SampleGroupId>
	<SampleGroupInternalId>232296</SampleGroupInternalId>
	<ProcessDescription>Formulated Bulk Release</ProcessDescription>
	<Samples>
		<Sample>
			<TypeOfSampleId>SID01</TypeOfSampleId>
			<SampleId>010-SID01C-01</SampleId>
			<SampleInternalId>232297</SampleInternalId>
        	</Sample>
		<Sample>
			<TypeOfSampleId>SID02</TypeOfSampleId>
			<SampleId>010-SID02-01</SampleId>
			<SampleInternalId>232298</SampleInternalId>
		</Sample>
		<Sample>
			<TypeOfSampleId>SID03</TypeOfSampleId>
			<SampleId>010-SID03-01</SampleId>
			<SampleInternalId>232299</SampleInternalId>
	        </Sample>
	</Samples>
</SampleData>

Thanks

BH

Report message to a moderator

Re: Select from XML [message #558082 is a reply to message #558078]

Tue, 19 June 2012 05:58

Michel Cadot
Messages: 68420
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Show us what should be the result with your data.

Regards
Michel

Report message to a moderator

Re: Select from XML [message #558083 is a reply to message #558082]

Tue, 19 June 2012 06:02

Michel Cadot
Messages: 68420
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Is this?

SQL> with data as (
  2    select xmltype('<?xml version="1.0" encoding="UTF-8"?>
  3  <SampleData>
  4   <MesRequestId>20120209122106507</MesRequestId>
  5   <Site>Test</Site>
  6   <SampleGroupId>Test-BULK-123579-BOBK8</SampleGroupId>
  7   <SampleGroupInternalId>232296</SampleGroupInternalId>
  8   <ProcessDescription>Formulated Bulk Release</ProcessDescription>
  9   <Samples>
 10    <Sample>
 11     <TypeOfSampleId>SID01</TypeOfSampleId>
 12     <SampleId>010-SID01C-01</SampleId>
 13     <SampleInternalId>232297</SampleInternalId>
 14           </Sample>
 15    <Sample>
 16     <TypeOfSampleId>SID02</TypeOfSampleId>
 17     <SampleId>010-SID02-01</SampleId>
 18     <SampleInternalId>232298</SampleInternalId>
 19    </Sample>
 20    <Sample>
 21     <TypeOfSampleId>SID03</TypeOfSampleId>
 22     <SampleId>010-SID03-01</SampleId>
 23     <SampleInternalId>232299</SampleInternalId>
 24           </Sample>
 25   </Samples>
 26  </SampleData>') data from dual
 27    )
 28  select extractvalue(value(x), '//SampleInternalId') val
 29  from data, table(xmlsequence(extract(data, '//Sample'))) x
 30  /
VAL
--------------------------------------------------------------------------
232297
232298
232299

3 rows selected.

Regards
Michel

Report message to a moderator

Re: Select from XML [message #558084 is a reply to message #558083]

Tue, 19 June 2012 06:08

bhiggin2
Messages: 3
Registered: June 2012
Location: Cork

Junior Member

Thanks Michel. Smile

That is the result set I want.

Can I point the query at the clob itself or would I have extract the full xml statement to another location and then incorporate it into the query ?

Thanks again

BH

Report message to a moderator

Re: Select from XML [message #558085 is a reply to message #558084]

Tue, 19 June 2012 06:15

Michel Cadot
Messages: 68420
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The best way to know is to test:

SQL> create table t2 (v clob);

Table created.

SQL> insert into t2 values ('<?xml version="1.0" encoding="UTF-8"?>
  2  <SampleData>
  3   <MesRequestId>20120209122106507</MesRequestId>
  4   <Site>Test</Site>
  5   <SampleGroupId>Test-BULK-123579-BOBK8</SampleGroupId>
  6   <SampleGroupInternalId>232296</SampleGroupInternalId>
  7   <ProcessDescription>Formulated Bulk Release</ProcessDescription>
  8   <Samples>
  9    <Sample>
 10     <TypeOfSampleId>SID01</TypeOfSampleId>
 11     <SampleId>010-SID01C-01</SampleId>
 12     <SampleInternalId>232297</SampleInternalId>
 13           </Sample>
 14    <Sample>
 15     <TypeOfSampleId>SID02</TypeOfSampleId>
 16     <SampleId>010-SID02-01</SampleId>
 17     <SampleInternalId>232298</SampleInternalId>
 18    </Sample>
 19    <Sample>
 20     <TypeOfSampleId>SID03</TypeOfSampleId>
 21     <SampleId>010-SID03-01</SampleId>
 22     <SampleInternalId>232299</SampleInternalId>
 23           </Sample>
 24   </Samples>
 25  </SampleData>');

1 row created.

SQL> commit;

Commit complete.

SQL> select extractvalue(value(x), '//SampleInternalId') val
  2  from t2, table(xmlsequence(extract(xmltype(v), '//Sample'))) x
  3  /
VAL
-------------------------------------------------------------------------------
232297
232298
232299

3 rows selected.

(Note the usage of XMLTYPE function in SELECT.)

Regards
Michel

Report message to a moderator

Re: Select from XML [message #558086 is a reply to message #558085]

Tue, 19 June 2012 06:17

bhiggin2
Messages: 3
Registered: June 2012
Location: Cork

Junior Member

Thank Michel, again.

I will test this against my own data. But it think you have it sorted.

Much appreciated.

BH

Report message to a moderator

Previous Topic:	How to Select xml node from oracle database.. Help !! Project Is Due Soon....T.T
Next Topic:	how to use sql to bypass xml special character? Please....:)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Mar 29 08:12:20 CDT 2023