Home » Developer & Programmer » JDeveloper, Java & XML » Generate XML format (Oracle 10, Windows)
Generate XML format [message #300050] Thu, 14 February 2008 01:56 Go to next message
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&apos;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&apos;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 #300054 is a reply to message #300050] Thu, 14 February 2008 02:11 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
I added code tags and reformatted your code.
Please do so yourself next time
Re: Generate XML format [message #300674 is a reply to message #300050] Sun, 17 February 2008 14:13 Go to previous messageGo to next message
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&apos;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&apos;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&apos;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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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


Previous Topic: parsing xml
Next Topic: How to make publicreportclient work?
Goto Forum:
  


Current Time: Fri Mar 29 03:00:05 CDT 2024