Trying to produce xml data file where a set of users have many products and org units, outcome should look like:
<TopTree>
<User>
<UserStatus>1</UserStatus>
<Access>
<AccessSetting><Product code="AAA"></Product><ActivateDate>2012-05-01</ActivateDate></AccessSetting>
<AccessSetting><Product code="BBB"<</Product><ActivateDate>2012-05-01</ActivateDate></AccessSetting>
<AccessSetting><Product code="CCC"></Product><ActivateDate>2012-05-01</ActivateDate></AccessSetting>
</Access>
<PersonInfo>
<Name>
<FirstName><![CDATA[John]]></FirstName>
<LastName><![CDATA[Smith]]></LastName>
</Name>
<ContactInfo>
<Email Label="business">john@businessemail.com</Email>
</ContactInfo>
</PersonInfo>
<Security>
<Credentials>
<UserName>jsmith</UserName>
<Password>jsmith123</Password>
</Credentials>
<OrganizationUnits>
<OrgUnit code="Engineering"></OrgUnit>
<Roles>
<UnitRole>INT_I</UnitRole>
<UnitRole>REP_A</UnitRole>
<UnitRole>OIT_S/UnitRole>
</Roles>
</OrganizationUnits>
</Security>
</User>
</TopTree>
am using the query:
SELECT
xmlElement("TopTree",
xmlAgg(xmlElement("User"
,xmlElement("UserStatus", DECODE(users.term_date, NULL, 1, 0))
,xmlElement("Access"
,xmlAgg(xmlElement("AccessSetting"
,xmlElement("Product", xmlAttributes(acc.product_access as "code"))
,xmlForest(acc.activate_date as "ActivateDate"
, acc.deactivate_date as "DeactivateDate")
)
)
)
,xmlElement("PersonInfo"
,xmlElement("Name"
,xmlElement("FirstName", xmlCDATA(users.first_name))
,xmlElement("LastName", xmlCDATA(users.last_name)))
,xmlElement("ContactInfo"
,xmlelement("Email", xmlattributes('business' as "Label"), users.email)
) -- end of ContactInfo
) -- end of PersonInfo
,xmlElement("Security"
,xmlElement("Credentials"
,xmlelement("UserName", users.login_key)
,xmlelement("Password", users.Password)
)
,xmlElement("OrganizationUnits"
,xmlElement("OrgUnit", xmlattributes('Engineering' as "code"))
,xmlElement("Roles", xmlagg(xmlElement("UnitRole", org.org_unit_role)))
) -- end of OrganizationUnits
) -- end of Security
) -- end of User
) -- end of xmlAgg User
) -- end of TopTree
FROM user_data users
,user_access_v acc
,org_unit_roles_v org
WHERE users.person_id = acc.person_id
AND users.org_role = org.org_role
GROUP BY DECODE(users.term_date, NULL, 1, 0)
,users.first_name
,users.last_name
,users.email
,users.login_key
,users.password
;
but I'm getting
<TopTree>
<User>
<UserStatus>1</UserStatus>
<Access>
<AccessSetting><Product code="AAA"></Product><ActivateDate>2012-05-01</ActivateDate></AccessSetting>
<AccessSetting><Product code="AAA"></Product><ActivateDate>2012-05-01</ActivateDate></AccessSetting>
<AccessSetting><Product code="AAA"></Product><ActivateDate>2012-05-01</ActivateDate></AccessSetting>
<AccessSetting><Product code="BBB"></Product><ActivateDate>2012-05-01</ActivateDate></AccessSetting>
<AccessSetting><Product code="BBB"></Product><ActivateDate>2012-05-01</ActivateDate></AccessSetting>
<AccessSetting><Product code="BBB"<</Product><ActivateDate>2012-05-01</ActivateDate></AccessSetting>
<AccessSetting><Product code="CCC"></Product><ActivateDate>2012-05-01</ActivateDate></AccessSetting>
<AccessSetting><Product code="CCC"></Product><ActivateDate>2012-05-01</ActivateDate></AccessSetting>
<AccessSetting><Product code="CCC"></Product><ActivateDate>2012-05-01</ActivateDate></AccessSetting>
</Access>
<PersonInfo>
<Name>
<FirstName><![CDATA[John]]></FirstName>
<LastName><![CDATA[Smith]]></LastName>
</Name>
<ContactInfo>
<Email Label="business">john@businessemail.com</Email>
</ContactInfo>
</PersonInfo>
<Security>
<Credentials>
<UserName>jsmith</UserName>
<Password>jsmith123</Password>
</Credentials>
<OrganizationUnits>
<OrgUnit code="Engineering"></OrgUnit>
<Roles>
<UnitRole>INT_I</UnitRole>
<UnitRole>INT_I</UnitRole>
<UnitRole>INT_A</UnitRole>
<UnitRole>REP_A</UnitRole>
<UnitRole>REP_A</UnitRole>
<UnitRole>REP_A</UnitRole>
<UnitRole>OIT_S/UnitRole>
<UnitRole>OIT_S</UnitRole>
<UnitRole>OIT_S</UnitRole>
</Roles>
</OrganizationUnits>
</Security>
</User>
</TopTree>
Have tried different combinations of XMLAGG but can't seem to get it to work when I have more than one sub-query