时间戳

首页 > 解决方案 > postgres复杂的xml解析

xml - postgres复杂的xml解析

问题描述

我有下面的xml，一个员工可以有几个reportees，每个reportees可以有几个reportess，但是层次结构最多可以有4个内部reportess。我需要这个 XML 的结果如下表所示。

我正在使用下面的 postgres 查询，我的计划是有 4 个 CTE，但是它失败并出现错误could not parse XML document我在这里做错了什么。

    <department>
        <head>  
            <employee>emp 1</employee>
            <cell col="2">M-y</cell>
            <cell col="3">T-y</cell>
            <cell col="4">W-n</cell>
            <cell col="5">T-y</cell>
            <cell col="6">F-n</cell>
            <reportees level="1">
                <employee>emp 11</employee>
                <cell col="2">M-n</cell>
                <cell col="3">T-n</cell>
                <cell col="4">W-n</cell>
                <cell col="5">T-y</cell>
                <cell col="6">F-n</cell>
                <reportees level="2">
                    <employee>emp 111</employee>
                    <cell col="2">M-n</cell>
                    <cell col="3">T-n</cell>
                    <cell col="4">W-n</cell>
                    <cell col="5">T-y</cell>
                    <cell col="6">F-n</cell>
                </reportees>
                <reportees level="2">
                    <employee>emp 10034</employee>
                    <cell col="2">M-n</cell>
                    <cell col="3">T-n</cell>
                    <cell col="4">W-n</cell>
                    <cell col="5">T-y</cell>
                    <cell col="6">F-n</cell>
                </reportees>
            </reportees>
            <reportees level="1">
                <employee>emp 12</employee>
                <cell col="2">M-n</cell>
                <cell col="3">T-n</cell>
                <cell col="4">W-n</cell>
                <cell col="5">T-y</cell>
                <cell col="6">F-n</cell>
            </reportees>
            <reportees level="1">
                <employee>emp 13</employee>
                <cell col="2">M-n</cell>
                <cell col="3">T-n</cell>
                <cell col="4">W-n</cell>
                <cell col="5">T-y</cell>
                <cell col="6">F-n</cell>
                <reportees level="2">
                    <employee>emp 131</employee>
                    <cell col="2">M-n</cell>
                    <cell col="3">T-n</cell>
                    <cell col="4">W-n</cell>
                    <cell col="5">T-y</cell>
                    <cell col="6">F-n</cell>
                    <reportees level="3">
                        <employee>emp 1311</employee>
                        <cell col="2">M-n</cell>
                        <cell col="3">T-n</cell>
                        <cell col="4">W-n</cell>
                        <cell col="5">T-y</cell>
                        <cell col="6">F-n</cell>
                    </reportees>
                </reportees>
            </reportees>
        </head>
    </department>
</company>

WITH CTE AS (
SELECT xmltable.*
  FROM xmldata,
       XMLTABLE('//company/department/head'
                PASSING data COLUMNS
                         employee text PATH 'employee'
                        , monday text PATH 'cell[1]'
                        , tuesday text PATH 'cell[2]'
                        , wednesday text PATH 'cell[3]'
                        , thuresday text PATH 'cell[4]'
                        , friday text PATH 'cell[5]'
                        , reportees XML PATH 'reportees'))
SELECT *
  FROM CTE 
  LEFT JOIN LATERAL XMLTABLE ('reportees' PASSING reportees COLUMNS 
                              employee text PATH 'employee'
                            , monday text PATH 'cell[1]'
                            , tuesday text PATH 'cell[2]'
                            , wednesday text PATH 'cell[3]'
                            , thuresday text PATH 'cell[4]'
                            , friday text PATH 'cell[5]'
                            , reportees XML PATH 'reportees') ON TRUE;


<table border='1'>
  <thead>
    <tr>
      <td>Employee</td>
      <td>Monday</td>
      <td>Tuesday</td>
      <td>Wednesday</td>
      <td>Thuresday</td>
      <td>Friday</td>
      <td>Employee_1</td>
      <td>Monday_1</td>
      <td>Tuesday_1</td>
      <td>Wednesday_1</td>
      <td>Thuresday_1</td>
      <td>Friday_1</td>
      <td>Employee_2</td>
      <td>Monday_2</td>
      <td>Tuesday_2</td>
      <td>Wednesday_2</td>
      <td>Thuresday_2</td>
      <td>Friday_2</td>
      <td>Employee_3</td>
      <td>Monday_3</td>
      <td>Tuesday_3</td>
      <td>Wednesday_3</td>
      <td>Thuresday_3</td>
      <td>Friday_3</td>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>emp 1</td>
      <td>M-y</td>
      <td>T-y</td>
      <td>W-n</td>
      <td>T-y</td>
      <td>F-n</td>
      <td>emp 11</td>
      <td>M-n</td>
      <td>T-n</td>
      <td>W-n</td>
      <td>T-y</td>
      <td>F-n</td>
      <td>emp 111</td>
      <td>M-n</td>
      <td>T-n</td>
      <td>W-n</td>
      <td>T-y</td>
      <td>F-n</td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
    </tr>
    <tr>
      <td>emp 1</td>
      <td>M-y</td>
      <td>T-y</td>
      <td>W-n</td>
      <td>T-y</td>
      <td>F-n</td>
      <td>emp 11</td>
      <td>M-n</td>
      <td>T-n</td>
      <td>W-n</td>
      <td>T-y</td>
      <td>F-n</td>
      <td>emp 10034</td>
      <td>M-n</td>
      <td>T-n</td>
      <td>W-n</td>
      <td>T-y</td>
      <td>F-n</td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
    </tr>
    <tr>
      <td>emp 1</td>
      <td>M-y</td>
      <td>T-y</td>
      <td>W-n</td>
      <td>T-y</td>
      <td>F-n</td>
      <td>emp 12</td>
      <td>M-n</td>
      <td>T-n</td>
      <td>W-n</td>
      <td>T-y</td>
      <td>F-n</td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
    </tr>
    <tr>
      <td>emp 1</td>
      <td>M-y</td>
      <td>T-y</td>
      <td>W-n</td>
      <td>T-y</td>
      <td>F-n</td>
      <td>emp 13</td>
      <td>M-n</td>
      <td>T-n</td>
      <td>W-n</td>
      <td>T-y</td>
      <td>F-n</td>
      <td>emp 131</td>
      <td>M-n</td>
      <td>T-n</td>
      <td>W-n</td>
      <td>T-y</td>
      <td>F-n</td>
      <td>emp 1311</td>
      <td>M-n</td>
      <td>T-n</td>
      <td>W-n</td>
      <td>T-y</td>
      <td>F-n</td>
    </tr>
  </tbody>
</table>

标签： xmlpostgresql

解决方案

xml 树<company>在开头缺少标记。
最好的问候，
Bjarni

推荐阅读