I started with the example online at DBMS_XMLGEN Example 5:
</code>
http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a88894/adx05xml.htm#1015194 <code>and modified it for my database. I am able to format all of my tags except for the top level tag. By naming the column that points to a particular Object Type I can name all my tags something like this:
<?xml version="1.0"?>
<Msg>
<FileGet>
<SYS_FIL_CASE16 PK="1001" UT="1" UserID="Bob">
<Col CaseNo="A245" ShortTitle="Test Case"/>
<Ent PK="501" UT="1" PKParent="1001" ParentEntCode="CAS" PKFile="1001" UserID="1">
<Col Ref_PartyType_ID="1" FirstName="Bob" MiddleName="T" LastName="Jones"/>
<Ent PK="2" UT="1" PKParent="501" ParentEntCode="1" PKFile="1001" UserID="1">
<Col Ref_AddressType_ID="1" Address1="124 Main Street" Address2="Apt. B" City="Denver"/>
</Ent>
</Ent>
</SYS_FIL_CASE16>
</FileGet>
</Msg>
Since the top level tag, SYS_FIL_CASE16, is actually a TYPE that is a TABLE consisting of all other types making up the hierarchical set of tables/data. I seem to be stuck with the tag SYS_FIL_CASE16 and can't seem to get around it because its not a column, its a type. Is there any way to get around this so I can name it Ent??
Since all of my xml will need to be formatted this way, I will have multiple types named the same which of course is not possible to do. Is there a way to not use the create type x as table of y and just create the hierarchical types as columns of the other tables?? I tried this with the example 5 and the CAST fails. I am thinking since I was able to name all the other tags, I have to be able to name this last one.???
-----------------------------------------------------------
Additional Info: I would like to get this out of sql and not transform it.
------------------------------------------------------------
-- Table ddl pulled from oem
CREATE TABLE "KRISTI"."ENT_ADDRESS" ("PK" VARCHAR2(38 byte) NOT
NULL, "UT" VARCHAR2(38 byte) NOT NULL, "PKPARENT"
VARCHAR2(38 byte) NOT NULL, "PARENTENTCODE" VARCHAR2(3 byte)
NOT NULL, "PKFILE" VARCHAR2(38 byte), "USERID" VARCHAR2(38
byte) NOT NULL, "REF_ADDRESSTYPE_ID" NUMBER(38) NOT NULL, "ADDRESS1"
VARCHAR2(60 byte) NOT NULL, "ADDRESS2" VARCHAR2(60 byte), "ADDRESS3"
VARCHAR2(60 byte), "ADDRESS4" VARCHAR2(60 byte), "CITY"
VARCHAR2(30 byte) NOT NULL, "REF_STATEPROVINCE_ID"
NUMBER(38) NOT NULL, "ZIPPOSTALCODE" VARCHAR2(10 byte),
CONSTRAINT "PK_ENT_ADDRESS" PRIMARY KEY("PK")
USING INDEX
TABLESPACE "INDX"
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
PCTFREE 10 INITRANS 2 MAXTRANS 255)
TABLESPACE "USERS" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING;
CREATE TABLE "KRISTI"."ENT_PARTY" ("PK" VARCHAR2(38 byte) NOT
NULL, "UT" VARCHAR2(38 byte) NOT NULL, "PKPARENT"
VARCHAR2(38 byte) NOT NULL, "PARENTENTCODE" VARCHAR2(3 byte)
NOT NULL, "PKFILE" VARCHAR2(38 byte), "USERID" VARCHAR2(38
byte) NOT NULL, "REF_PARTYTYPE_ID" NUMBER(38) NOT NULL, "FIRSTNAME"
VARCHAR2(30 byte) NOT NULL, "MIDDLENAME" VARCHAR2(30 byte), "LASTNAME"
VARCHAR2(30 byte) NOT NULL,
CONSTRAINT "PK_ENT_PARTY" PRIMARY KEY("PK")
USING INDEX
TABLESPACE "INDX"
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
PCTFREE 10 INITRANS 2 MAXTRANS 255)
TABLESPACE "USERS" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING ;
CREATE TABLE "KRISTI"."FIL_CASE" ("PK" VARCHAR2(38 byte) NOT NULL,
"UT" VARCHAR2(38 byte) NOT NULL, "USERID" VARCHAR2(38 byte)
NOT NULL, "CASENO" VARCHAR2(10 byte) NOT NULL, "SHORTTITLE"
VARCHAR2(30 byte) NOT NULL, "REF_CASETYPE_ID" NUMBER(38) NOT
NULL, "REF_DISPOSITIONTYPE_ID" NUMBER(38), "REF_DISPOSITIONSTAGE_ID"
NUMBER(38), "FILINGDATE" DATE NOT NULL, "DISPDATE" DATE, "REF_OFFICIAL_ID"
NUMBER(38), "CAS_OFF_FIRSTNAME" VARCHAR2(10 byte), "CAS_OFF_LASTNAME"
VARCHAR2(20 byte),
CONSTRAINT "PK_FIL_CASE" PRIMARY KEY("PK")
USING INDEX
TABLESPACE "INDX"
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
PCTFREE 10 INITRANS 2 MAXTRANS 255)
TABLESPACE "USERS" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING ;
CREATE TABLE temp_clob_tab(result CLOB);
create table sxmsg(pk varchar(38));
-- inserts
insert into sxmsg(pk) values(1001);
INSERT INTO "KRISTI"."FIL_CASE" ("PK" ,"UT" ,"USERID" ,"CASENO" ,"SHORTTITLE" ,"REF_CASETYPE_ID" ,"REF_DISPOSITIONTYPE_ID" ,"REF_DISPOSITIONSTAGE_ID" ,"FILINGDATE" ,"DISPDATE" ,"REF_OFFICIAL_ID" ,"CAS_OFF_FIRSTNAME" ,"CAS_OFF_LASTNAME" )
VALUES ('1001' ,'1' ,'Bob' ,'A245' ,'Test Case' ,1 ,1 ,1 ,sysdate ,sysdate ,1 ,'George' ,'Smith' );
INSERT INTO "KRISTI"."ENT_PARTY" ("PK" ,"UT" ,"PKPARENT" ,"PARENTENTCODE" ,"PKFILE" ,"USERID" ,"REF_PARTYTYPE_ID" ,"FIRSTNAME" ,"MIDDLENAME" ,"LASTNAME" )
VALUES ('501' ,'1' ,'1001' ,'CAS' ,'1001' ,'1' ,1 ,'Bob' ,'T' ,'Jones' )
INSERT INTO "KRISTI"."ENT_ADDRESS" ("PK" ,"UT" ,"PKPARENT" ,"PARENTENTCODE" ,"PKFILE" ,"USERID" ,"REF_ADDRESSTYPE_ID" ,"ADDRESS1" ,"ADDRESS2" ,"ADDRESS3" ,"ADDRESS4" ,"CITY" ,"REF_STATEPROVINCE_ID" ,"ZIPPOSTALCODE" )
VALUES ('2' ,'1' ,'501' ,'1' ,'1001' ,'1' ,1 ,'124 Main Street' ,'Apt. B' ,'' ,'' ,'Denver' ,3 ,'80201' )
-- types
CREATE or REPLACE TYPE ent_address16 AS OBJECT(
"@Ref_AddressType_ID" NUMBER(38),
"@Address1" VARCHAR2(30),
"@Address2" VARCHAR2(30),
"@Address3" VARCHAR2(30),
"@Address4" VARCHAR2(30),
"@City" varchar2(30),
"@Ref_StateProvince_ID" number(38),
"@ZipPostalCode" VARCHAR2(10)
);
/
CREATE OR REPLACE TYPE sys_ent_address16 AS OBJECT(
"@PK" VARCHAR2(38),
"@UT" VARCHAR2(38),
"@PKParent" VARCHAR2(38),
"@ParentEntCode" VARCHAR2(3),
"@PKFile" VARCHAR2(38),
"@UserID" VARCHAR2(38),
"Col" ent_address16
);
/
CREATE OR REPLACE TYPE ent_party16 AS OBJECT(
"@Ref_PartyType_ID" varchar2(38),
"@FirstName" VARCHAR2(30),
"@MiddleName" varchar2(30),
"@LastName" varchar2(38)
);
/
CREATE OR REPLACE TYPE sys_ent_party16 AS OBJECT(
"@PK" VARCHAR2(38),
"@UT" VARCHAR2(38),
"@PKParent" VARCHAR2(38),
"@ParentEntCode" VARCHAR2(3),
"@PKFile" VARCHAR2(38),
"@UserID" VARCHAR2(38),
"Col" ent_party16,
"Ent" sys_ent_address16
);
/
CREATE OR REPLACE TYPE fil_case16 AS OBJECT(
"@CaseNo" varchar2(10),
"@ShortTitle" VARCHAR2(30),
"@Ref_CaseType_ID" Number(38),
"@Ref_DispositionType_ID" Number(38),
"@FilingDate" Date,
"@DispDate" Date
);
/
CREATE OR REPLACE TYPE sys_fil_case16 AS OBJECT(
"@PK" VARCHAR2(38),
"@UT" VARCHAR2(38),
"@UserID" varchar2(10),
"Col" fil_case16,
"Ent" sys_ent_party16
);
/
CREATE OR REPLACE TYPE CasePartyAddress16_ntabtyp AS TABLE OF sys_fil_case16
/
CREATE OR REPLACE TYPE sxmsg16 AS OBJECT(
pk VARCHAR2(38),
CasePartyAddr CasePartyAddress16_ntabtyp
);
/
-- view
CREATE OR REPLACE VIEW CasePartyAddress OF sxmsg16
WITH OBJECT IDENTIFIER (PK)
AS SELECT s.pk,
CAST( MULTISET(
SELECT sys_fil_case16(sc.pk, sc.ut, sc.userid,
fil_case16(c.caseno, c.shorttitle, c.ref_casetype_id, c.ref_dispositiontype_id, c.filingdate, c.dispdate),
sys_ent_party16(sp.PK, sp.UT, sp.PKParent, sp.parentEntCode, sp.pkfile, sp.userid,
ent_party16(p.Ref_PartyType_ID, p.FirstName, p.MiddleName, p.LastName),
sys_ent_address16(sa.PK, sa.UT, sa.PKParent, sa.parentEntCode, sa.pkfile, sa.userid,
ent_address16(a.ref_addresstype_id, a.address1, a.address2, a.address3, a.address4, a.city, a.ref_stateprovince_id, a.zippostalcode))))
FROM fil_case sc, fil_case c, ent_party sp, ent_party p, ent_address sa, ent_address a
WHERE sc.pk = s.pk and sc.pk = c.pk and sp.pkparent = c.pk and sp.pk = p.pk and sa.pkparent = p.pk and sa.pk = a.pk )
AS CasePartyAddress16_ntabtyp)
FROM sxmsg s;
-- procedure
CREATE OR REPLACE PROCEDURE "KRISTI"."SP_DBMS_XMLGEN"
AS
qryCtx DBMS_XMLGEN.ctxHandle;
result CLOB;
BEGIN
qryCtx := dbms_xmlgen.newContext('
SELECT CasePartyAddr "FileGet"
FROM CasePartyAddress d'
);
DBMS_XMLGEN.setRowTag(qryCtx, NULL);
DBMS_XMLGEN.setRowSetTag(qryCtx, 'Msg');
DELETE temp_clob_tab;
result := DBMS_XMLGEN.getXML(qryCtx);
INSERT INTO temp_clob_tab VALUES(result);
End;
/
-- sqlplus
set serveroutput on;
begin
sp_dbms_xmlgen();
end;
/
set pagesize 2000;
set linesize 120;
set long 4000;
select * from temp_clob_tab;
I sent this to our resident XML guru, Sean Dillon and he says....
It's relatively easy to change the names of elements in generated XML from DBMS_XMLGEN. You can use column aliases to accomplish this. In the following example, I build a couple of simple types and then use two different queries in the DBMS_XMLGEN.NEWCONTEXT call to modify the element names of my generated xml documents:
SQL> create type phonelist_vartyp as varray(10) of varchar2(20)
2 /
Type created.
SQL> create type address_typ as object(
2 street varchar2(200),
3 city varchar2(200),
4 state char(2),
5 zip varchar2(20)
6 )
7 /
Type created.
SQL> create type customer_typ as object(
2 customer_no number,
3 customer_name varchar2(200),
4 address address_typ,
5 phone_list phonelist_vartyp
6 )
7 /
Type created.
SQL> create table customer_tab(
2 customer_no number,
3 customer_name varchar2(200),
4 street varchar2(200),
5 city varchar2(200),
6 state char(2),
7 zip varchar2(20),
8 phone1 varchar2(20),
9 phone2 varchar2(20),
10 phone3 varchar2(20),
11 phone4 varchar2(20),
12 constraint customer_pk primary key (customer_no)
13 )
14 /
Table created.
SQL> create or replace view customer_view
2 of customer_typ
3 with object identifier( customer_no )
4 as select ct.customer_no, ct.customer_name,
5 address_typ( ct.street, ct.city, ct.state, ct.zip ),
6 phonelist_vartyp( ct.phone1, ct.phone2, ct.phone3, ct.phone4 )
7 from customer_tab ct
8 /
View created.
SQL> insert into customer_tab values (1, 'Jean Nance', '2 Avocet Drive',
2 'Redwood Shores', 'CA', '95054',
3 '415-555-1212', '650-123-3456', '609-555-1212', '201-555-1212') ;
1 row created.
SQL> insert into customer_tab values (2, 'John Nike', '323 College Drive',
2 'Edison', 'NJ', '08820',
3 '609-555-1212', '201-555-1212', '415-555-1212', '650-123-3456') ;
1 row created.
SQL> set autoprint on
SQL> variable c clob;
SQL> declare
2 qryctx dbms_xmlgen.ctxhandle;
3 begin
4 -- notice SELECT *: just take the defaults
5 qryctx := dbms_xmlgen.newcontext('select * from customer_view');
6 dbms_xmlgen.setrowsettag(qryctx,'PeopleList');
7 dbms_xmlgen.setrowtag(qryctx,'Employees');
8 :c := dbms_xmlgen.getxml(qryctx);
9 end;
10 /
PL/SQL procedure successfully completed.
C
-------------------------------------------------------------------------------
<?xml version="1.0"?>
<PeopleList>
<Employees>
<CUSTOMER_NO>1</CUSTOMER_NO>
<CUSTOMER_NAME>Jean Nance</CUSTOMER_NAME>
<ADDRESS>
<STREET>2 Avocet Drive</STREET>
<CITY>Redwood Shores</CITY>
<STATE>CA</STATE>
<ZIP>95054</ZIP>
</ADDRESS>
<PHONE_LIST>
<VARCHAR2>415-555-1212</VARCHAR2>
<VARCHAR2>650-123-3456</VARCHAR2>
<VARCHAR2>609-555-1212</VARCHAR2>
<VARCHAR2>201-555-1212</VARCHAR2>
</PHONE_LIST>
</Employees>
<Employees>
<CUSTOMER_NO>2</CUSTOMER_NO>
<CUSTOMER_NAME>John Nike</CUSTOMER_NAME>
<ADDRESS>
<STREET>323 College Drive</STREET>
<CITY>Edison</CITY>
<STATE>NJ</STATE>
<ZIP>08820</ZIP>
</ADDRESS>
<PHONE_LIST>
<VARCHAR2>609-555-1212</VARCHAR2>
<VARCHAR2>201-555-1212</VARCHAR2>
<VARCHAR2>415-555-1212</VARCHAR2>
<VARCHAR2>650-123-3456</VARCHAR2>
</PHONE_LIST>
</Employees>
</PeopleList>
SQL> declare
2 qryctx dbms_xmlgen.ctxhandle;
3 begin
4 -- in this one, we use aliases to make attributes and rename elements:
5 qryctx := dbms_xmlgen.newcontext('
6 select customer_no "@cno", customer_name "@cname",
7 address "address_info", phone_list "phone_numbers"
8 from customer_view');
9 dbms_xmlgen.setrowsettag(qryctx,'PeopleList');
10 dbms_xmlgen.setrowtag(qryctx,'Employees');
11 :c := dbms_xmlgen.getxml(qryctx);
12 end;
13 /
PL/SQL procedure successfully completed.
C
-------------------------------------------------------------------------------
<?xml version="1.0"?>
<PeopleList>
<Employees cno="1" cname="Jean Nance">
<address_info>
<STREET>2 Avocet Drive</STREET>
<CITY>Redwood Shores</CITY>
<STATE>CA</STATE>
<ZIP>95054</ZIP>
</address_info>
<phone_numbers>
<VARCHAR2>415-555-1212</VARCHAR2>
<VARCHAR2>650-123-3456</VARCHAR2>
<VARCHAR2>609-555-1212</VARCHAR2>
<VARCHAR2>201-555-1212</VARCHAR2>
</phone_numbers>
</Employees>
<Employees cno="2" cname="John Nike">
<address_info>
<STREET>323 College Drive</STREET>
<CITY>Edison</CITY>
<STATE>NJ</STATE>
<ZIP>08820</ZIP>
</address_info>
<phone_numbers>
<VARCHAR2>609-555-1212</VARCHAR2>
<VARCHAR2>201-555-1212</VARCHAR2>
<VARCHAR2>415-555-1212</VARCHAR2>
<VARCHAR2>650-123-3456</VARCHAR2>
</phone_numbers>
</Employees>
</PeopleList>
That's it... you have pretty good control over your generated XML element names, as well as the flexibility to move elements to attributes by aliasing columns with at-signs (from the block above):
6 select customer_no "@cno", customer_name "@cname",
...resulted in:
<Employees cno="1" cname="Jean Nance">
...and...
<Employees cno="2" cname="John Nike">
If you are constructing a type in your query, you can alias that as well. The following block would work fine:
SQL> declare
2 qryctx dbms_xmlgen.ctxhandle;
3 begin
4 qryctx := dbms_xmlgen.newcontext('
5 select ct.customer_no, ct.customer_name,
6 address_typ(ct.street, ct.city, ct.state, ct.zip) "addresses",
7 phonelist_vartyp(ct.phone1, ct.phone2, ct.phone3, ct.phone4) "phone_numbers"
8 from customer_tab ct');
9 dbms_xmlgen.setrowsettag(qryctx,'PeopleList');
10 dbms_xmlgen.setrowtag(qryctx,'Employees');
11 :c := dbms_xmlgen.getxml(qryctx);
12 end;
13 /
You get the idea... hope that helps!
***********************************************************
FOLLOW-UP TO REVIEW 1:
Yeah, using cast(multiset in your view makes the standard column aliasing functionality a bit more difficult. You could easily use XSLT to transform the resulting document to whatever it is you want the new entity names to be, for example:
SQL> create or replace function transform_xml_with_xsl(
2 p_xml in clob,
3 p_xsl in clob )
4 return clob
5 is
6 l_par xmlparser.parser;
7 l_xml xmldom.domdocument;
8 l_pro xslprocessor.processor;
9 l_xsl xslprocessor.stylesheet;
10 l_clob clob;
11 begin
12 l_par := xmlparser.newparser;
13 xmlparser.parseclob(l_par, p_xsl);
14 l_xsl := xslprocessor.newstylesheet(xmlparser.getdocument(l_par),null);
15 xmlparser.parseclob(l_par, p_xml);
16 l_xml := xmlparser.getdocument(l_par);
17 dbms_lob.createtemporary(l_clob, true, dbms_lob.session);
18 l_pro := xslprocessor.newprocessor;
19 xslprocessor.processxsl(l_pro,l_xsl,l_xml,l_clob);
20 xmlparser.freeparser(l_par);
21 xslprocessor.freeprocessor(l_pro);
22 return l_clob;
23 exception
24 when others then
25 xmlparser.freeparser(l_par);
26 xslprocessor.freeprocessor(l_pro);
27 raise;
28 end transform_xml_with_xsl;
29 /
Function created.
SQL> create or replace function sp_dbms_xmlgen
2 return clob
3 as
4 l_qryctx dbms_xmlgen.ctxhandle;
5 l_xsl clob;
6 begin
7 l_xsl :=
8 '<?xml version="1.0"?>
9 <xsl:stylesheet xmlns:xsl="</code>
http://www.w3.org/1999/XSL/Transform" <code>version="1.0">
10 <xsl:template match="node()|@*">
11 <xsl:copy>
12 <xsl:apply-templates select="@*|node()"/>
13 </xsl:copy>
14 </xsl:template>
15
16 <xsl:template match="SYS_FIL_CASE16">
17 <NEWENT><xsl:apply-templates select="@*|node()"/></NEWENT>
18 </xsl:template>
19 </xsl:stylesheet>';
20
21 l_qryctx := dbms_xmlgen.newcontext('
22 select casepartyaddr "FileGet"
23 from casepartyaddress d');
24 dbms_xmlgen.setrowtag(l_qryctx, null);
25 dbms_xmlgen.setrowsettag(l_qryctx, 'msg');
26
27 return transform_xml_with_xsl(
28 p_xml => dbms_xmlgen.getxml(l_qryctx),
29 p_xsl => l_xsl );
30
31 end;
32 /
Function created.
SQL> show errors
No errors.
SQL>
SQL> var c clob;
SQL> begin
2 :c := sp_dbms_xmlgen;
3 end;
4 /
PL/SQL procedure successfully completed.
C
-------------------------------------------------------------------------------
<?xml version = '1.0' encoding = 'UTF-8'?>
<msg>
<FileGet>
<NEWENT PK="1001" UT="1" UserID="Bob">
<Col CaseNo="A245" ShortTitle="Test Case" Ref_CaseType_ID="1"
Ref_DispositionType_ID="1" FilingDate="31-OCT-01"
DispDate="31-OCT-01"/>
<Ent PK="501" UT="1" PKParent="1001" ParentEntCode="CAS"
PKFile="1001" UserID="1">
<Col Ref_PartyType_ID="1" FirstName="Bob" MiddleName="T"
LastName="Jones"/>
<Ent PK="2" UT="1" PKParent="501" ParentEntCode="1"
PKFile="1001" UserID="1">
<Col Ref_AddressType_ID="1" Address1="124 Main Street"
Address2="Apt. B" City="Denver" Ref_StateProvince_ID="3"
ZipPostalCode="80201"/>
</Ent>
</Ent>
</NEWENT>
</FileGet>
</msg>
r/s,
Sean Dillon