-4

in the Oracle database I have a table which contains simple attributes such as:

name first name age

Goal: I would like to recover this data in XSD format.

are there SQL functions for that or java libraries that do that?

Thanks in advance

Community
  • 1
  • 1
H_A_92 s7
  • 1
  • 1
    Please provide sample data and expected results to clarify your requirement. – GMB Jan 27 '20 at 21:12
  • Do you mean in XML format or do you really mean XSD format? – MT0 Jan 27 '20 at 21:16
  • Xml or xsd format, if you had a solution, i'll take it. – H_A_92 s7 Jan 27 '20 at 21:30
  • 3
    You can't just say "XML or XSD, ... I'll take it" they may both be XML documents but they fulfil a very different function. One contains data (XML) and one describes how the data should be structured (XSD). – MT0 Jan 27 '20 at 21:59

1 Answers1

3

XSD stands for XML Schema Definition and is used to describe an XML document's structure, constraints, data types. So you would put data in an XML document but the structure/format of that document (without any data) would be described by an associated XSD.

Since you are talking about data, you probably want an XML document (and not an XSD) and can use Oracle's XML functions:

Oracle Setup:

CREATE TABLE table_name ( first_name, last_name, date_of_birth ) AS
SELECT 'Alice', 'Adams', DATE '1970-01-01' FROM DUAL UNION ALL
SELECT 'Beryl', 'Booth', DATE '1980-01-01' FROM DUAL UNION ALL
SELECT 'Carol', 'Cross', DATE '1990-01-01' FROM DUAL;

Query:

SELECT XMLELEMENT(
         "People",
         XMLAGG(
           XMLELEMENT( 
           "Person",
             XMLFOREST(
               first_name AS "FirstName",
               last_name  AS "LastName",
               EXTRACT( YEAR FROM ( SYSDATE - date_of_birth ) YEAR TO MONTH ) AS "Age"
             )
           )
         )
       ) AS XML
FROM   table_name

Output:

| XML                                                                                                                                                                                                                                                                           |
| :---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| <People><Person><FirstName>Alice</FirstName><LastName>Adams</LastName><Age>50</Age></Person><Person><FirstName>Beryl</FirstName><LastName>Booth</LastName><Age>40</Age></Person><Person><FirstName>Carol</FirstName><LastName>Cross</LastName><Age>30</Age></Person></People> |

db<>fiddle here

If you really want to generate an XSD then you can do that using the same functions as an XSD is, itself, an XML document; but its not something you put data into.

MT0
  • 143,790
  • 11
  • 59
  • 117
  • Thanks for your response but what if i dont know the content of my table ? Or i have Many tables? Is there a more simple way ? – H_A_92 s7 Jan 27 '20 at 21:49
  • 3
    @H_A_92s7 Please [edit](https://stackoverflow.com/review/suggested-edits/25201287) your question to give us more details of what you are actually asking. You say in the question that you have a table and give us fields that are in there and then you say in the comment above that you don't know what columns/table you have; which is it? And what do you mean a more simple way? The SQL query above is about as simple as you are going to get for generating a single XML document. – MT0 Jan 27 '20 at 21:56