CODEWITHSUNDEEP
javapostgresql
Siten
Siten

Reputation: 4533

Create XML from PostgreSQL?

How can I create an XML file from PostgreSQL?

Upvotes: 0

Views: 15511

Answers (4)

Royce
Royce

Reputation: 170

This is my precise example to the question.

CREATE TABLE xml_mobile_data AS SELECT
xml $$
    <rows>
        <row id="1">
            <model>Samsung Galaxy Note 9</model>
            <price>$3,000.00</price>
        </row>
        <row id="2">
            <model>iPhone X</model>
            <price>$1,000.00</price>
        </row>
        <row id="3">
            <model>Samsung Galaxy S9+</model>
            <price>$999.00</price>
        </row>
        <row id="4">
            <model>Huawei P20 Pro</model>
            <price>$2,000.00</price>
        </row>
        <row id="5">
            <model>Google Pixel XL 3</model>
            <price>$899.00</price>
        </row>
    </rows>
$$ AS mobile_data;

We can retrieve this in row format. 

SELECT xmltable.*
FROM xml_mobile_data,
    XMLTABLE('/rows/row' 
             PASSING mobile_data 
             COLUMNS
                ordinality FOR ORDINALITY,
                model_id INT PATH '@id',
                model TEXT PATH 'model',
                "price" MONEY
    );

Another way is the usage of WITH QUERY CTE(COMMON TABLE EXPRESSIONS)

WITH xmldata(data) AS (VALUES ('
<rows>
    <row id="1">
        <model>Samsung Galaxy Note 9</model>
        <price>$3,000.00</price>
    </row>
    <row id="2">
        <model>iPhone X</model>
        <price>$1,000.00</price>
    </row>
    <row id="3">
        <model>Samsung Galaxy S9+</model>
        <price>$999.00</price>
    </row>
    <row id="4">
        <model>Huawei P20 Pro</model>
        <price>$2,000.00</price>
    </row>
    <row id="5">
        <model>Google Pixel XL 3</model>
        <price>$899.00</price>
    </row>
</rows>
'::XML))
SELECT xmltable.*
FROM xmldata, 
        XMLTABLE('/rows/row' 
             PASSING data 
             COLUMNS
                ordinality FOR ORDINALITY,
                model_id INT PATH '@id',
                model TEXT PATH 'model',
                "price" MONEY
    );

Upvotes: 1

MAbraham1
MAbraham1

Reputation: 1768

Here is a stored procedure (called function in PostgresSQL) that returns XML from a simple query.

CREATE OR REPLACE FUNCTION getXml()
  RETURNS xml 
  AS
$BODY$
DECLARE myXml xml;
BEGIN 
    SELECT * INTO myXml FROM query_to_xml_and_xmlschema('SELECT id FROM someTable', true, true, 'myProject.mySchema');
    RETURN myXml;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION getXml() OWNER TO postgres;

Call the function by the select statement:

SELECT getXml();

The function will return a schema in XSD schema notation, and your data in XML rendered as an 'XML forest'.

Upvotes: 1

Udayanga
Udayanga

Reputation: 81

Lets say you need to create following kind of XML

<Agents>
<agent id="32"/>
<agent id="33"/>
<agent id="34"/>
</Agents>

Then just run following query;

SELECT
    XMLFOREST(tb1."xml-val" AS "Agents")
FROM
    (
        SELECT
            XMLAGG(XMLELEMENT(NAME agent ,XMLATTRIBUTES(t.id AS id))) AS 
            "xml-val"
        FROM
            test t
    ) AS tb1;

:)

Upvotes: 8

user330315
user330315

Reputation:

By using the XML functions:

http://www.postgresql.org/docs/current/static/functions-xml.html#AEN15086

Upvotes: 3

Related Questions