xml, PostgreSQL - rextester

Language: Layout:

CREATE TABLE if not exists xmltest2 (
    id SERIAL,
    data xml
);

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;

INSERT INTO xmltest2 (data, id) VALUES (
'<?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_Summary>
    </SmartTextData>
</ItemData>', 1);

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;

drop table xmltest2;


absolute service time: 0,49 sec