I am building a sql query with Oracle 10g, And would like to get sql output with below xml format:
<Users>
<User>
<USRID>1234</USRID>
<USERNAME>ABCD</USERNAME>
<ROLES>
<ROLE>
<ROLEID>1</ROLEID>
<ROLENAME>GlobalAdministrator</ROLENAME>
<ISDEFAULTROLE>1</ISDEFAULTROLE>
</ROLE>
<ROLE>
<ROLEID>2</ROLEID>
<ROLENAME>Administrator</ROLENAME>
<ISDEFAULTROLE>0</ISDEFAULTROLE>
</ROLE>
</ROLES>
</User>
<User>
<USRID>2312</USRID>
<USERNAME>XUX</USERNAME>
<ROLES>
<ROLE>
<ROLEID>3</ROLEID>
<ROLENAME>AccountManager</ROLENAME>
<ISDEFAULTROLE>1</ISDEFAULTROLE>
</ROLE>
<ROLE>
<ROLEID>5</ROLEID>
<ROLENAME>Approver</ROLENAME>
<ISDEFAULTROLE>0</ISDEFAULTROLE>
</ROLE>
</ROLES>
</User>
</Users>
Sql I just build:
SELECT XMLElement("Users", XMLAgg(XMLElement("User", XMLElement("USRID",u.USRID) , XMLElement("USERNAME",u.USERNAME) , XMLElement("Roles", SELECT xmlagg(XMLElement("Role", XMLElement("ROLEID",ur.ROLEID), XMLElement("ROLENAME",r.NAME), xmlelement("ISDEFAULTROLE",ur.PRIMARY)) FROM USERROLE ur,role r WHERE u.USRID = ur.USRID AND r.ROLEID = ur.ROLEID))))).getClobVal() FROM usr u
But its giving below error: ORA-00936: missing expression 00936. 00000 - "missing expression" *Cause:
*Action: Error at Line: 26 Column: 1
How can I correct this/what would be the proper sql to achieve desired xml format ?