I’m in Baltimore this weekend at the Postgres East 2008 conference. I’m giving a presentation tomorrow on SQL/XML for developers. Below is the presentation and below that is the code used in the presentation.

Code to follow along:

If you aren’t using javascript, you can get download the presentation or the script from scribd.
Or, to make it easy, just grab this code:

Slide 25

CREATE TABLE EMP
(
  LAST_NAME text,
  EMP_ID integer NOT NULL,
  FIRST_NAME text,
  DEPT_ID integer,
  SALARY integer,
  CONSTRAINT EMP_pkey PRIMARY KEY (EMP_ID)
)
WITH (OIDS=FALSE);
ALTER TABLE EMP OWNER TO xmluser;

Slide 26

INSERT INTO
    EMP(
       LAST_NAME, EMP_ID, FIRST_NAME, DEPT_ID, SALARY)
    VALUES
      ('Blow', 1, 'Joe', 1, 10000),
      ('Head', 2, 'Helmut', 1, 12000),
      ('Jack', 3, 'Noe', 1, 12000),
      ('Hard', 4, 'Blow', 2, 20000),
      ('First', 5, 'Hugo', 2, 21000),
      ('Spaem',6, 'Kingoof', 2, 20000),
      ('Ventura', 7, 'Ace', 3, 35000),
      ('Nickleby', 8, 'Nick', 3, 400000),
      ('Budd', 9, 'Billy', 4, 99000),
      ('Cleaver', 10, 'Wally', 4, 100000) ;

Slide 29

SELECT xmlelement(name main, last_name) from emp;

Slide 30

SELECT xmlelement(name main, last_name),
       xmlelement(name main, first_name)
FROM emp;

Slide 31

SELECT XMLForest(last_name, first_name)
FROM emp;

Slide 32

SELECT xmlelement(name main,
       xmlforest(last_name, first_name) )
FROM emp;

Slide 33

SELECT XMLAgg(
  XMLForest(last_name, first_name) )
FROM emp;

Slide 34

SELECT XMLElement(name main,
       XMLAgg(XMLForest(last_name, first_name) ))
FROM emp;

Slide 35

SELECT XMLElement(name main, XMLAttributes(nextval('t_seq') AS rownum) )
FROM emp;

Slide 36

CREATE TEMP SEQUENCE t_seq;

SELECT xmlelement(name main, xmlattributes(nextval('t_seq') AS rownum),
       xmlforest(last_name, first_name) )
FROM emp;

DROP SEQUENCE t_seq;

Slide 38

SELECT xmlelement(name main,
       xmlforest(last_name || first_name AS fullname, salary) )
FROM emp;

Slide 39

SELECT xmlelement(name main,
       xmlelement(name fullname, xmlattributes(dept_id), last_name || first_name ),
       xmlforest(salary) )
FROM emp;

Slide 40

SELECT xmlelement(name main, xmlcomment('Each Name Goes Here'),
       xmlforest(last_name, first_name))
FROM emp;

Slide 41

SELECT xmlelement(name lastname, last_name),
       xmlelement(name firstname, first_name)
FROM emp;

Slide 42

SELECT xmlconcat(
             xmlelement(name lastname, last_name),
             xmlelement(name firstname, first_name) )
FROM emp;

Slide 43

SELECT xmlelement(name main, xmlconcat(
             xmlelement(name lastname, last_name),
             xmlelement(name firstname, first_name) ) )
FROM emp;

Slide 44

CREATE TABLE xmltab (col1 XML);

INSERT INTO xmltab (
  SELECT xmlelement(name main, xmlconcat(
             xmlelement(name lastname, last_name),
             xmlelement(name firstname, first_name) ) )
FROM emp);

Slide 45

SELECT * FROM xmltab;

Slide 47

SELECT xpath('/main/firstname/text()', col1) FROM xmltab;

Slide 48

SELECT textcol[1] FROM (
  SELECT xpath('/main/firstname/text()', col1) AS textcol
    FROM xmltab ) AS xmlsource;

Slide 49

select query_to_xml('select * from emp', TRUE, TRUE, 'scott');

Slide 50

select table_to_xml('emp', TRUE, TRUE, 'scott');

postgresql conference sql/xml presentation

This post isn’t really PostgreSQL specific. It also happens with MySQL and Ingres (and any other 32 bit driver).

I am running Vista 64 on one of my machines. I installed Postgres 8.3 and the ODBC drivers. When I tried to setup an ODBC connection, the windows ODBC administrator program ODBCAD32.exe did not list Postgres. I noticed that it also did not list MySQL or Ingres even though I knew I had installed drivers for those databases also.

After a bit of research, I tracked down the issue. My ODBC drivers did in fact install. Microsoft, in all their wisdom, has 2 versions of odbcad32.

I wrote about GridSQL and EnterpriseDB a while back. EnterpriseDB bought GridSQL (from Mason who now works at EnterpriseDB) and has been working to improve it. Yesterday, they announced that they are open sourcing it.

GridSQL is really for data warehousing but can be applied to some OLTP/reporting combination databases. What it does is take a query and spread it out to run in parallel against a set of cheap PCs. There is a controller machine, called a coordinator, that keeps track of what is running and where.

Because there is a central point of failure (the coordinator), it is not a high availability solution.

I’m doing a “Best Practices on Database Migration” webinar at ITToolbox.com March 12.

I’ll talk about ways to make your life easier when doing database migrations.

LewisC

oracle enterprisedb

I just read this in InfoWeek: Database Survey Gives Oracle The Lead In All 13 Categories

The survey compared Oracle with DB2, MySQL, Informix Dynamic Server, PostgreSQL, Microsoft SQL Server, and Sybase Advanced Server Enterprise.

The significant parts of that are that Postgres was included (we don’t see enough of that) and that Postgres performed exceptionally well.
Some important quotes:

In a few categories, the open source system, MySQL, trailed most of the five commercial systems with which it was compared. But high user satisfaction in several categories indicates thatSun Microsystems (NSDQ: JAVA) may have gotten its money’s worth when it paid $1 billion for the database’s parent company, MySQL AB, last month.