XML output using oracle sql

Question

Please guide me how to get below output using oracle sql query and output should be in one row and one column(single row column).

<Row><Cell><Data ss:Type="String">SMITH</Data></Cell><Cell><Data ss:Type="String">800</Data></Cell></Row> <Row><Cell><Data ss:Type="String">ALLEN</Data></Cell><Cell><Data ss:Type="String">1600</Data></Cell></Row>

i tried using sql statement but i'm getting multiple rows.

select 
'<Row>'||XMLELEMENT("Cell",XMLELEMENT("Data",xmlattributes('String' as  "ss:Type"),ename) )||
  XMLELEMENT("Cell",XMLELEMENT("Data",xmlattributes('String' as  "ss:Type"),sal)) ||'</Row>' as "Result" from emp;

Thanks in advance

notionquest · Accepted Answer · 2016-09-10 10:17:40Z

1

You can generate the XML using the standard package DBMS_XMLGEN.GETXML.

Note:-

You should have sufficient privilege to run the package. If you are using Oracle local database with admin access, you shouldn't have any problem.

Syntax:-

select dbms_xmlgen.getxmltype(Your select query here) from dual;

Example:-

SELECT dbms_xmlgen.getxmltype('select first_name, last_name, phone_number, email from employees where employee_id in (100,101)')
FROM dual;

If you need only one row in output, you can use rownum = 1 in where clause to limit the number of rows in the output.

Convert SQL result to XML using standard package

edited Sep 10, 2016 at 10:17

user330315

answered Sep 10, 2016 at 10:12

notionquest

39.4k6 gold badges120 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kamil Gosciminski Over a year ago

OP seems to need attributes as well. Does your example support this case?

notionquest Over a year ago

It doesn't return the attribute type. However, DBMS_XMLGEN.GETXMLTYPE generates the XML document and returns it as a sys.XMLType. I believe you don't need a type in output as XML as its own type. You can define a schema (XSD) and consume the data in other process.

MT0 · Accepted Answer · 2016-09-10 23:43:03Z

0

Use XMLAGG() to aggregate the rows:

SELECT  XMLAGG(
          XMLELEMENT(
            "Row",
            XMLELEMENT(
              "Cell",
              XMLELEMENT("Data",xmlattributes('String' as  "ss:Type"),ename)
            ),
            XMLELEMENT(
              "Cell",
              XMLELEMENT("Data",xmlattributes('String' as  "ss:Type"),sal)
            )
          )
        ).getClobVal() AS "Result"
FROM    emp;

edited Sep 10, 2016 at 23:43

answered Sep 10, 2016 at 23:37

MT0

173k12 gold badges70 silver badges136 bronze badges

Collectives™ on Stack Overflow

XML output using oracle sql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related