CODEWITHSUNDEEP
sqlxmloracle-database
Daler Isoev
Daler Isoev

Reputation: 13

Export sql query results to xml data file in oracle

I have an SQL query which returns two columns with data like this:.

State   Name  
------- ---------
Online  Terminal1
Offline Terminal2
Online  Terminal3
Online  Terminal4

Now I want to create an XML file with a SQL query runs. XML file structure must be like this:

<?xml version="1.0" encoding="utf-8" ?>
<Terminallist name="xml data">
   <Value id="0">
      <Terminal>Terminal1</Terminal>
      <State>Online</State>
   </Value>
   <Value id="1">
      <Terminal>Terminal2</Terminal>
      <State>Offline</State>
   </Value>
   <Value id="2">
      <Terminal>Terminal3</Terminal>
      <State>Online</State>
   </Value>
   <Value id="3">
      <Terminal>Terminal4</Terminal>
      <State>Online</State>
   </Value>
</Terminallist>

I want to save XML file to a directory like this c:/file.xml.

Upvotes: 0

Views: 13027

Answers (3)

Clarius
Clarius

Reputation: 1409

Using the Oracle XE database as source data I'm able to export the employees table as XML as follows...

SELECT SYS_XMLAGG(
    SYS_XMLGEN(
        XMLFOREST(employee_id, first_name, last_name, email, phone, hire_date, manager_id, job_title)
    )
) FROM employees;

The XML will look like...

<?xml version="1.0"?>
<ROWSET>
<ROW>
  <EMPLOYEE_ID>107</EMPLOYEE_ID>
  <FIRST_NAME>Summer</FIRST_NAME>
  <LAST_NAME>Payne</LAST_NAME>
  <EMAIL>[email protected]</EMAIL>
  <PHONE>515.123.8181</PHONE>
  <HIRE_DATE>2016-06-07</HIRE_DATE>
  <MANAGER_ID>106</MANAGER_ID>
  <JOB_TITLE>Public Accountant</JOB_TITLE>
</ROW>
<ROW>
  <EMPLOYEE_ID>...

The CSV list of column names for XMLForest can be obtained thus...

SELECT LISTAGG(column_name, ',') FROM user_tab_columns WHERE table_name = 'EMPLOYEES';

Luck.

Upvotes: 0

APC
APC

Reputation: 146199

This solution uses a WITH clause to generate the ID as you want, starting from 0. Using the analytic row_number() function provides a guaranteed sort order in the result set.

NB: XMLRoot() is deprecated as part of the XML/SQL standard but generates the version header you asked for. Find out more.

with cte as (
    select row_number() over (order by name) - 1 as id
           , name
           , state
    from terminals
    )
SELECT xmlroot (
  XMLElement(
    "Terminallist",
    XMLAttributes( 'xml data' as  "name"),
       XMLAgg(XMLElement("Value",
                         XMLAttributes(cte.id as "id"),
                         XMLElement("Terminal",cte.name),
                         XMLElement("State",cte.state)
                         )
      )
      )
    , version '1.0'   )
FROM cte
order by cte.id
/

Here is the output:

<?xml version="1.0"?>
<Terminallist name="xml data">
  <Value id="0">
    <Terminal>Terminal1</Terminal>
    <State>Online</State>
  </Value>
  <Value id="1">
    <Terminal>Terminal2</Terminal>
    <State>Offline</State>
  </Value>
  <Value id="2">
    <Terminal>Terminal3</Terminal>
    <State>Online</State>
  </Value>
  <Value id="3">
    <Terminal>Terminal4</Terminal>
    <State>Online</State>
  </Value>
</Terminallist>

As for writing the output to a file, that depends on how you want to call the SQL. Use SPOOL if you're running it from SQL*Plus and want to save it to a local file. If running from PL/SQL you'll need to use UTL_FILE and write to a directory on the database server.

Upvotes: 0

Naren
Naren

Reputation: 1477

Answer:-

Table name: temptable

Data in table :

enter image description here

Query :-

    SELECT XMLElement("Terminallist ", XMLAttributes('xml data' AS "name"),XMLAgg(XMLElement("value ", XMLAttributes(rownum AS "id"),XMLForest(Terminal,state))))
FROM temptable ;

output :-

<Terminallist name = "xml data">
  <value id = "1">
    <TERMINAL>Terminal2</TERMINAL>
    <STATE>Offline</STATE>
  </value>
  <value id = "2">
    <TERMINAL>Terminal3</TERMINAL>
    <STATE>Online</STATE>
  </value>
  <value id = "3">
    <TERMINAL>Terminal4</TERMINAL>
    <STATE>Online</STATE>
  </value>
</Terminallist>

Thanks Narendar

Upvotes: 3

Related Questions