从PostgreSQL到XML的3个表

从PostgreSQL到XML的3个表

您有三层嵌套表。

样本数据：

CREATE TABLE a(  a_id integer primary key,  name text);CREATE TABLE b(  b_id integer primary key,  a_id integer references a(a_id),  val text);CREATE TABLE c(  c_id serial primary key,  b_id integer references b(b_id),  blah text);INSERT INTO a(a_id, name) VALUES (1, 'fred'),(2, 'bert');INSERT INTO b(b_id, a_id, val) VALUES (11, 1, 'x'), (12, 1, 'y'), (21, 2, 'a'), (22, 2, 'b');INSERT INTO c(b_id, blah) VALUES(11, 'whatever'), (11, 'gah'), (12, 'borkbork'), (22, 'fuzz');

方法1：进行左联接，在客户端中处理XML

处理此问题的最简单方法是对所有三个表进行左联接，从最外到最内的顺序进行排序。然后，您迭代结果集，每当该级别的主题发生变化时，关闭一个元素，然后打开另一个元素。

select *from a left join b on (a.a_id = b.a_id)       left join c on (b.b_id = c.b_id)order by a.a_id, b.b_id, c.c_id;

然后循环返回的行，并为每行 伪代码 ：

cur_row = get_new_row()if (cur_row[b_id] != prev_row[b_id]) {   emit_close_tableb();}if (cur_row[a_id] != prev_row[a_id]) {   emit_close_tablea();   emit_open_tablea(cur_row);}if (cur_row[b_id] != prev_row[b_id]) {   emit_open_tableb(cur_row);}emit_tablec(cur_row);prev_row = cur_row;

要编写XML，可以使用

XMLWriter

。要读取查询数据，可以使用PDO之类的东西或您喜欢的任何驱动程序。如果数据集很大，请考虑使用游标读取数据。

这很好用，但是它传输了 很多 多余的数据，因为您

n

为

n

与之关联的内部表的每一行传输了外部表的数据的副本。

---

为了减少交换的多余数据，您只能选择外部表的ID

select a.a_id, b.b_id, c.*from a left join b on (a.a_id = b.a_id)       left join c on (b.b_id = c.b_id)order by a.a_id, b.b_id, c.c_id;

…然后，当您切换到新的tablea /
tableb时，

SELECt

其余的行就会出现。您可能会使用第二个连接来执行此 *** 作，这样就不会破坏从中读取行的主连接上的结果集和光标状态。

方法2：在PostgreSQL中全部完成

对于较小的数据集或较大数据集的内部级别，可以使用PostgreSQL的XML支持来构造XML文档，例如：

WITH xmlinput AS (  SELECT a, b, c  FROM a  LEFT JOIN b ON (a.a_id = b.a_id)  LEFT JOIN c on (b.b_id = c.b_id)  ORDER BY a.a_id, b.b_id, c.c_id)SELECt  XMLELEMENT(name items,    xmlagg(      XMLELEMENT(name a,        XMLFOREST((a).a_id AS a_id, (a)."name" AS name),        b_xml      )    ORDER BY (a).a_id)  ) AS outputFROM(  SELECt    a,    xmlagg(      XMLELEMENT(name b,        XMLFOREST((b).b_id AS b_id, (b).val AS val),        c_xml      )    ORDER BY (b).b_id)    AS b_xml  FROM  (    SELECt      a, b,      xmlagg(        XMLELEMENT(name c,          XMLFOREST((c).c_id AS c_id, (c).blah AS blah)        )      ORDER BY (c).c_id)      AS c_xml    FROM xmlinput    GROUP BY a, b  ) c_as_xml  GROUP BY a) b_as_xml;

…但是实际上，您必须是某种受虐狂才能编写这样的代码。尽管可以证明它相当快。

要理解该查询，您需要阅读PostgreSQLXML文档。奇怪的语法是SQL / XML委员会梦dream以求的，不要怪我们。

还要注意，在上面的代码中大量使用 行变量
来使其保持井井有条。

a

，

b

并

c

作为整行传递到查询的外层。这样就避免了名称冲突时使用别名的麻烦。语法

(a).a_id

等含义是“

a_id

行变量的字段

a

”。有关详细信息，请参见PostgreSQL手册。

上面使用了更好的XML结构（请参阅下面的注释）。如果要发出属性而不是元素，则可以将

XMLFOREST

调用更改为

XMLATTRIBUTES

调用。

输出：

<items><a><a_id>1</a_id><name>fred</name><b><b_id>11</b_id><val>x</val><c><c_id>1</c_id><blah>whatever</blah></c><c><c_id>2</c_id><blah>gah</blah></c></b><b><b_id>12</b_id><val>y</val><c><c_id>3</c_id><blah>borkbork</blah></c></b></a><a><a_id>2</a_id><name>bert</name><b><b_id>21</b_id><val>a</val><c/></b><b><b_id>22</b_id><val>b</val><c><c_id>4</c_id><blah>fuzz</blah></c></b></a></items>

或者，印刷精美：

<?xml version="1.0" encoding="utf-16"?><items>    <a>        <a_id>1</a_id>        <name>fred</name>        <b> <b_id>11</b_id> <val>x</val> <c>     <c_id>1</c_id>     <blah>whatever</blah> </c> <c>     <c_id>2</c_id>     <blah>gah</blah> </c>        </b>        <b> <b_id>12</b_id> <val>y</val> <c>     <c_id>3</c_id>     <blah>borkbork</blah> </c>        </b>    </a>    <a>        <a_id>2</a_id>        <name>bert</name>        <b> <b_id>21</b_id> <val>a</val> <c />        </b>        <b> <b_id>22</b_id> <val>b</val> <c>     <c_id>4</c_id>     <blah>fuzz</blah> </c>        </b>    </a></items>

请发出更好的XML

附带说明一下，在XML中使用诸如此类的属性似乎很诱人，但是使用起来很快变得困难而丑陋。请只使用普通的XML元素：

  <Table 1>    <Nr>1</Nr>    <Name>blah</Name>     <Table 2>       <Nr>1</Nr>       <Table 3>          <Col1>42</Col1>          <Col2>...</Col2>          <Col3>...</Col3>          <Col4>...</Col4>          ...       </Table 3>     </Table 2>   </Table 1>