Re: XML Parsing in Postgresql

Hi

2017-12-18 14:50 GMT+01:00 Abdul Hameedk <[email protected]>:

> I have below xml content. In oracle we are having cursor to extract the
> xml in row format. Do we have same like in POSGRES.
>
> <TABLES>
>
>                 <SEC_CALDAY> --Table name
>
>                                 <ROW> --3 rows formed with ROW tag
>
>                                                 <SEC_DID>123</SEC_DID>
>
>                                                 <SOURCE>TEXT</SOURCE>
>
>                                                 <DAYS>31</DAYS>
>
>                                                 <OCCR>24</OCCR>
>
>
> <SEC_POLICYDETAILSID>45678</SEC_POLICYDETAILSID>
>
>                                 </ROW>
>
>                                 <ROW>
>
>                                                 <SEC_DID>456</SEC_DID>
>
>                                                 <SOURCE>TEXT</SOURCE>
>
>                                                 <DAYS>31</DAYS>
>
>                                                 <OCCR>24</OCCR>
>
>
> <SEC_POLICYDETAILSID>45679</SEC_POLICYDETAILSID>
>
>                                 </ROW>
>
>                                 <ROW>
>
>                                                 <SEC_DID>789</SEC_DID>
>
>                                                 <SOURCE>TEXT</SOURCE>
>
>                                                 <DAYS>31</DAYS>
>
>                                                 <OCCR>24</OCCR>
>
>
> <SEC_POLICYDETAILSID>45680</SEC_POLICYDETAILSID>
>
>                                 </ROW>
>
>                 </SEC_CALDAY>
>
> </TABLES>
>
>
>
> *Oracle cursor to parse and textract the xml in row format.*
>
>
>
> CURSOR C_XMLSEC_CALDAY (B_XMLDOC11 IN XMLTYPE)
>
> IS
>
> SELECT  EXTRACTVALUE (VALUE (T), '/ROW/SEC_DID')  SEC_DID
>
> ,   EXTRACTVALUE (VALUE (T), '/ROW/SOURCE')  SOURCE
>
> ,  EXTRACTVALUE (VALUE (T), '/ROW/DAYS') DAYS
>
> ,  EXTRACTVALUE (VALUE (T), '/ROW/OCCR') OCCR
>
> ,  EXTRACTVALUE (VALUE (T), '/ROW/SEC_POLICYDETAILSID') SEC_POLICYDETAILSID
>
> FROM TABLE (XMLSEQUENCE (EXTRACT (B_XMLDOC11, '//ROW'))) T;
>

depends on PostgreSQL version

last version has XMLTABLE - for older versions, you should to use XPATH
function probably


postgres=# select xmltable.* from data, lateral
xmltable('/TABLES/SEC_CALDAY/ROW' passing x columns sec_did int path
'SEC_DID', source text path 'SOURCE', days int path 'DAYS');
┌─────────┬────────┬──────┐
│ sec_did │ source │ days │
╞═════════╪════════╪══════╡
│     123 │ TEXT   │   31 │
│     456 │ TEXT   │   31 │
│     789 │ TEXT   │   31 │
└─────────┴────────┴──────┘
(3 rows)

or

postgres=# select (xpath('/ROW/SEC_DID/text()', x2))[1],
(xpath('/ROW/SOURCE/text()', x2))[1] from ( select
unnest(xpath('/TABLES/SEC_CALDAY/ROW', x)) x2 FROM data) s;
┌───────┬───────┐
│ xpath │ xpath │
╞═══════╪═══════╡
│ 123   │ TEXT  │
│ 456   │ TEXT  │
│ 789   │ TEXT  │
└───────┴───────┘
(3 rows)




>
> *Please let me know the corresponding parsing query in Postgresql.*
>
>
> ::DISCLAIMER:: ------------------------------
> ------------------------------------------------------------
> ---------------------------------------------------------- The contents
> of this e-mail and any attachment(s) are confidential and intended for the
> named recipient(s) only. E-mail transmission is not guaranteed to be secure
> or error-free as information could be intercepted, corrupted, lost,
> destroyed, arrive late or incomplete, or may contain viruses in
> transmission. The e mail and its contents (with or without referred errors)
> shall therefore not attach any liability on the originator or HCL or its
> affiliates. Views or opinions, if any, presented in this email are solely
> those of the author and may not necessarily reflect the views or opinions
> of HCL or its affiliates. Any form of reproduction, dissemination, copying,
> disclosure, modification, distribution and / or publication of this message
> without the prior written consent of authorized representative of HCL is
> strictly prohibited. If you have received this email in error please delete
> it and notify the sender immediately. Before opening any email and/or
> attachments, please check them for viruses and other defects.
> ------------------------------------------------------------
> ----------------------------------------------------------------------------------------
> ::DISCLAIMER:: ------------------------------
> ------------------------------------------------------------
> ---------------------------------------------------------- The contents
> of this e-mail and any attachment(s) are confidential and intended for the
> named recipient(s) only. E-mail transmission is not guaranteed to be secure
> or error-free as information could be intercepted, corrupted, lost,
> destroyed, arrive late or incomplete, or may contain viruses in
> transmission. The e mail and its contents (with or without referred errors)
> shall therefore not attach any liability on the originator or HCL or its
> affiliates. Views or opinions, if any, presented in this email are solely
> those of the author and may not necessarily reflect the views or opinions
> of HCL or its affiliates. Any form of reproduction, dissemination, copying,
> disclosure, modification, distribution and / or publication of this message
> without the prior written consent of authorized representative of HCL is
> strictly prohibited. If you have received this email in error please delete
> it and notify the sender immediately. Before opening any email and/or
> attachments, please check them for viruses and other defects.
> ------------------------------------------------------------
> ----------------------------------------------------------------------------------------
>
>