How I can add another sql xmlelement and attributes above 'emp' something like summary? I trying it myself but I there was error and dont know how to handle it
There are a number of ways to do that depending on what tools you are using. SQL*Plus or SQLcl : You could spool it to a file using the SPOOL command. From PL/SQL : Use the UTL_FILE package to write it to the file system of the database server. Oracle REST Data Services (ORDS) : Present it as a web service response. Cheers Tim...
@@oracle-base Thanks for the reply, it comes really handy for my purpose :) Know... I was wondering how would be to have a xml that comes from something like: Create Table TUsuario ( CodUsuario Number Primary Key Not Null, UName Varchar2(2)) Create table TLoc ( CodLoc Number Primary Key Not Null, Latitud Number Not Null, Longtud Number Not Null) select CodUsuario, Uname from TUsuario order by CodUsuario; select CodLoc, Latitud, Longtud from TLoc order by CodLo; Basically it's from 2 diferent query, I honestly have try but without any good results 😔 I know it's a trouble from my part and I would understand if there isn't a reply
@@JorgeEnriqueValle It depends what you want. 1) Two separate files, then do them separately. 2) If you want then in one file that is valid XML, then you will have to make a choice how that should be formatted. You can join the tables, if there is a possible join between them, to form a single query, which with then work fine. 3) Alternatively to can do something like return the XML fragments from the two separate queries using an XMLAGG and merge them together to form one document with two sections. Kind-of like, {your fragment goes here}
{your fragment goes here} Those are the only options that come to mind. This is just nesting, as shown in the video. Just build it up one layer at a time.
Nice video. Thanks. I have registered several schemas (xsd) in xdb database and, thus, were generated lots of types, collections and tables. Do you know where I can find a way of generating xml conforming a specific xsd (already registerd in the database) simply instantiating (and setting values to their attributes) objects created by oracle during the registration of schema?
@@oracle-base They aren't exactly the same. XMFOREST doesn’t create elements if values contain NULLs. SQL> select banner 2 from v$version; BANNER _________________________________________________________________________ Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production SQL> SQL> with t as 2 (select 1 as c1, null as c2, 3 as c3 from dual) 3 select xmlelement("root" 4 ,xmlelement("c1", t.c1) 5 ,xmlelement("c2", t.c2) 6 ,xmlelement("c3", t.c3)) as xml_text 7 from t; XML_TEXT _____________________________________________ 13 SQL> SQL> with t as 2 (select 1 as c1, null as c2, 3 as c3 from dual) 3 select xmlelement("root" 4 ,xmlforest(t.c1 as "c1" 5 ,t.c2 as "c2" 6 ,t.c3 as "c3")) as xml_text 7 from t; XML_TEXT ____________________________________ 13
How I can add another sql xmlelement and attributes above 'emp' something like summary? I trying it myself but I there was error and dont know how to handle it
Hello, I found your video really usefull but I wonder if there's a way to export the result of the query to an actual xml file 🤔⁉️
There are a number of ways to do that depending on what tools you are using.
SQL*Plus or SQLcl : You could spool it to a file using the SPOOL command.
From PL/SQL : Use the UTL_FILE package to write it to the file system of the database server.
Oracle REST Data Services (ORDS) : Present it as a web service response.
Cheers
Tim...
@@oracle-base
Thanks for the reply, it comes really handy for my purpose :)
Know...
I was wondering how would be to have a xml that comes from something like:
Create Table TUsuario (
CodUsuario Number Primary Key Not Null,
UName Varchar2(2))
Create table TLoc (
CodLoc Number Primary Key Not Null,
Latitud Number Not Null,
Longtud Number Not Null)
select CodUsuario, Uname from TUsuario order by CodUsuario;
select CodLoc, Latitud, Longtud from TLoc order by CodLo;
Basically it's from 2 diferent query, I honestly have try but without any good results 😔
I know it's a trouble from my part and I would understand if there isn't a reply
@@JorgeEnriqueValle It depends what you want.
1) Two separate files, then do them separately.
2) If you want then in one file that is valid XML, then you will have to make a choice how that should be formatted. You can join the tables, if there is a possible join between them, to form a single query, which with then work fine.
3) Alternatively to can do something like return the XML fragments from the two separate queries using an XMLAGG and merge them together to form one document with two sections. Kind-of like,
{your fragment goes here}
{your fragment goes here}
Those are the only options that come to mind.
This is just nesting, as shown in the video. Just build it up one layer at a time.
Nice video. Thanks.
I have registered several schemas (xsd) in xdb database and, thus, were generated lots of types, collections and tables.
Do you know where I can find a way of generating xml conforming a specific xsd (already registerd in the database) simply instantiating (and setting values to their attributes) objects created by oracle during the registration of schema?
Excellent. Thank you!
Nice Tut..Many Thanks..But what's the difference between XML forest and XML element...They are showing the same output.
XMl Forest is like doing a coma-separated list of XML Elements in a single step. It's just more convenient. The result is the same.
@@oracle-base They aren't exactly the same. XMFOREST doesn’t create elements if values contain NULLs.
SQL> select banner
2 from v$version;
BANNER
_________________________________________________________________________
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL>
SQL> with t as
2 (select 1 as c1, null as c2, 3 as c3 from dual)
3 select xmlelement("root"
4 ,xmlelement("c1", t.c1)
5 ,xmlelement("c2", t.c2)
6 ,xmlelement("c3", t.c3)) as xml_text
7 from t;
XML_TEXT
_____________________________________________
13
SQL>
SQL> with t as
2 (select 1 as c1, null as c2, 3 as c3 from dual)
3 select xmlelement("root"
4 ,xmlforest(t.c1 as "c1"
5 ,t.c2 as "c2"
6 ,t.c3 as "c3")) as xml_text
7 from t;
XML_TEXT
____________________________________
13
Cleared the concept ! Thanks .........
one more function if you can please explain will be very useful - XMLSERIALIZE
Hi. I don't have a video, but I have this article.
oracle-base.com/articles/10g/xmlserialize
that "dot.com" made me laugh more than it should have. Great video