Преобразование данных столбца XML в строки

Я использую версию 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-данные в структуру столбцов строк.

Спасибо, что прочитали это.

sql,xml,postgresql,

1

Ответов: 1


0

Глядя на вопросы SO, я нашел это:

ERROR: функция unsest (integer []) не существует в 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-данные не имеют схемы.

Я тестировал его в реестре, и он работает.

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

Проверьте это здесь: Rextester

SQL, XML, PostgreSQL,
Похожие вопросы