What's the best way to incrementally build an XML document/string using PL/pgSQL? Consider the following desired XML output:
<Directory>
<Person>
<Name>Bob</Name>
<Address>1234 Main St</Address>
<MagicalAddressFactor1>3</MagicalAddressFactor1>
<MagicalAddressFactor2>8</MagicalAddressFactor2>
<MagicalAddressFactor3>1</MagicalAddressFactor3>
<IsMagicalAddress>Y</IsMagicalAddress>
</Person>
<Person>
<Name>Joshua</Name>
<Address>100 Broadway Blvd</Address>
<MagicalAddressFactor1>2</MagicalAddressFactor1>
<MagicalAddressFactor2>1</MagicalAddressFactor2>
<MagicalAddressFactor3>4</MagicalAddressFactor3>
<IsMagicalAddress>Y</IsMagicalAddress>
</Person>
</Directory>
Where:
How could I generate this with PL/pgSQL using XML functions to ensure a well-formed XML element? Without using XML functions the code would look like this:
DECLARE
v_sql text;
v_rec RECORD;
v_XML xml;
v_factor1 integer;
v_factor2 integer;
v_factor3 integer;
v_IsMagical varchar;
BEGIN
v_XML := '<Directory>';
v_sql := 'select * from person;'
FOR v_rec IN v_sql LOOP
v_XML := v_XML || '<Name>' || v_rec.name || '</Name>' ||
'<Address>' || v_rec.Address || '</Address>';
v_factor1 := get_factor_1(v_rec);
v_factor2 := get_factor_2(v_rec);
v_factor3 := get_factor_3(v_rec);
v_IsMagical := case
when (v_factor1 + v_factor2 + v_factor3) > 10 then
'Y'
else
'N'
end;
v_XML := v_XML || '<MagicalAddressFactor1>' || v_factor1 || '</MagicalAddressFactor1>' ||
'<MagicalAddressFactor2>' || v_factor2 || '</MagicalAddressFactor2>' ||
'<MagicalAddressFactor3>' || v_factor3 || '</MagicalAddressFactor3>' ||
'<IsMagicalAddress>' || v_IsMagical || '</IsMagicalAddress>';
v_XML := v_XML || '</Person>'
END LOOP;
v_XML := v_XML || '</Directory>'
END;
For OP and future readers, consider a general purpose language whenever needed to migrate database content to XML documents. Simply connect via ODBC/OLEDB drivers, retrieve query, and output to XML document. Using OP's needs, calculations can be incorporated into one select query or a stored procedure that returns a resultset and have coding language import records for document building.
Below are open-source solutions including Java where each connect using corresponding PostgreSQL drivers (requiring installation). SQL queries assumes get_factor1(), get_factor2(), get_factor3() are inline database functions and Persons maintain a unique ID in first column.
Java (using the Postgre JDBC driver)
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.OutputKeys;
import java.sql.* ;
import java.util.ArrayList;
import java.io.IOException;
import java.io.File;
import org.w3c.dom.Attr;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
public class SQLtoXML {
public static void main(String[] args) {
String currentDir = new File("").getAbsolutePath();
try {
String url = "jdbc:postgresql://localhost/test";
Properties props = new Properties();
props.setProperty("user","sqluser");
props.setProperty("password","secret");
props.setProperty("ssl","true");
Connection conn = DriverManager.getConnection(url, props);
String url = "jdbc:postgresql://localhost/test?user=sqlduser&password=secret&ssl=true";
Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT name, address, "
+ "get_factor_1(v_rec) As v_factor1, "
+ "get_factor_2(v_rec) As v_factor2, "
+ "get_factor_3(v_rec) As v_factor3, "
+ " CASE WHEN (get_factor_1(v_rec) + "
+ " get_factor_2(v_rec) + "
+ " get_factor_3(v_rec)) > 10 "
+ " THEN 'Y' ELSE 'N' END As v_isMagical "
+ " FROM Persons;");
// Write to XML document
DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();
DocumentBuilder docBuilder = docFactory.newDocumentBuilder();
Document doc = docBuilder.newDocument();
// Root element
Element rootElement = doc.createElement("Directory");
doc.appendChild(rootElement);
// Export table data
ResultSetMetaData rsmd = rs.getMetaData();
int columnsNumber = rsmd.getColumnCount();
while (rs.next()) {
// Data rows
Element personNode = doc.createElement("Person");
rootElement.appendChild(personNode);
Element nameNode = doc.createElement("name");
nameNode.appendChild(doc.createTextNode(rs.getString(2)));
personNode.appendChild(nameNode);
Element addressNode = doc.createElement("address");
addressNode.appendChild(doc.createTextNode(rs.getString(3)));
personNode.appendChild(addressNode);
Element magicaladd1Node = doc.createElement("MagicalAddressFactor1");
magicaladd1Node.appendChild(doc.createTextNode(rs.getString(4)));
personNode.appendChild(magicaladd1Node);
Element magicaladd2Node = doc.createElement("MagicalAddressFactor2");
magicaladd2Node.appendChild(doc.createTextNode(rs.getString(5)));
personNode.appendChild(magicaladd2Node);
Element magicaladd3Node = doc.createElement("MagicalAddressFactor3");
magicaladd3Node.appendChild(doc.createTextNode(rs.getString(6)));
personNode.appendChild(magicaladd3Node);
Element isMagicalNode = doc.createElement("IsMagicalAddress");
isMagicalNode.appendChild(doc.createTextNode(rs.getString(7)));
personNode.appendChild(isMagicalNode);
}
rs.close();
stmt.close();
conn.close();
// Output content to xml file
TransformerFactory transformerFactory = TransformerFactory.newInstance();
Transformer transformer = transformerFactory.newTransformer();
transformer.setOutputProperty(OutputKeys.INDENT, "yes");
transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "2");
DOMSource source = new DOMSource(doc);
StreamResult result = new StreamResult(new File(currentDir + "\\PostgreXML_java.xml"));
transformer.transform(source, result);
System.out.println("Successfully created xml file!");
} catch (ParserConfigurationException pce) {
System.out.println(pce.getMessage());
} catch (TransformerException tfe) {
System.out.println(tfe.getMessage());
} catch (SQLException err) {
System.out.println(err.getMessage());
}
}
}
Python (Using the Psycopg module)
import psycopg2
import os
import lxml.etree as ET
cd = os.path.dirname(os.path.abspath(__file__))
# DB CONNECTION AND QUERY
db = psycopg2.connect("dbname=test user=postgres")
cur = db.cursor()
cur.execute("SELECT name, address, \
get_factor_1(v_rec) As v_factor1, \
get_factor_2(v_rec) As v_factor2, \
get_factor_3(v_rec) As v_factor3, \
CASE WHEN (get_factor_1(v_rec) + \
get_factor_2(v_rec) + \
get_factor_3(v_rec)) > 10 \
THEN 'Y' ELSE 'N' END As v_isMagical \
FROM Persons;")
# WRITING XML FILE
root = ET.Element('Directory')
for row in cur.fetchall():
personNode = ET.SubElement(root, "Person")
ET.SubElement(personNode, "Name").text = row[1]
ET.SubElement(personNode, "Address").text = row[2]
ET.SubElement(personNode, "MagicalAddressFactor1").text = row[3]
ET.SubElement(personNode, "MagicalAddressFactor2").text = row[4]
ET.SubElement(personNode, "MagicalAddressFactor3").text = row[5]
ET.SubElement(personNode, "IsMagicalAddress").text = row[6]
# CLOSE CURSOR AND DATABASE
cur.close()
db.close()
# OUTPUT XML
tree_out = (ET.tostring(root, pretty_print=True, xml_declaration=True, encoding="UTF-8"))
xmlfile = open(os.path.join(cd, 'PostgreXML_py.xml'),'wb')
xmlfile.write(tree_out)
xmlfile.close()
print("Successfully migrated SQL to XML data!")
PHP (using Postgre PDO Driver)
<?php
$cd = dirname(__FILE__);
// create a dom document with encoding utf8
$domtree = new DOMDocument('1.0', 'UTF-8');
$domtree->formatOutput = true;
$domtree->preserveWhiteSpace = false;
# Opening db connection
$host="root";
$dbuser = "*****";
try {
$dbh = new PDO("pgsql:dbname=$dbname;host=$host", $dbuser, $dbpass);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT name, address,
get_factor_1(v_rec) As v_factor1,
get_factor_2(v_rec) As v_factor2,
get_factor_3(v_rec) As v_factor3,
CASE WHEN (get_factor_1(v_rec) +
get_factor_2(v_rec) +
get_factor_3(v_rec)) > 10
THEN 'Y' ELSE 'N' END As v_isMagical
FROM Persons;";
$STH = $dbh->query($sql);
$STH->setFetchMode(PDO::FETCH_ASSOC);
}
catch(PDOException $e) {
echo $e->getMessage();
exit;
}
/* create the root element of the xml tree */
$xmlRoot = $domtree->createElement("Directory");
$xmlRoot = $domtree->appendChild($xmlRoot);
/* loop query results through child elements */
while($row = $STH->fetch()) {
$personNode = $xmlRoot->appendChild($domtree->createElement('Person'));
$nameNode = $personNode->appendChild($domtree->createElement('Name', $row['name']));
$addNode = $personNode->appendChild($domtree->createElement('Address', $row['address']));
$magadd1Node = $personNode->appendChild($domtree->createElement('MagicalAddressFactor1', $row['v_factor1']));
$magadd2Node = $personNode->appendChild($domtree->createElement('MagicalAddressFactor2', $row['v_factor2']));
$magadd3Node = $personNode->appendChild($domtree->createElement('MagicalAddressFactor3', $row['v_factor3']));
$ismagicalNode = $personNode->appendChild($domtree->createElement('IsMagicalAddress', $row['v_isMagical']));
}
file_put_contents($cd. "/PostgreXML_php.xml", $domtree->saveXML());
echo "\nSuccessfully migrated SQL data into XML!\n";
# Closing db connection
$dbh = null;
exit;
?>
R (using the RPostgreSQL package)
library(RPostgreSQL)
library(XML)
#setwd("C:/path/to/working/folder")
# OPEN DATABASE AND QUERY
drv <- dbDriver("PostgreSQL")
conn <- dbConnect(drv, dbname="tempdb")
df <- sqlQuery(conn, "SELECT name, address,
get_factor_1(v_rec) As v_factor1,
get_factor_2(v_rec) As v_factor2,
get_factor_3(v_rec) As v_factor3,
CASE WHEN (get_factor_1(v_rec) +
get_factor_2(v_rec) +
get_factor_3(v_rec)) > 10
THEN 'Y' ELSE 'N' END As v_isMagical
FROM Persons;")
close(conn)
# CREATE XML FILE
doc = newXMLDoc()
root = newXMLNode("Directory", doc = doc)
# WRITE XML NODES AND DATA
for (i in 1:nrow(df)){
personNode = newXMLNode("Person", parent = root)
nameNode = newXMLNode("name", df$name[i], parent = personNode)
addressNode = newXMLNode("address", df$address[i], parent = personNode)
magicaladdress1Node = newXMLNode("MagicalAddressFactor1", df$v_factor1[i], parent = personNode)
magicaladdress2Node = newXMLNode("MagicalAddressFactor2", df$v_factor2[i], parent = personNode)
magicaladdress3Node = newXMLNode("MagicalAddressFactor3", df$v_factor3[i], parent = personNode)
ismagicalNode = newXMLNode("IsMagicalAddress", df$v_isMagical[i], parent = personNode)
}
# OUTPUT XML CONTENT TO FILE
saveXML(doc, file="PostgreXML_R.xml")
print("Successfully migrated SQL to XML data!")