sql - 将列 XML 数据转换为行

Question

我使用的是 PostgreSQL 8.3.12 版本。

表列名称:xmltest

id data

Data 列包含如下所示的 XML 数据:

<?xml version="1.0" encoding="utf-8"?>
<ItemData>
    <ReviewComments>&lt;?xml version="1.0" encoding="utf-8"?&gt;
    </ReviewComments>
    <SmartTextData RootProtect="True">
        <STI_Summary IID="d10c5cbf-f5cf-4478-9f33-4580c1930413" IR="True">
            <ObjectValue />
            <CP />
            <SIProps ST="4" PL="False" PLS="" />
            <STI_SummaryActiveProblemsField LIID="cdbd7044-ccde-11db-8cba-df0a56d89593" IID="37742a5f-7998-4715-8d43-0d7a19284d44" IR="True" RW="1">
            <HD Title="Active Problems" />
            <ObjectValue />
            <CP>
                <PosReplace />
                <NegReplace />
            </CP>
            <SIProps ST="4" PL="False" PLS="" />
            <STI_US>
                <ObjectValue>
                    <TextValue>
                        <![CDATA[
]]>
                    </TextValue>
                </ObjectValue>
                <CP />
                <SIProps ST="1" SS=" " PL="False" PLS="" />
            </STI_US>
            <STI_DxItem LIID="71194038-8ffb-488b-8af5-5f1f1a679115" IID="aaf2de4e-2f1f-409b-87b7-b7265bec37db" RW="1">
                <HD Title="Coronary artery disease  " />
                <ObjectValue>
                    <Code>
                        <CodingSystem>ICD-9 CM</CodingSystem>
                        <Value>414.01</Value>
                    </Code>
                    <Code>
                        <CodingSystem>SWICPC</CodingSystem>
                        <Value>08.0.K76.CIR</Value>
                    </Code>
                </ObjectValue>
            </STI_DxItem >
        </STI_Summary>
    </SmartTextData>
</ItemData>

我想将 XML 标记 IID 和 CODE 数据溢出到相应的 ID 列。

预期输出:

          ID   LIID                                         Code_Value   CodingSystem
          1    d10c5cbf-f5cf-4478-9f33-4580c1930413      NULL
          1    37742a5f-7998-4715-8d43-0d7a19284d44      NULL
          1    aaf2de4e-2f1f-409b-87b7-b7265bec37db      414.01         IC CM
          1    aaf2de4e-2f1f-409b-87b7-b7265bec37db      08.0.K76.CIR   SWICPC

注意:我使用的是 PostgreSQL 8.3.12 版本，其中一些新的 XMLPATH 语法不起作用。

只是我想将 XML 数据转换为行列结构。

感谢阅读本文。

Answer 1

查看 SO 问题，我发现了这一点:

ERROR: function unnest(integer[]) does not exist in postgresql

根据本题的解法，可以在一维数组上自己实现unnest:

CREATE OR REPLACE FUNCTION unnest2(anyarray)
  RETURNS SETOF anyelement AS
$BODY$
   SELECT $1[i] FROM generate_series(array_lower($1,1), array_upper($1,1)) i;
$BODY$ LANGUAGE sql IMMUTABLE;

此函数应该允许使用下一句查询您的数据:

SELECT xpath('/ItemData/SmartTextData/STI_Summary/STI_DxItem/@IID', data, array[array['aaa','example.com']]) as IID,
       unnest2(xpath('/ItemData/SmartTextData/STI_Summary/ObjectValue/Code/CodingSystem/text()', data, array[array['aaa','example.com']])) as Code,
       unnest2(xpath('/ItemData/SmartTextData/STI_Summary/ObjectValue/Code/Value/text()', data, array[array['aaa','example.com']])) as Value
from xmltest2;

注意，正如您在评论中指出的那样，我使用了 array[array['aaa','example.com']]，因为您的 XML 数据没有架构。

我已经在 rextester 中对其进行了测试并且它有效。

+--------------------------------------+----------+--------------+
|                  iid                 |   code   | value        |
+--------------------------------------+----------+--------------+
| aaf2de4e-2f1f-409b-87b7-b7265bec37db | ICD-9 CM | 414.01       |
+--------------------------------------+----------+--------------+
| aaf2de4e-2f1f-409b-87b7-b7265bec37db | SWICPC   | 08.0.K76.CIR |
+--------------------------------------+----------+--------------+

在这里查看:Rextester