Skip to content

Latest commit

 

History

History
70 lines (61 loc) · 1.79 KB

how-do-i-parse-xml-data-using-postgresql.md

File metadata and controls

70 lines (61 loc) · 1.79 KB
Raw

How do I parse XML data using PostgreSQL?

// plain

PostgreSQL can parse XML data using the xml data type and several functions and operators that are available to work with it.

To parse XML data, you need to first convert it into the xml data type. This can be done using the xmlparse function. For example:

SELECT xmlparse(document '<note>
  <to>Tove</to>
  <from>Jani</from>
  <heading>Reminder</heading>
  <body>Don't forget me this weekend!</body>
</note>');

The output of this code will be:

   xmlparse
---------------
  <note>
    <to>Tove</to>
    <from>Jani</from>
    <heading>Reminder</heading>
    <body>Don't forget me this weekend!</body>
  </note>
(1 row)

Once the XML data is in the xml data type, you can use the xpath function to extract values from the XML. For example:

SELECT xpath('/note/to/text()', xmlparse(document '<note>
  <to>Tove</to>
  <from>Jani</from>
  <heading>Reminder</heading>
  <body>Don't forget me this weekend!</body>
</note>'));

The output of this code will be:

 xpath
-------
 Tove
(1 row)

You can also use the xmlexists function to check if a certain node exists in the XML. For example:

SELECT xmlexists('/note/from[text() = "Jani"]'
   PASSING BY VALUE xmlparse(document '<note>
  <to>Tove</to>
  <from>Jani</from>
  <heading>Reminder</heading>
  <body>Don't forget me this weekend!</body>
</note>'));

The output of this code will be:

 xmlexists
-----------
 t
(1 row)

Helpful links

onelinerhub: How do I parse XML data using PostgreSQL?