时间戳

首页 > 解决方案 > 如何从 PostgreSQL 1:n 表生成 XML 响应？

sql - 如何从 PostgreSQL 1:n 表生成 XML 响应？

问题描述

我有表“客户”：

id  name     registered_on  status
--  -------  -------------  ------
 1  Alice    2020-03-04     a     
 2  Vincent  2020-03-05     p     
 3  Anne     2020-03-06     a

和表“帐户”：

client_id  account_number  type  balance
---------  --------------  ----  -------
        1  300-1           CHK       100
        2  307-5           SAV        24
        2  307-6           CHK       350

我在DB Fiddle中创建了它们（对于我之前问过的关于生成 JSON 的类似问题）。

现在，我需要一个 SQL 查询来生成 1:n XML 文档：

<client id="1" name="Alice" registered_on="2020-03-04" status="a">
  <account account_number="300-1" type="CHK" balance="100" />
</client>

<client id="2" name="Vincent" registered_on="2020-03-05" status="p">
  <account account_number="307-5" type="SAV" balance="24" />
  <account account_number="307-6" type="CHK" balance="350" />
</client>

<client id="3" name="Anne" registered_on="2020-03-06" status="a" />

表格之间存在 1:n 的关系，并且某些客户可能没有帐户（例如“Anne”）。结果是我知道该怎么做的简单连接（可能是外部连接）。我只是不知道如何从中生成 XML 文档。

如果它更容易/更短，我愿意接受替代 XML 结果，只要它代表相同的数据；例如，使用标签而不是属性。

标签： sqlxmldatabasepostgresql

解决方案

在尝试了一堆选项后，我能够找到答案。

原始格式：带属性

可以使用外连接生成 XML 结果：

select
  xmlserialize(content -- remove this line to keep as XML instead of VARCHAR
  xmlagg(r)
  as text) -- remove this line to keep as XML instead of VARCHAR
from (
  select
    xmlelement(name client,
      xmlattributes(c.id, c.name, c.registered_on, c.status),
      case when count(a.client_id) > 0 then 
        xmlagg(xmlelement(name account, 
                 xmlattributes(a.account_number, a.type, a.balance) ))
      end
    ) as r
  from client c
  left join account a on a.client_id = c.id
  group by c.id
) s

或使用子查询（更短但性能更低）：

select
  xmlserialize(content -- remove this line to keep as XML instead of VARCHAR
  xmlagg(
  xmlelement(name client, xmlattributes(id, name, registered_on, status),
    ( select xmlagg(xmlelement(name account,
             xmlattributes(a.account_number, a.type, a.balance)
      )) from account a where a.client_id = c.id
    )
  ))
  as text) -- remove this line to keep as XML instead of VARCHAR
from client c;

结果：

<client id="1" name="Alice" registered_on="2020-03-04" status="a">
  <account account_number="300-1" type="CHK" balance="100.00" />
</client>
<client id="2" name="Vincent" registered_on="2020-03-05" status="p">
  <account account_number="307-5" type="SAV" balance="24.00" />
  <account account_number="307-6" type="CHK" balance="350.00" />
</client>
<client id="3" name="Anne" registered_on="2020-03-06" status="a" />

替代格式：无属性

有些人喜欢完全避免使用属性并总是使用标签。这也可以使用：

select 
  xmlserialize(content -- remove this line to keep as XML instead of VARCHAR
  xmlagg(xmlelement(name client,
    xmlforest(id, name, registered_on, status), 
    ( select xmlagg(xmlelement(name account, 
             xmlforest(a.account_number, a.type, a.balance))) 
      from account a where a.client_id = c.id 
    )
  ))
  as text) -- remove this line to keep as XML instead of VARCHAR
from client c;

结果：

<client>
  <id>1</id>
  <name>Alice</name>
  <registered_on>2020-03-04</registered_on>
  <status>a</status>
  <account>
    <account_number>300-1</account_number>
    <type>CHK</type>
    <balance>100.00</balance>
  </account>
</client>
<client>
  <id>2</id>
  <name>Vincent</name>
  <registered_on>2020-03-05</registered_on>
  <status>p</status>
  <account>
    <account_number>307-5</account_number>
    <type>SAV</type>
    <balance>24.00</balance>
  </account>
  <account>
    <account_number>307-6</account_number>
    <type>CHK</type>
    <balance>350.00</balance>
  </account>
</client>
<client>
  <id>3</id>
  <name>Anne</name>
  <registered_on>2020-03-06</registered_on>
  <status>a</status>
</client>

推荐阅读