Extracting value of xml in PostgreSQL

Question

Extracting value of xml in PostgreSQL

28 views Asked by Дмитрий Мыльч At 08 March 2024 at 15:37

Please tell me how to extract the guid field from this XML? The problem is the presence of xmlns parameters. Currently returning empty.

with XML_text(col) as
(
select
'<?xml version="1.0" encoding="UTF-8"?>
<purchasePlan
xmlns:ns2="http://zakupki.gov.ru/223fz/purchasePlan/1"
xmlns="http://zakupki.gov.ru/223fz/types/1"
xmlns:ns10="http://zakupki.gov.ru/223fz/decisionSuspension/1"
xmlns:ns11="http://zakupki.gov.ru/223fz/disagreementProtocol/1"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="https://zakupki.gov.ru/223/integration/schema/TFF-13.1 https://zakupki.gov.ru/223/integration/schema/TFF-13.1/purchasePlan.xsd">
<body>
<item>
<guid>096c4bf6-d656-4441-9032-0b7c45423af1</guid>
</item>
</body>
</purchasePlan>'::xml
)
SELECT r.guid
  FROM XML_text as x,
       XMLTABLE('purchasePlan/body/item' passing x.col
                COLUMNS guid varchar(50) path './guid'
                ) as r
;

The result must be '096c4bf6-d656-4441-9032-0b7c45423af1'.

Original Q&A

There are 1 answers

**Yitzhak Khabinsky** · Accepted Answer · 2024-03-08T16:02:47+00:00

Your XML has a default namespace. All XML elements are bound to it, even if we don't see it explicitly. It needs to be declared via xmlnamespaces(...) clause and used in the XPath expressions.

dbfiddle

SQL

with XML_text(col) as
(
select
'<?xml version="1.0" encoding="UTF-8"?>
<purchasePlan xmlns:ns2="http://zakupki.gov.ru/223fz/purchasePlan/1"
              xmlns="http://zakupki.gov.ru/223fz/types/1"
              xmlns:ns10="http://zakupki.gov.ru/223fz/decisionSuspension/1"
              xmlns:ns11="http://zakupki.gov.ru/223fz/disagreementProtocol/1"
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              xsi:schemaLocation="https://zakupki.gov.ru/223/integration/schema/TFF-13.1 https://zakupki.gov.ru/223/integration/schema/TFF-13.1/purchasePlan.xsd">
    <body>
        <item>
            <guid>096c4bf6-d656-4441-9032-0b7c45423af1</guid>
        </item>
    </body>
</purchasePlan>'::xml
)
SELECT r.guid
  FROM XML_text as x,
       XMLTABLE(xmlnamespaces('http://zakupki.gov.ru/223fz/types/1' AS "ns1"),
                '/ns1:purchasePlan/ns1:body/ns1:item' 
                PASSING x.col
                COLUMNS guid varchar(50) path 'ns1:guid'
                ) as r
;

TechQA.

Extracting value of xml in PostgreSQL

There are 1 answers

Related Questions in XML

Related Questions in POSTGRESQL

Related Questions in XPATH

Related Questions in XML-PARSING

Related Questions in XMLTABLE

Popular Questions

Popular Tags

Trending Questions