In SQL Server 2005's T-SQL language I can shred XML value the following way:
SELECT
t.c.value('./ID[1]', 'INT'),
t.c.value('./Name[1]', 'VARCHAR(50)')
FROM @Xml.nodes('/Customer') AS t(c)
where @Xml is a xml value something like
'<Customer><ID>23</ID><Name>Google</Name></Customer>'
Can someone help me to achieve the same result in PostgreSQL (probably in PL/pgSQL)?
The xpath
function will return an array of nodes so you can extract multiple partners. Typically you would do something like:
SELECT (xpath('/Customer/ID/text()', node))[1]::text::int AS id,
(xpath('/Customer/Name/text()', node))[1]::text AS name,
(xpath('/Customer/Partners/ID/text()', node))::_text AS partners
FROM unnest(xpath('/Customers/Customer',
'<Customers><Customer><ID>23</ID><Name>Google</Name>
<Partners><ID>120</ID><ID>243</ID><ID>245</ID></Partners>
</Customer>
<Customer><ID>24</ID><Name>HP</Name><Partners><ID>44</ID></Partners></Customer>
<Customer><ID>25</ID><Name>IBM</Name></Customer></Customers>'::xml
)) node
Where you use unnest(xpath(...))
to break a big chunk of xml into row sized bites; and xpath()
to extract individual values. Extracting the first value from an XML array, casting to text
and then casting to int
(or date
, numeric
etc) is not really comfortable. I have some helper functions on my blog to make this easier, see XML Parsing in Postgres
Use the xpath-function in PostgreSQL to proces the XML.
Edit: Example:
SELECT
xpath('/Customer/ID[1]/text()', content),
xpath('/Customer/Name[1]/text()', content),
*
FROM
(SELECT '<Customer><ID>23</ID><Name>Google</Name></Customer>'::xml AS content) AS sub;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With