OraFAQ Forum: JDeveloper, Java & XML » XML generation using XSU

Home » Developer & Programmer » JDeveloper, Java & XML » XML generation using XSU

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

XML generation using XSU [message #154323]

Fri, 06 January 2006 18:13

mpinvitesu
Messages: 15
Registered: July 2005

Junior Member

Hi Everyone,

I am creating a simple XML using XSU.

Step 1:
SQL> create type nametype as object(
2 fname varchar2(20),
3 mname varchar2(20),
4 lname varchar2(20)
5 );
6 /

Type created.

Step 2:
SQL> create table emp_details(
2 id number,
3 name nametype
4 );

Table created.

Step 3:
SQL> insert into emp_details(id, name) values(100, nametype('John','M','White'));

1 row created.

Step 4:(Standard XSU invocation)
SQL> create or replace procedure emp_xmlgen as
2 queryCtx DBMS_XMLquery.ctxType;
3 result clob;
4 xmlstr varchar2(32767);
5 line varchar2(2000);
6 begin
7 queryCtx := DBMS_XMLQuery.newContext('select * from emp_details');
8 result := DBMS_XMLQuery.getXML(queryCtx);
9 xmlstr := dbms_lob.SUBSTR(result,32767);
10 loop
11 exit when xmlstr is null;
12 line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
13 dbms_output.put_line('| '||line);
14 xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
15 end loop;
16 DBMS_XMLQuery.closeContext(queryCtx);
17 end;
18 /

Procedure created.

SQL> exec emp_xmlgen;
| <?xml version = '1.0'?>
| <ROWSET>
| <ROW num="1">
| <ID>100</ID>
| <NAME>
| <FNAME>John</FNAME>
| <MNAME>M</MNAME>
| <LNAME>White</LNAME>
| </NAME>
| </ROW>
| </ROWSET>

PL/SQL procedure successfully completed.
Now the actual Question:

If I want the XML format to be (Just added underscores in element names)

| <?xml version = '1.0'?>
| <ROWSET>
| <ROW num="1">
| <ID>100</ID>
| <NAME>
| <_FNAME>John</_FNAME>
| <_MNAME>M</_MNAME>
| <_LNAME>White</_LNAME>
| </NAME>
| </ROW>
| </ROWSET>

How should I do it?. If i try to create an object with underscore as starting character, I am encountering an error...

SQL> create type nametype as object(
2 _fname varchar2(20),
3 _mname varchar2(20),
4 _lname varchar2(20)
5 );
6 /

Warning: Type created with compilation errors.

SQL> show err;
Errors for TYPE NAMETYPE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1 PLS-00103: Encountered the symbol "_" when expecting one of the
following:
, <an identifier> <a double-quoted delimited-identifier>
current order static member map
The symbol "_" was ignored.

3/1 PLS-00103: Encountered the symbol "_" when expecting one of the
following:
, pragma <an identifier>
<a double-quoted delimited-identifier> current order static
member map

LINE/COL ERROR
-------- -----------------------------------------------------------------
The symbol "_" was ignored.

4/1 PLS-00103: Encountered the symbol "_" when expecting one of the
following:
, pragma <an identifier>
<a double-quoted delimited-identifier> current order static
member map
The symbol "_" was ignored.

Have anybody know the workaround...other than using XSLT?

Thanks in Advance
Prakash Muthusamy

Report message to a moderator

Re: XML generation using XSU [message #154375 is a reply to message #154323]

Sat, 07 January 2006 11:36

kmmfoo
Messages: 38
Registered: June 2005
Location: massachusetts

Member

Just a suggestion (since I haven't tried your whole example), but typically the way you create field names that don't meet the standard naming-convention requirements is to enclose them in double-quotes. For example change your "step 1" to something like this:

SQL> create type nametype as object(
2 "_FNAME" varchar2(20),
3 "_MNAME" varchar2(20),
4 "_LNAME" varchar2(20)
5 );
6 /

HTH...

Report message to a moderator

Re: XML generation using XSU [message #154385 is a reply to message #154375]

Sat, 07 January 2006 15:19

mpinvitesu
Messages: 15
Registered: July 2005

Junior Member

Excellent Buddy, I got it....It's working...

Just a quick question...Is this a standard convention to enclose in Quotes for such cases in oracle??

Any references??

Thanks and Regards,
Prakash

Report message to a moderator