Home » Developer & Programmer » JDeveloper, Java & XML » XML format question
XML format question [message #339455] |
Thu, 07 August 2008 09:33 |
lmayer
Messages: 8 Registered: December 2006
|
Junior Member |
|
|
Morning,
I have a generic query to format some xml data it is very close except I get an extra row that I need to get rid of <campus_id_row> Does anyone know how I can supress it?
This is what I want:
<ROWSET>
<ROW>
<CAMPUS_ID>
<PERSON_ID>27365F</PERSON_ID>
</CAMPUS_ID>
<DEPARTMENT_OWNER_ID>1</DEPARTMENT_OWNER_ID>
This is what I get:
<ROWSET>
<ROW>
<CAMPUS_ID>
<CAMPUS_ID_ROW>
<PERSON_ID>27365F</PERSON_ID>
</CAMPUS_ID_ROW>
</CAMPUS_ID>
<DEPARTMENT_OWNER_ID>1</DEPARTMENT_OWNER_ID>
This is the simple test query I'm using:
select
dbms_xmlgen.getxml(
'select cursor(select PERSON_ID
from fsa_to_xml a
where a.PERSON_ID = c.PERSON_ID
) campus_id,
c.*
from fsa_to_xml c
where c.PERSON_ID in (''27365F'',''23151J'')') thexml
from dual
Any thoughts would be great. Thanks in advance.
--
Laura
|
|
|
|
Re: XML format question [message #339477 is a reply to message #339455] |
Thu, 07 August 2008 10:14 |
lmayer
Messages: 8 Registered: December 2006
|
Junior Member |
|
|
Thanks for that I will fix. We are on Oracle v.10
here is a sample table with the new code to reference that:
--create table
CREATE TABLE "HRMS_LOAD2"
( "DARTID" VARCHAR2(9 BYTE),
"LASTNAME" VARCHAR2(50 BYTE),
"FIRSTNAME" VARCHAR2(50 BYTE),
"MIDDLENAME" VARCHAR2(50 BYTE),
"NAMESUFFIX" VARCHAR2(50 BYTE),
"BIRTHDATE" VARCHAR2(50 BYTE),
"NATIONALITY" VARCHAR2(50 BYTE),
"TAXRESIDENCY" VARCHAR2(50 BYTE),
"ADDRESS_LINE_1_US" VARCHAR2(50 BYTE),
"ADDRESS_LINE_2_US" VARCHAR2(50 BYTE),
"ADDRESS_CITY_US" VARCHAR2(50 BYTE),
"ADDRESS_STATE_US" VARCHAR2(50 BYTE),
"ADDRESS_ZIP_US" VARCHAR2(50 BYTE)
)
---insert data
insert into HRMS_LOAD2
values('111111','Doe','John','S.',null,'27-NOV-80','GZ','GZ','PO Box 6044',null,'Hanover','NH','03755')
insert into HRMS_LOAD2
values('999999','Smith','Jane','M.',null,'13-SEP-82','CA','CA','PO Box 6105',null,'Hanover','NH','03755')
and the updated code to match:
select
dbms_xmlgen.getxml(
'select cursor(select dartid
from hrms_load2 a
where a.dartid = c.dartid
) campus_id,
c.*
from hrms_load2 c') thexml
from dual
Thanks again for any help you can provide.
Laura
|
|
|
Goto Forum:
Current Time: Fri Dec 06 01:46:32 CST 2024
|