Convert Column XML data to Rows

Question

I am using version PostgreSQL 8.3.12.

Table Column Name : xmltest

id data

Data Column contains XML data that looks like this:

<?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>

I want to spilt XML Tag IID and CODE data to respective ID Column.

Expected Output :

          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

Note : I am using version PostgreSQL 8.3.12 with this some new syntax of XMLPATH not work.

Simply I want to convert XML Data to rows column structure.

Thanks for Reading this.

File cannot be validated, there is a missing tag .http://www.xmlvalidation.com/ — McNets, Feb 02 '17 at 11:44
Your XML is not correctly balanced, `STI_DxItem` is not closed. — jcaron, Feb 02 '17 at 11:44
@jcaron CODE EDITED. iS THER ANY WAT TO spilt out all LLID here. — rohit patil, Feb 02 '17 at 11:58
@McNets adjust missing tags. is there any way to list out all LIID here — rohit patil, Feb 02 '17 at 11:59

score 0 · Answer 1 · edited May 23 '17 at 12:33

Looking at SO questions, I've found this:

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

According to the solution of this question, you can implement unnest by your own, on a one-dimension arrays in this way:

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;

This function should allow to query your data using next sentence:

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;

Notice, I've used array[array['aaa','example.com']] as you pointed out in your comments, due your XML data has not schema.

I've tested it in rextester and it works.

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

Check it here: Rextester

its not wokring on 8.3 vesrion .Thnk for help . But any another way to Find onlt LIID here ? — rohit patil, Feb 02 '17 at 12:23
"select xpath('/aaa:gpx','abc',array[array['aaa','http://example.com']]);" This query runs but resturn Blank result. — rohit patil, Feb 02 '17 at 12:47

Convert Column XML data to Rows

1 Answers1