CODEWITHSUNDEEP
sqlxmlpostgresqlxml-parsing
dp1212
dp1212

Reputation: 89

how to extract a value from xml document in postgresql

How to extract the value of clientid from the following xml document in Postgres?

I want to get value 1435 from this xml:

CREATE TABLE xmltest3(xtxt xml);
INSERT INTO xmltest3 values ('<clients><client clientId="1435"/></clients>');
SELECT unnest(xpath('./client /text()', xtxt::xml))::text AS XMLDATA FROM XMLTEST3;

I tried:

SELECT unnest(xpath('./clients/text()', xtxt::xml))::text AS XMLDATA 
FROM XMLTEST3;

But it is not working.

Also, how to extract the ENvalues from this?

<ENList><EN ENValue="Liquidity"/><EN ENValue="Treasury"/></ENList>

Upvotes: 1

Views: 7557

Answers (1)

Mike Organek
Mike Organek

Reputation: 12484

Use @clientId to get the attribute value:

with invar as (
  select '<clients><client clientId="1435"/></clients>'::xml as x
) 

select unnest(xpath('/clients/client/@clientId', x))
  from invar;
 unnest 
--------
 1435
(1 row)

The concept is the same for your other example:

with invar as (
  select '<ENList><EN ENValue="Liquidity"/><EN ENValue="Treasury"/></ENList>'::xml as x
) 
select unnest(xpath('/ENList/EN/@ENValue', x))
  from invar;

  unnest   
-----------
 Liquidity
 Treasury
(2 rows)

Upvotes: 2

Related Questions