Search code examples
xmlpostgresqlxpathxml-parsingxmltable

Extracting value of xml in PostgreSQL

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'.

Solution

  • 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
;