Home » Developer & Programmer » JDeveloper, Java & XML » Generate XML format (Oracle 10, Windows)
| Generate XML format [message #300050] |
Thu, 14 February 2008 01:56  |
mrzali
Messages: 3
Registered: February 2008
|
Junior Member |
|
|
Hi there,
I'm trying to produce an xml in certain output, n these are what i can do. But the output doesn't turn out well as i expected.
Can anybody simulate these and give some solutions on the actual output below? I'm totally new n just learned xml few days back.
CREATE TABLE TEST_DATA (
ID VARCHAR2(15),
TRANCODE VARCHAR2(15),
INTCLIENTREF VARCHAR2(15),
EXTPOLICYREF CHAR(2),
EXTCLASSREF VARCHAR2(15),
POLICYNO VARCHAR2(40),
INSURED_NAME VARCHAR2(60),
INSURED_ADDR1 VARCHAR2(100),
INSURED_ADDR2 VARCHAR2(100),
INSURED_ADDR3 VARCHAR2(100),
COVER_DATE_FROM VARCHAR2(10),
COVER_DATE_TO VARCHAR2(10),
GWP VARCHAR2(15),
FSL VARCHAR2(15),
GST VARCHAR2(15),
SD VARCHAR2(15));
INSERT INTO TEST_DATA ( ID, TRANCODE, INTCLIENTREF, EXTPOLICYREF,
EXTCLASSREF, POLICYNO, INSURED_NAME, INSURED_ADDR1, INSURED_ADDR2,
INSURED_ADDR3,COVER_DATE_FROM, COVER_DATE_TO, GWP, FSL, GST,
SD ) VALUES (
'ILD080214000049', 'New', 'Test1', 'LL', '0001', 'SSLL000004', '10 LOC QUOTE', '28 O''connell street'
, 'ACTON', 'ACT', '2008-01-24', '2009-01-24'
, '2029.04', '106.40', '213.58', '197.36');
commit;
DECLARE
ctx DBMS_XMLGEN.ctxHandle;
xml CLOB;
xmlc VARCHAR2(4000);
off INTEGER := 1;
len INTEGER := 4000;
BEGIN
ctx := DBMS_XMLGEN.newContext('SELECT XMLElement( "Policy"
, XMLElement("TransactionCode", TRANCODE)
, XMLElement("InternalClientRef", INTCLIENTREF)
, XMLElement("ExternalPolicyRef", EXTPOLICYREF)
, XMLElement("ExternalClassRef", EXTCLASSREF)
, XMLElement("InceptionDate", COVER_DATE_FROM)
, XMLElement("ExpiryDate", COVER_DATE_TO)
, XMLElement("PolicyNumber", POLICYNO)
, XMLElement("Base", GWP)
, XMLElement("BaseGST", GST)
, XMLElement("FireLevy", FSL)
, XMLElement("StampDuty", SD))
, XMLElement("Client"
, XMLElement("Name", INSURED_NAME)
, XMLElement("PostalAddress1", INSURED_ADDR1)
, XMLElement("PostalAddress2", INSURED_ADDR2)
, XMLElement("PostalAddress3", INSURED_ADDR3))
FROM TEST_DATA');
DBMS_XMLGen.setRowsetTag(ctx, 'Import');
DBMS_XMLGen.setRowTag(ctx, 'Policy');
DBMS_OUTPUT.PUT_LINE(ctx);
xml := DBMS_XMLGEN.getXML(ctx);
DBMS_XMLGEN.closeContext(ctx);
DBMS_LOB.READ( xml, len, off, xmlc);
DBMS_OUTPUT.PUT_LINE(xmlc);
END;
/
<?xml version="1.0"?>
<Import>
<Policy>
<XMLELEMENT_x0028__x0022_POLICY_x0022__x002C_XMLELEMENT_x0028__x0022_TRANSACTION
CODE_x0022__x002C_TRANCODE_x0029__x002C_XMLELEMENT_x0028__x0022_INTERNALCLIENTRE
F_x0022__x002C_INTCLIENTREF_x0029__x002C_XMLELEMENT_x0028__x0022_EXTERNALPOLICYR
EF_x0022__x002C_EXTPOLICYREF_x0029__x002C_XMLELEMENT_x0028__x0022_EXTERNALCLASSR
EF_x0022__x002C_EXTCLASSREF_x0029__x002C_XMLELEMENT_x0028__x0022_INCEPTIONDATE_x
0022__x002C_COVER_DATE_FROM_x0029__x002C_XMLELEMENT_x0028__x0022_EXPIRYD>
<Policy><TransactionCode>New</TransactionCode><InternalClientRef>Test1</Internal
ClientRef><ExternalPolicyRef>LL</ExternalPolicyRef><ExternalClassRef>0001</Exter
nalClassRef><InceptionDate>2008-01-24</InceptionDate><ExpiryDate>2009-01-24</Exp
iryDate><PolicyNumber>SSLL000004</PolicyNumber><Base>2029.04</Base><BaseGST>213.
58</BaseGST><FireLevy>106.40</FireLevy><StampDuty>197.36</StampDuty></Policy>
</XMLELEMENT_x0028__x0022_POLICY_x0022__x002C_XMLELEMENT_x0028__x0022_TRANSACTIO
NCODE_x0022__x002C_TRANCODE_x0029__x002C_XMLELEMENT_x0028__x0022_INTERNALCLIENTR
EF_x0022__x002C_INTCLIENTREF_x0029__x002C_XMLELEMENT_x0028__x0022_EXTERNALPOLICY
REF_x0022__x002C_EXTPOLICYREF_x0029__x002C_XMLELEMENT_x0028__x0022_EXTERNALCLASS
REF_x0022__x002C_EXTCLASSREF_x0029__x002C_XMLELEMENT_x0028__x0022_INCEPTIONDATE_
x0022__x002C_COVER_DATE_FROM_x0029__x002C_XMLELEMENT_x0028__x0022_EXPIRYD>
<XMLELEMENT_x0028__x0022_CLIENT_x0022__x002C_XMLELEMENT_x0028__x0022_NAME_x0022_
_x002C_INSURED_NAME_x0029__x002C_XMLELEMENT_x0028__x0022_POSTALADDRESS1_x0022__x
002C_INSURED_ADDR1_x0029__x002C_XMLELEMENT_x0028__x0022_POSTALADDRESS2_x0022__x0
02C_INSURED_ADDR2_x0029__x002C_XMLELEMENT_x0028__x0022_POSTALADDRESS3_x0022__x00
2C_INSURED_ADDR3_x0029__x0029_>
<Client><Name>10 LOC
QUOTE</Name><PostalAddress1>28 O'connell
street</PostalAddress1><PostalAddress2>ACTON</PostalAddress2><PostalAddress3>ACT
</PostalAddress3></Client>
</XMLELEMENT_x0028__x0022_CLIENT_x0022__x002C_XMLELEMENT_x0028__x0022_NAME_x0022
__x002C_INSURED_NAME_x0029__x002C_XMLELEMENT_x0028__x0022_POSTALADDRESS1_x0022__
x002C_INSURED_ADDR1_x0029__x002C_XMLELEMENT_x0028__x0022_POSTALADDRESS2_x0022__x
002C_INSURED_ADDR2_x0029__x002C_XMLELEMENT_x0028__x0022_POSTALADDRESS3_x0022__x0
02C_INSURED_ADDR3_x0029__x0029_>
</Policy>
</Import>
PL/SQL procedure successfully completed.
if I used DBMS_XMLGEN, i can produce the result that i wanted, but i couldn't produce the next rowtag for <Client> cuz it need another query to be executed.
block 2:
DECLARE
ctx DBMS_XMLGEN.ctxHandle;
xml CLOB;
xmlc VARCHAR2(4000);
off INTEGER :=1;
len INTEGER :=4000;
BEGIN
ctx := DBMS_XMLGEN.newContext('SELECT ILD_TRANCODE as "TransactionCode"
, ILD_INTCLIENTREF as "InternalClientRef"
, ILD_EXTPOLICYREF as "ExternalPolicyRef"
, ILD_EXTCLASSREF as "ExternalClassRef"
, ILD_COVER_DATE_FROM as "InceptionDate"
, ILD_COVER_DATE_TO as "ExpiryDate"
, ILD_POLICYNO as "PolicyNumber"
, ILD_GWP as "Base"
, ILD_GST as "BaseGST"
, ILD_FSL as "FireLevy"
, ILD_SD as "StampDuty"
FROM IN_LANDLORD_DATA');
DBMS_XMLGen.setRowsetTag(ctx, 'Import');
DBMS_XMLGen.setRowTag(ctx, 'Policy');
DBMS_OUTPUT.PUT_LINE(ctx);
xml := DBMS_XMLGEN.getXML(ctx);
DBMS_XMLGEN.closeContext(ctx);
DBMS_LOB.READ(xml, len, off, xmlc);
DBMS_OUTPUT.PUT_LINE(xmlc);
END;
/
Output:
<Import>
<Policy>
<TransactionCode>New</TransactionCode>
<InternalClientRef>Test1</InternalClientRef>
<ExternalPolicyRef>LL</ExternalPolicyRef>
<ExternalClassRef>0001</ExternalClassRef>
<InceptionDate>2008-01-24</InceptionDate>
<ExpiryDate>2009-01-24</ExpiryDate>
<PolicyNumber>SSLL000004</PolicyNumber>
<Base>2029.04</Base>
<BaseGST>213.58</BaseGST>
<FireLevy>106.40</FireLevy>
<StampDuty>197.36</StampDuty>
</Policy>
</Import>
These are the actual result that i wanted:
<Import>
<Policy>
<TransactionCode>New</TransactionCode>
<InternalClientRef>Test1</InternalClientRef>
<ExternalPolicyRef>LL</ExternalPolicyRef>
<ExternalClassRef>0001</ExternalClassRef>
<InceptionDate>2008-01-24</InceptionDate>
<ExpiryDate>2009-01-24</ExpiryDate>
<PolicyNumber>SSLL000004</PolicyNumber>
<Base>2029.04</Base>
<BaseGST>213.58</BaseGST>
<FireLevy>106.40</FireLevy>
<StampDuty>197.36</StampDuty>
</Policy>
<Client>
<Name>AUSTBROKERS RWA</Name>
<PostalAddress1>28 O'connell street</PostalAddress1>
<PostalAddress2>ACTON</PostalAddress2>
<PostalAddress3>ACT</PostalAddress3>
</Client>
</Import>
As i'm very new to oracle xml, really need some advise on this, please.
Thanks in advance.
[Mod-edit: Frank added code-tags.]
[Updated on: Thu, 14 February 2008 02:10] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Generate XML format [message #300674 is a reply to message #300050] |
Sun, 17 February 2008 14:13  |
mchadder
Messages: 224
Registered: May 2005
Location: UK
|
Senior Member |
|
|
Hello.
A few questions for you. Since Policy and Client are peers, contained within the Import tag, what is your expected output for multiple rows in test_data?
If you expect (as I infer from your expected output)
<Import>
<Policy>..</Policy>
<Client>..</Client>
<Policy>..</Policy>
<Client>..</Client>
</Import>
Then how are you going to relate a <Client> to a <Policy>?
I would have expected that you have something like :
<Import>
<Policy>
..
<Client>..</Client>
</Policy>
</Import>
Or add some kind of client "id" to both Client and Policy (or is this what InternalClientRef is supposed to signify?).
This would then model the relationship between Policy and Client.
However, if it really is the first structure you require, and are relying on positional structure for the relationship, then a query similar to the following should be what you require :
SELECT
XMLELEMENT("Import",
XMLAGG(
XMLCONCAT(
XMLELEMENT("Policy",
XMLFOREST(t.trancode "TransactionCode",
t.intclientref "InternalClientRef",
t.extpolicyref "ExternalPolicyRef",
t.EXTCLASSREF "ExternalClassRef",
t.COVER_DATE_FROM "InceptionDate",
t.COVER_DATE_TO "ExpiryDate",
t.POLICYNO "PolicyNumber",
t.gwp "Base",
t.gst "BaseGST",
t.fsl "FireLevy",
t.sd "StampDuty"
)
),
XMLELEMENT("Client",
XMLFOREST(t.insured_name "Name",
t.INSURED_ADDR1 "PostalAddress1",
t.INSURED_ADDR2 "PostalAddress2",
t.INSURED_ADDR3 "PostalAddress3"
)
)
)
)
)
FROM test_data t
/
The output from your test data is then :
<Import>
<Policy>
<TransactionCode>New</TransactionCode>
<InternalClientRef>Test1</InternalClientRef>
<ExternalPolicyRef>LL</ExternalPolicyRef>
<ExternalClassRef>0001</ExternalClassRef>
<InceptionDate>2008-01-24</InceptionDate>
<ExpiryDate>2009-01-24</ExpiryDate>
<PolicyNumber>SSLL000004</PolicyNumber>
<Base>2029.04</Base>
<BaseGST>213.58</BaseGST>
<FireLevy>106.40</FireLevy>
<StampDuty>197.36</StampDuty>
</Policy>
<Client>
<Name>10 LOC QUOTE</Name>
<PostalAddress1>28 O'connell street</PostalAddress1>
<PostalAddress2>ACTON</PostalAddress2>
<PostalAddress3>ACT</PostalAddress3>
</Client>
</Import>
And, as mentioned, the output for multiple rows is :
<Import>
<Policy>
<TransactionCode>New</TransactionCode>
<InternalClientRef>Test1</InternalClientRef>
<ExternalPolicyRef>LL</ExternalPolicyRef>
<ExternalClassRef>0001</ExternalClassRef>
<InceptionDate>2008-01-24</InceptionDate>
<ExpiryDate>2009-01-24</ExpiryDate>
<PolicyNumber>SSLL000004</PolicyNumber>
<Base>2029.04</Base>
<BaseGST>213.58</BaseGST>
<FireLevy>106.40</FireLevy>
<StampDuty>197.36</StampDuty>
</Policy>
<Client>
<Name>10 LOC QUOTE</Name>
<PostalAddress1>28 O'connell street</PostalAddress1>
<PostalAddress2>ACTON</PostalAddress2>
<PostalAddress3>ACT</PostalAddress3>
</Client>
<Policy>
<TransactionCode>New</TransactionCode>
<InternalClientRef>Test1</InternalClientRef>
<ExternalPolicyRef>LL</ExternalPolicyRef>
<ExternalClassRef>0001</ExternalClassRef>
<InceptionDate>2008-01-24</InceptionDate>
<ExpiryDate>2009-01-24</ExpiryDate>
<PolicyNumber>SSLL000004</PolicyNumber>
<Base>2029.04</Base>
<BaseGST>213.58</BaseGST>
<FireLevy>106.40</FireLevy>
<StampDuty>197.36</StampDuty>
</Policy>
<Client>
<Name>10 LOC QUOTE</Name>
<PostalAddress1>28 O'connell street</PostalAddress1>
<PostalAddress2>ACTON</PostalAddress2>
<PostalAddress3>ACT</PostalAddress3>
</Client>
</Import>
Regards
|
|
|
|
| Re: Generate XML format [message #300700 is a reply to message #300674] |
Sun, 17 February 2008 21:55 |
mrzali
Messages: 3
Registered: February 2008
|
Junior Member |
|
|
Hi mchadder,
Thanks for the reply. As for your question, the record already indicated that the client is under that policy. I think I know what u mean, but later in the requirements, there will be some keys to link between those two.
Anyway, I did try to use the xmlelement, xmlagg, xmlforest as other alternatives. Yes it produced the result that I wanted, but it displays in one line only. Not as what dbms_xmlgen produced. Dbms_xmlgen displays the output in xml format. So I use your solution n run it at my end, the output is exactly as I wanted but it still displayed in one row. Is your output display in one row or u need to do some commands to make it printed in the xml format?
Thanks again. I really appreciated u answering my question.
|
|
|
|
| Re: Generate XML format [message #300903 is a reply to message #300700] |
Mon, 18 February 2008 11:01 |
mchadder
Messages: 224
Registered: May 2005
Location: UK
|
Senior Member |
|
|
Ohhhh... I see. You're talking about "pretty" printing. That's just the way that Oracle formats the output, but it's still just a single XMLTYPE instance. You can find more information about that here, but it basically just amounts to a .EXTRACT('/') or similar on the end, i.e.
SELECT XMLELEMENT("...").EXTRACT('/') FROM ...
Regards
|
|
|
|
| Re: Generate XML format [message #301338 is a reply to message #300903] |
Wed, 20 February 2008 02:05 |
mrzali
Messages: 3
Registered: February 2008
|
Junior Member |
|
|
Thanks man.
Now i know, it's about preety printing. I'll study them n try them out later. Thanks for the fast feedback. Appreciated it so much.
|
|
|
|
| Re: Generate XML format [message #461597 is a reply to message #301338] |
Sat, 19 June 2010 10:37 |
kin2010
Messages: 1
Registered: June 2010
|
Junior Member |
|
|
Hi,
I have to say MANY THANKS to mrzali and mchadder.
Although this was a post at year 2008, I am exactly having the question of mrzali now (at Jun/2010) and have searched around the Internet for the "pretty printing" solution of Oracle SQLX output (before that, I didn't know the key word of "pretty printing"). Actually I have found a lot of Web Sites but not getting the solution until I see this post.
I did not have an OraFAQ id before. I registered one now mainly for saying THANKS to mrzali and mchadder. Wish you guys all the best!
Regards,
Kin
|
|
|
|
Goto Forum:
Current Time: Sun Jan 26 01:00:25 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
