Home » Developer & Programmer » JDeveloper, Java & XML » XML format question
XML format question [message #339455] Thu, 07 August 2008 09:33 Go to next message
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 #339466 is a reply to message #339455] Thu, 07 August 2008 09:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
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
Re: XML format question [message #339477 is a reply to message #339455] Thu, 07 August 2008 10:14 Go to previous message
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
Previous Topic: Problem with CSS
Next Topic: XML Document Formatting
Goto Forum:
  


Current Time: Fri Dec 06 01:46:32 CST 2024