Thread: Selecting data from XML

Selecting data from XML

From
Victor Sterpu
Date:
If I have a XML like this
<?xml version="1.0" encoding="UTF-8"?>
<Errors>
<Error  code="CLIN102" validFrom="1980-02-23"/>
<Error  code="CLIN103" validFrom="1980-02-23" validTo="2012-01-01"/>
</Errors>

can I write a query that will output the columns names and values like this?

code;validFrom;validTo
------------------------------
CLIN102;1980-02-23;
CLIN103;1980-02-23;2012-01-01

Thank you


Re: Selecting data from XML

From
Craig Ringer
Date:
<div class="moz-cite-prefix">On 07/17/2012 03:56 AM, Victor Sterpu wrote:<br /></div><blockquote
cite="mid:a1dc56fc-9159-4f31-9c94-9c363757f031@caido"type="cite"><pre wrap="">If I have a XML like this
 
<?xml version="1.0" encoding="UTF-8"?>
<Errors>
<Error  code="CLIN102" validFrom="1980-02-23"/>
<Error  code="CLIN103" validFrom="1980-02-23" validTo="2012-01-01"/>
</Errors>

can I write a query that will output the columns names and values like this?

code;validFrom;validTo
------------------------------
CLIN102;1980-02-23;
CLIN103;1980-02-23;2012-01-01</pre></blockquote><br /><a
href="http://www.postgresql.org/docs/9.1/static/functions-xml.html#FUNCTIONS-XML-PROCESSING">http://www.postgresql.org/docs/9.1/static/functions-xml.html#FUNCTIONS-XML-PROCESSING</a><br
/><br/> You should be able to do it with some xpath expressions. It probably won't be fast or pretty. Consider using
PL/Python,PL/perl, PL/Java, or something like that to do the processing and return the resultset.<br /><br /> --<br />
CraigRinger<br /><br />