How can I convert table data to XML?

There is a table:

------------- ALLTAGS ------------- TAGID | COUNT ------------- 1 | 2 2 | 5 3 | 4 -------------

You need to get the following XML:

 <ALLSCOPES> <ResultTag> <Tag_ID>1</Tag_ID> <Tag_Count>2</Tag_Count> </ResultTag> <ResultTag> <Tag_ID>2</Tag_ID> <Tag_Count>5</Tag_Count> </ResultTag> <ResultTag> <Tag_ID>3</Tag_ID> <Tag_Count>4</Tag_Count> </ResultTag> </ALLSCOPES>

I try to do this:

 SELECT (XMLElement("ALLSCOPES", XMLElement("ResultTag", XMLElement("Tag_ID", t.TAG_ID), XMLElement("Tag_Count", t.TAGCOUNT))) ).GetStringVal() as ALLSCOPES FROM ALLTAGS t;

but I get 3 entries like:

 <ALLSCOPES> <ResultTag> <Tag_ID>1</Tag_ID> <Tag_Count>2</Tag_Count> </ResultTag> </ALLSCOPES> <ALLSCOPES> <ResultTag> <Tag_ID>2</Tag_ID> <Tag_Count>5</Tag_Count> </ResultTag> </ALLSCOPES>

etc.

How to solve my problem?

    1 answer 1

    I understand nothing in oracle. I type in google request: SELECT XMLElement , open the first link, look at the documentation and see:

     SELECT XMLElement("Department", XMLAgg(XMLElement("Employee", e.job_id||' '||e.last_name) ORDER BY e.last_name)) AS "Dept_list" FROM hr.employees e WHERE e.department_id = 30 OR e.department_id = 40;

    should give the result:

     Dept_list ------------------ <Department> <Employee>PU_CLERK Baida</Employee> <Employee>PU_CLERK Colmenares</Employee> <Employee>PU_CLERK Himuro</Employee> <Employee>PU_CLERK Khoo</Employee> <Employee>HR_REP Mavris</Employee> <Employee>PU_MAN Raphaely</Employee> <Employee>PU_CLERK Tobias</Employee> </Department>

    Seem to be? Seem to be. We are trying to adapt to your request:

     SELECT (XMLElement("ALLSCOPES", XMLAgg(XMLElement("ResultTag", XMLElement("Tag_ID", t.TAG_ID), XMLElement("Tag_Count", t.TAGCOUNT)))) ).GetStringVal() as ALLSCOPES FROM ALLTAGS t;

    Will try?

    • I checked for the vehicle, yes, it works :) - Mike