在postgresql中获取XML值

Question

在postgres数据库(jira中的工作流)表中的一列中有以下xml：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE workflow PUBLIC "-//OpenSymphony Group//DTD OSWorkflow 2.8//EN" "http://www.opensymphony.com/osworkflow/workflow_2_8.dtd">
<workflow>
    ...
  <steps>
    <step id="1" name="New">
        ...
    </step>
    <step id="5" name="Resolved">
        ...
    </step>
    <step id="10" name="Closed">
        ...
    </step>
  </steps>
  ...
</workflow>

我想选择映射：

1 - New
5 - Resolved
10 - Closed

怎样才能做到这一点？我应该使用哪个postgres函数？

Answer 1

您将需要使用xpath 函数。

select
  unnest(xpath('//workflow/steps/step/@id', xmlin)),
  unnest(xpath('//workflow/steps/step/@name', xmlin))
from
  (select xmlparse(document '<?xml version="1.0"?>
  <!DOCTYPE workflow PUBLIC "-//OpenSymphony Group//DTD OSWorkflow 2.8//EN" "http://www.opensymphony.com/osworkflow/workflow_2_8.dtd">
<workflow>
  <steps>
    <step id="1" name="New">
    </step>
    <step id="5" name="Resolved">
    </step>
    <step id="10" name="Closed">
    </step>
  </steps>
</workflow>') xmlin) x

----
1;New
5;Resolved
10;Closed

注意--正如@posz所指出的，如果缺少name或id属性，这是行不通的。

Answer 2

要处理xml节点，可以使用xpath()函数：

with v(x) as (
  select xmlparse(document '<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE workflow PUBLIC "-//OpenSymphony Group//DTD OSWorkflow 2.8//EN" "http://www.opensymphony.com/osworkflow/workflow_2_8.dtd">
<workflow><steps><step id="1" name="New"/><step id="5" name="Resolved"/><step id="10" name="Closed"/></steps></workflow>')
)
select (xpath('@id', step))[1] as id, (xpath('@name', step))[1] as name
from v
cross join lateral unnest(xpath('/workflow/steps/step', x)) as step

结果列将按类型xml排列，因此可能需要进行转换。