Home » Developer & Programmer » JDeveloper, Java & XML » XML Formatting
XML Formatting [message #246755] Thu, 21 June 2007 14:28 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
The Table and Data is uploaded in the file.
I want to Generate XML in the following format.
Basically for a Form_Number and Elabel_number, group by System_ID and userid_type_format, get all the entitlements.

<Entitlement_Template>
<Form_Number>4008</Form_number>
<Elabel_number>042</Elabel_Number>
<System_ID>MFTSBC</System_Id>
<Userid_Type_Format>IDSXS</Userid_type_format>
<Entitlements>
<Resource_Type>NAME</Resource_Type>
<Resource_Entity>*$FULLNAME$*</Resource_Entity>
<Resource_Type>PAS</Resource_Type>
<Resource_Entity>*$PASSWORD,0</Resource_Entity>
...
...
</Entitlements>
<System_ID>MFTSSK</System_Id>
<Userid_Type_Format>IDSXS**</Userid_type_format>
<Entitlements>
<Resource_Type>NAME</Resource_Type>
<Resource_Entity>*$FULLNAME$*</Resource_Entity>
...
</Entitlement_Template>


The furthest I have been able to reach with this query,

SELECT
xmlelement( "Entitlement_Template",
xmlforest( form_number,elabel_number) ,
( SELECT xmlagg(xmlelement("Entitlements",
xmlforest( resource_type,
resource_entity,
resource_entitlement
)
))
FROM x1 ee1
WHERE ee1.form_number = ee2.form_number
AND ee1.elabel_number = ee2.elabel_number
)
).extract('/*') xmldocop
FROM
( SELECT distinct form_number,elabel_number
FROM x1
WHERE form_number=4008
AND elabel_number=042
) ee2;

But not according to what I need.
Please help.
Re: XML Formatting [message #251296 is a reply to message #246755] Thu, 12 July 2007 16:36 Go to previous message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hi there.

Try a query something like this :

SELECT XMLELEMENT("Entitlement_Template",
                  XMLFOREST(a.form_number "Form_Number",
                            a.elabel_number  "Elabel_Number"),
                  XMLAGG(XMLFOREST(a.system_id  "System_ID",
                                   a.userid_type_format "Userid_Type_Format",
                                   ( SELECT XMLAGG(XMLFOREST(b.resource_type  "Resource_Type",
                                                             b.resource_entity  "Resource_Entity"))
                                       FROM x1 b
                                      WHERE b.form_number = a.form_number
                                        AND b.elabel_number = a.elabel_number
                                        AND b.system_id     = a.system_id
                                        AND b.userid_type_format = a.userid_type_format
                                   ) "Entitlements"
                                  )
                        )                                              
                 )
FROM ( SELECT DISTINCT t.form_number, t.elabel_number, t.system_id, t.userid_type_format
         FROM x1 t ) a
GROUP BY a.form_number, a.elabel_number
/

It should give you the output you require :
<Entitlement_Template>
  <Form_Number>4008</Form_Number>
  <Elabel_Number>42</Elabel_Number>
  <System_ID>MFRFRA</System_ID>
  <Userid_Type_Format>IDSX***</Userid_Type_Format>
  <Entitlements>
    <Resource_Type>NAME</Resource_Type>
    <Resource_Entity>*$FULLNAME$*</Resource_Entity>
    <Resource_Type>PASS</Resource_Type>
    <Resource_Entity>*$PASSWORD$*</Resource_Entity>
    <Resource_Type>DFLTG</Resource_Type>
    <Resource_Entity>BNKDKUT</Resource_Entity>
    <Resource_Type>OWNER</Resource_Type>
    <Resource_Entity>BNK0KCC</Resource_Entity>
    <Resource_Type>WORKATTR_WANAME</Resource_Type>
    <Resource_Entity>4008042.RPL-----.COMMERCL.COLL----</Resource_Entity>
    <Resource_Type>WORKATTR_WABLDG</Resource_Type>
    <Resource_Entity>*$GEID$*</Resource_Entity>
  </Entitlements>
  <System_ID>MFTSBC</System_ID>
  <Userid_Type_Format>IDSX***</Userid_Type_Format>
  <Entitlements>
    <Resource_Type>NAME</Resource_Type>
    <Resource_Entity>*$FULLNAME$*</Resource_Entity>
    <Resource_Type>PAS</Resource_Type>
    <Resource_Entity>*$PASSWORD$*,30,EXP</Resource_Entity>
    <Resource_Type>DEPT</Resource_Type>
    <Resource_Entity>BNKDKUT</Resource_Entity>
    <Resource_Type>FAC</Resource_Type>
    <Resource_Entity>CICSPROD</Resource_Entity>
    <Resource_Type>TSS_ATTRIBUTE</Resource_Type>
    <Resource_Entity>DUFXTR</Resource_Entity>
    <Resource_Type>ELAB</Resource_Type>
    <Resource_Entity>4008042.RPL-----.COMMERCL.COLL----</Resource_Entity>
    <Resource_Type>PROF</Resource_Type>
    <Resource_Entity>BKC2CO01</Resource_Entity>
    <Resource_Type>PROF</Resource_Type>
    <Resource_Entity>BCL2KCL5</Resource_Entity>
    <Resource_Type>PROF</Resource_Type>
    <Resource_Entity>ZCO2CLCC</Resource_Entity>
    <Resource_Type>PROF</Resource_Type>
    <Resource_Entity>BNK2CBI4</Resource_Entity>
    <Resource_Type>GEID</Resource_Type>
    <Resource_Entity>*$GEID$*</Resource_Entity>
    <Resource_Type>SSN</Resource_Type>
    <Resource_Entity>*$SSN$*</Resource_Entity>
  </Entitlements>
  <System_ID>MFTSSK</System_ID>
  <Userid_Type_Format>IDSX***</Userid_Type_Format>
  <Entitlements>
    <Resource_Type>NAME</Resource_Type>
    <Resource_Entity>*$FULLNAME$*</Resource_Entity>
    <Resource_Type>PAS</Resource_Type>
    <Resource_Entity>*$PASSWORD$*,30,EXP</Resource_Entity>
    <Resource_Type>DEPT</Resource_Type>
    <Resource_Entity>BNKDKUT</Resource_Entity>
    <Resource_Type>PID</Resource_Type>
    <Resource_Entity>*$PID$*</Resource_Entity>
    <Resource_Type>FAC</Resource_Type>
    <Resource_Entity>CICSPROD</Resource_Entity>
    <Resource_Type>PROF</Resource_Type>
    <Resource_Entity>NCI2PROD</Resource_Entity>
    <Resource_Type>ELAB</Resource_Type>
    <Resource_Entity>4008042.RPL-----.COMMERCL.COLL----</Resource_Entity>
    <Resource_Type>GEID</Resource_Type>
    <Resource_Entity>*$GEID$*</Resource_Entity>
    <Resource_Type>SSN</Resource_Type>
    <Resource_Entity>*$SSN$*</Resource_Entity>
  </Entitlements>
  <System_ID>MFTSBC</System_ID>
  <Userid_Type_Format>IDSX***_A</Userid_Type_Format>
  <Entitlements>
    <Resource_Type>NAME</Resource_Type>
    <Resource_Entity>*$FULLNAME$*</Resource_Entity>
    <Resource_Type>PAS</Resource_Type>
    <Resource_Entity>*$PASSWORD$*,30,EXP</Resource_Entity>
    <Resource_Type>DEPT</Resource_Type>
    <Resource_Entity>BNKDKUT</Resource_Entity>
    <Resource_Type>TSS_ATTRIBUTE</Resource_Type>
    <Resource_Entity>DUFXTR</Resource_Entity>
    <Resource_Type>FAC</Resource_Type>
    <Resource_Entity>CICSPROD</Resource_Entity>
    <Resource_Type>ELAB</Resource_Type>
    <Resource_Entity>4008042.RPL-----.COMMERCL.COLL----</Resource_Entity>
    <Resource_Type>PROF</Resource_Type>
    <Resource_Entity>BKC2LC02</Resource_Entity>
    <Resource_Type>GEID</Resource_Type>
    <Resource_Entity>*$GEID$*</Resource_Entity>
    <Resource_Type>SSN</Resource_Type>
    <Resource_Entity>*$SSN$*</Resource_Entity>
  </Entitlements>
</Entitlement_Template>

Regards
Previous Topic: XMLTYPE, Error Create Table with XMLTYPE using Oracle 9.0.1.1.1
Next Topic: insert xml data into database table
Goto Forum:
  


Current Time: Fri Dec 02 20:39:09 CST 2016

Total time taken to generate the page: 0.09985 seconds