OraFAQ Forum: JDeveloper, Java & XML

Home » Developer & Programmer » JDeveloper, Java & XML » XML format question

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

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

Report message to a moderator

Re: XML format question [message #339466 is a reply to message #339455]

Thu, 07 August 2008 09:55

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel

Report message to a moderator

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

Report message to a moderator

Previous Topic:	Problem with CSS
Next Topic:	XML Document Formatting

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 16:25:54 CST 2024