| XML Formatting [message #246755] |
Thu, 21 June 2007 14:28  |
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  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
