从PostgreSQL到XML的3个表

admin

从PostgreSQL到XML的3个表

sql

我是一个小的开发团队的实习生，我的项目负责人希望我编写一个函数，该函数将PostgreSQL数据导出到XML文件。不幸的是，我只知道如何将导出内容写入csv。

有3个不同的表，他希望这样（XML视图）

<Table 1 Col1=".." Col2="..">
    <Table 2 Col1="...">
        <Table3 Col1=".." Col2="" Col3=".." Col4="..." />
        <Table3 Col1=".." Col2="" Col3=".." Col4="..." />
        <Table3 Col1=".." Col2="" Col3=".." Col4="..." />       
     </Table2>
    <Table1>
        <Table1>....</Table>
    </Table>
</Table2>
<Table1 Col1="xxx" Col2="xxx">
 ...

这些标签分别用于我的表名。如何编写代码以导出此代码？

关于的其他问题主要集中在导出单个表上，因此我希望这个问题也可以帮助其他尝试导出多个表的人。

阅读 202

2021-06-07

共1个答案

admin

您有三层嵌套表。

样本数据：

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 output
FROM
(
  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>

2021-06-07