agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedXML Parsing in Postgresql
3+ messages / 3 participants
[nested] [flat]
* XML Parsing in Postgresql
@ 2017-12-18 13:50 Abdul Hameedk <hameedka@hcl.com>
0 siblings, 2 replies; 3+ messages in thread
From: Abdul Hameedk @ 2017-12-18 13:50 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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;
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. ----------------------------------------------------------------------------------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: XML Parsing in Postgresql
@ 2017-12-18 14:02 Lutz Horn <lutz.horn@posteo.de>
parent: Abdul Hameedk <hameedka@hcl.com>
1 sibling, 0 replies; 3+ messages in thread
From: Lutz Horn @ 2017-12-18 14:02 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
> PLEASE LET ME KNOW THE CORRESPONDING PARSING QUERY IN POSTGRESQL.
Take a look at
https://www.postgresql.org/docs/current/static/functions-xml.html#FUNCTIONS-XML-PROCESSING
Regards
Lutz
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: XML Parsing in Postgresql
@ 2017-12-18 14:26 Pavel Stehule <pavel.stehule@gmail.com>
parent: Abdul Hameedk <hameedka@hcl.com>
1 sibling, 0 replies; 3+ messages in thread
From: Pavel Stehule @ 2017-12-18 14:26 UTC (permalink / raw)
To: Abdul Hameedk <hameedka@hcl.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Hi
2017-12-18 14:50 GMT+01:00 Abdul Hameedk <hameedka@hcl.com>:
> 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.
> ------------------------------------------------------------
> ----------------------------------------------------------------------------------------
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-12-18 14:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-12-18 13:50 XML Parsing in Postgresql Abdul Hameedk <hameedka@hcl.com>
2017-12-18 14:02 ` Lutz Horn <lutz.horn@posteo.de>
2017-12-18 14:26 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox