|
Replies:
12
-
Pages:
1
-
Last Post:
Dec 8, 2006 2:24 AM
Last Post By: user547718
|
|
|
Posts:
27
Registered:
08/08/06
|
|
|
|
getting the "encoding" in generated xml
Posted:
Aug 21, 2006 6:21 AM
|
|
|
|
okay i am using something like:
SELECT SYS_XMLAGG(attribxml,sys.xmlgenformatType.createFormat('hello')).getStringVal() into finalresult FROM temp
everything works fine.i get the xml looking something like
<?xml version="1.0"?>
<hello>
..
..
</hello>
The problem is i want something like
<?xml version="1.0" encoding="UTF-8"?>
at the start of the xml
( yes the encoding tag)
is this possible? i searched the forum but couldnt find something related to this.
thanks in advance
i hate mondays.
|
|
|
Posts:
5,305
Registered:
01/10/01
|
|
|
|
Re: getting the "encoding" in generated xml
Posted:
Aug 21, 2006 8:44 AM
in response to: sphinx
|
|
|
|
The correct way of doing this is with XMLRoot() in 10gR2. You should look at the rest of the SQL/XML operators XMLAGG, XMLELEMENT, XMLFOREST, XMLATTRIBUTES etc rather than SYS_XMLAGG
|
|
|
Posts:
943
Registered:
01/08/04
|
|
|
|
Re: getting the "encoding" in generated xml
Posted:
Aug 21, 2006 8:46 AM
in response to: mdrake
|
|
|
Mark,
I was trying this one, and I didn't see how to incorporate 'encoding' in there.
SQL> select xmlroot(xmltype('<root/>'), version '1.0') from dual;
XMLROOT(XMLTYPE('<ROOT/>'),VERSION'1.0')
<?xml version="1.0"?>
<root/>
SQL> select xmlroot(xmltype('<root/>'), version '1.0', encoding 'UTF-8') from dual;
select xmlroot(xmltype('<root/>'), version '1.0', encoding 'UTF-8') from dual
*
ERROR at line 1:
ORA-02000: missing STANDALONE keyword
SQL> select xmlroot(xmltype('<root/>'), version '1.0', standalone yes, encoding 'UTF-8') from dual;
select xmlroot(xmltype('<root/>'), version '1.0', standalone yes, encoding 'UTF-8') from dual
*
ERROR at line 1:
ORA-00907: missing right parenthesis |
|
|
Posts:
27
Registered:
08/08/06
|
|
|
|
Re: getting the "encoding" in generated xml
Posted:
Aug 21, 2006 9:56 PM
in response to: RPuttagunta
|
|
|
yes , doesnt seem to work that way......now what? i absolutely need to get that encoding tag.
i tried all that and also tried writing a select query in xmlroot something like
strangely it would either say disconnected from oracle or end of file on communication channel like this:
SQL> select XMLROOT (
2 ( SELECT 'encoding=UTF 8' FROM dual ),
3 VERSION '1.0',
4 STANDALONE YES ) from dual
5 /
ERROR:
ORA-03114: not connected to ORACLE
SQL> conn sys/sys@c6demo as sysdba
Connected.
SQL> select XMLROOT (
2 ( SELECT 'encoding=UTF 8' FROM dual ),
3 VERSION '1.0',
4 STANDALONE YES ) from dual
5 /
select XMLROOT (
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
what is going on? have i accidently stumbled upon a way to disconnect from oracle? help!! |
|
|
Posts:
5,305
Registered:
01/10/01
|
|
|
|
Re: getting the "encoding" in generated xml
Posted:
Aug 21, 2006 10:39 PM
in response to: sphinx
|
|
|
Ok, the bug is 5480323
It looks like you'll need to do something like
SQL> select XMLTYPE
2 (
3 '<?xml version="1.0" encoding="UTF-8"?>' ||
4 xmlElement("Foo",'BAA').getClobVal()
5 )
6 from DUAL
7 /
XMLTYPE('<?XMLVERSION="1.0"ENCODING="UTF-8"?>'||XMLELEMENT("FOO",'BAA').GETCLOBV
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="WINDOWS-1252"?>
<Foo>BAA</Foo>
Note how the encoding was adjusted automatically to reflect the character set requested by the client (in my case MS-Windows). To change this set the NLS_LANG value....
eg
C:\TEMP>set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
C:\TEMP>sqlplus scott/tiger
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Aug 21 22:37:38 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select XMLTYPE
2 (
3 '<?xml version="1.0" encoding="UTF-8"?>' ||
4 xmlElement("Foo",'BAA').getClobVal()
5 )
6 from DUAL
7 /
XMLTYPE('<?XMLVERSION="1.0"ENCODING="UTF-8"?>'||XMLELEMENT("FOO",'BAA').GETCLOBV
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?><Foo>BAA</Foo>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
C:\TEMP>set nls_lang
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
C:\TEMP>set nls_lang=
C:\TEMP>set nls_lang
Environment variable nls_lang not defined
C:\TEMP>sqlplus scott/tiger
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Aug 21 22:38:09 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select XMLTYPE
2 (
3 '<?xml version="1.0" encoding="UTF-8"?>' ||
4 xmlElement("Foo",'BAA').getClobVal()
5 )
6 from DUAL
7 /
XMLTYPE('<?XMLVERSION="1.0"ENCODING="UTF-8"?>'||XMLELEMENT("FOO",'BAA').GETCLOBV
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="WINDOWS-1252"?>
<Foo>BAA</Foo>
|
|
|
Posts:
27
Registered:
08/08/06
|
|
|
|
Re: getting the "encoding" in generated xml
Posted:
Aug 22, 2006 12:18 AM
in response to: mdrake
|
|
|
okay so now i have
select XMLELEMENT("icim",XMLAGG(attribxml)) into finalresult2
FROM temp WHERE flag=processflag;
select xmltype ('<?xml version="1.0" encoding="UTF-8"?>' ||CHR(10)|| finalresult2) into finalresult2 from dual;
c:=finalresult2.getclobval();
giving
<?xml version="1.0" encoding="UTF-8"?> --CHR(10) worked here, so did encoding!
<icim><object class="hi" name="xxx">
instead of the simpler
SELECT SYS_XMLAGG(attribxml,sys.xmlgenformatType.createFormat('icim')).getStringVal() into c FROM temp where flag=processflag;
--which gave
<?xml version="1.0"?>
<icim>
<object class="hi" name="xxx">
as you can see,, the encoding thing worked!! great!
but i cannot use the CHR(10) after icim, the root tag,even after trying to pass it as a variable. i need a newline after icim too  ...any help on this please
Message was edited by:
sphinx |
|
|
Posts:
5,305
Registered:
01/10/01
|
|
|
|
Re: getting the "encoding" in generated xml
Posted:
Aug 22, 2006 1:49 AM
in response to: sphinx
|
|
|
The SQL/XML standard states that the operators should not format the output or generate insignificant whitespace. It is proposed that XMLSerialize should provide this functionality but that is still under discussion.
However if you use a single statement you should get the required output
SQL> select xmltype
2 (
3 '<?xml version="1.0" encoding="UTF-8"?>' ||
4 XMLELEMENT
5 (
6 "Employees",
7 ( select XMLAGG
8 (
9 XMLElement
10 (
11 "Employee",
12 xmlAttributes
13 (
14 ENAME as "Name",
15 EMPNO as "Id"
16 )
17 )
18 )
19 from EMP
20 )
21 ).getClobVal()
22 )
23 from dual
24 /
XMLTYPE('<?XMLVERSION="1.0"ENCODING="UTF-8"?>'||XMLELEMENT("EMPLOYEES",(SELECTXM
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="WINDOWS-1252"?>
<Employees>
<Employee Name="SMITH" Id="7369"/>
<Employee Name="ALLEN" Id="7499"/>
<Employee Name="WARD" Id="7521"/>
<Employee Name="JONES" Id="7566"/>
<Employee Name="MARTIN" Id="7654"/>
<Employee Name="BLAKE" Id="7698"/>
<Employee Name="CLARK" Id="7782"/>
<Employee Name="SCOTT" Id="7788"/>
<Employee Name="KING" Id="7839"/>
<Employee Name="TURNER" Id="7844"/>
<Employee Name="ADAMS" Id="7876"/>
<Employee Name="JAMES" Id="7900"/>
<Employee Name="FORD" Id="7902"/>
<Employee Name="MILLER" Id="7934"/>
</Employees>
SQL>
SQL>
|
|
|
Posts:
27
Registered:
08/08/06
|
|
|
|
Re: getting the "encoding" in generated xml
Posted:
Aug 22, 2006 2:47 AM
in response to: mdrake
|
|
|
tried this
select xmltype
('<?xml version="1.0" encoding="UTF-8"?>'
xmlelement
(
"icim",
(select XMLAGG(attribxml) from temp where flag=processflag)
)
) into finalresult2 from dual;
heres what i get
<?xml version="1.0" encoding="UTF-8"?><icim><object........
i can insert chr(10) after <?xml....
which gives
<?xml version="1.0" encoding="UTF-8"?>
<icim><object.....
still the <object> doesnt come up on the next line
sad...guess its meant to be that way if i use a query inside the xmlelement tag.?? |
|
|
Posts:
5,305
Registered:
01/10/01
|
|
|
|
Re: getting the "encoding" in generated xml
Posted:
Aug 22, 2006 7:31 AM
in response to: sphinx
|
|
|
If the foramtting is important try this..
SQL> drop table temp
2 /
Table dropped.
SQL> create table temp
2 (
3 xmldoc xmltype
4 )
5 /
Table created.
SQL> insert into temp values ( xmltype('<object id="1">This is object 1</object>'))
2 /
1 row created.
SQL> insert into temp values ( xmltype('<object id="2">This is object 2</object>'))
2 /
1 row created.
SQL> select xmltype
2 (
3 '<?xml version="1.0" encoding="UTF-8"?>' ||
4 XMLELEMENT
5 (
6 "icim",
7 ( select XMLAGG
8 (
9 xmldoc
10 )
11 from TEMP
12 )
13 )
14 )
15 from dual
16 /
XMLTYPE('<?XMLVERSION="1.0"ENCODING="UTF-8"?>'||XMLELEMENT("ICIM",(SELECTXMLAGG(
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="WINDOWS-1252"?>
<icim>
<object id="1">This is object 1</object>
<object id="2">This is object 2</object>
</icim>
SQL> set serveroutput on
SQL> declare
2 finalresult xmltype;
3 begin
4 select xmltype
5 (
6 '<?xml version="1.0" encoding="UTF-8"?>' ||
7 XMLELEMENT
8 (
9 "icim",
10 ( select XMLAGG
11 (
12 xmldoc
13 )
14 from TEMP
15 )
16 )
17 )
18 into finalresult
19 from dual;
20 dbms_output.put_line(finalresult.getClobVal());
21 end;
22 /
<?xml version="1.0" encoding="UTF-8"?><icim><object id="1">This is object
1</object>
<object id="2">This is object 2</object>
</icim>
PL/SQL procedure successfully completed.
SQL> declare
2 finalresult xmltype;
3 begin
4 select xmltype
5 (
6 '<?xml version="1.0" encoding="UTF-8"?>' || chr(10) ||
7 XMLELEMENT
8 (
9 "icim",
10 ( select XMLAGG
11 (
12 xmldoc
13 )
14 from TEMP
15 )
16 ).extract('/*')
17 )
18 into finalresult
19 from dual;
20 dbms_output.put_line(finalresult.getClobVal());
21 end;
22 /
<?xml version="1.0" encoding="UTF-8"?>
<icim>
<object id="1">This is object 1</object>
<object id="2">This is object 2</object>
</icim>
|
|
|
Posts:
27
Registered:
08/08/06
|
|
|
|
Re: getting the "encoding" in generated xml
Posted:
Aug 22, 2006 9:43 PM
in response to: mdrake
|
|
|
|
mark,, man,,u rock.
thanks.
|
|
|
Posts:
5,305
Registered:
01/10/01
|
|
|
|
Re: getting the "encoding" in generated xml
Posted:
Aug 22, 2006 10:14 PM
in response to: sphinx
|
|
|
|
Glad too help.
Getting a little to old to 'rock'... To quote Jethro Tull "Too old to rock'n'roll, too young to die". However it's appreciated...
Anyway the guys who really rock are the development team behind this product.
I remember when a 2GB Disc Farm was really big ( And I mean 3 soccor fields big !)
|
|
|
Posts:
27
Registered:
08/08/06
|
|
|
|
Re: getting the "encoding" in generated xml
Posted:
Aug 23, 2006 6:12 AM
in response to: mdrake
|
|
|
|
........
He then decides to commit suicide but fails and lands himself in a hospital coma for an undetermined amount of time.
.....
When he awakes he discovers society has changed again, and his style of dress and music are now popular again. He has become an overnight sensation with the young kids who now try to dress and act like him.
.......
.....
hehehe...i would sure [:p]...hehehe....and thnks to u jethro tull now has a new fan.
|
|
|
Posts:
9
Registered:
12/08/06
|
|
|
|
Re: getting the "encoding" in generated xml
Posted:
Dec 8, 2006 2:24 AM
in response to: sphinx
|
|
|
i am getting xml out put properly
can u send ur query to me
my query is
select sys_xmlgen(xmlconcat(xmlelement(main , xmlforest(job,sal)),
xmlelement(sub , xmlforest (mgr, empno)))
from emp
this is my query iam getting out put sequence
i want it in set by set format |
|
|
|
Legend
|
|
Guru : 2500
- 1000000
pts
|
|
Expert : 1000
- 2499
pts
|
|
Pro : 500
- 999
pts
|
|
Journeyman : 200
- 499
pts
|
|
Newbie : 0
- 199
pts
|
|
Oracle ACE Director
|
|
Oracle ACE Member
|
|
Oracle Employee ACE
|
|
Helpful Answer
(5 pts)
|
|
Correct Answer
(10 pts)
|
|
