Postgres Pro
Downloads
Home   >   mailing lists

Thread: Unable to convert xpath value to date

Unable to convert xpath value to date

From
saritha N
Date:
Hi,

I am getting an error while retrieving the values from xml and converting into date format 

when I execute this query
select history from KM_REL_FF_USR_DATA where    USERID  in   (53008) and SHARE_WORKING_GROUP =15683

output is
"<HISTORY TYPE="DATAENTRYTYPE" ><USERSUBMITEDDATA ID="1" ><DATAENTRYFIELDDATA ID="1" TYPE="DATAENTRYTYPE" ><DATA ID="1" CREATED-DATE="09-09-2013" CREATED-TIME="12:00:00 am(am)" MODIFIED-DATE="09-09-2013" MODIFIED-TIME="12:00:00 am(am)" > Enter what you want to achieve from this programme&#058;
 </DATA></DATAENTRYFIELDDATA></USERSUBMITEDDATA></HISTORY>"

from this I need to extract modified date and convert to date.I am using this below query.But while executing this query I am getting error

select to_date(xpath('//DATA/@MODIFIED-DATE',(select history from KM_REL_FF_USR_DATA where    USERID  in   (53008) and SHARE_WORKING_GROUP =15683))::text,'dd/mm/yyyy')

 ERROR:  invalid value "{0" for "dd"
 DETAIL:  Value must be an integer.

Please help me out to solve this issue.

Thanks & Regards,
Saritha

Re: Unable to convert xpath value to date

From
David Johnston
Date:
saritha N wrote
>  ERROR:  invalid value "{0" for "dd"
>  DETAIL:  Value must be an integer.

The leading "{" in the data is the big give away.  This is how a string
representation of an array looks.  From the documentation:

http://www.postgresql.org/docs/9.3/interactive/functions-xml.html


> The function xpath evaluates the XPath expression xpath (a text value)
> against the XML value xml. It returns an array of XML values corresponding
> to the node set produced by the XPath expression. If the XPath expression
> returns a scalar value rather than a node set, a single-element array is
> returned.

If you want to convert the result of an xpath query you need to operate over
an array.  Usually that means unnesting though there are other
possibilities.   If you know you are only dealing (or care about) the first
element you can simply code it as (xpath(...))[1] - note the surrounding
parentheses - to get that first element.

David J.






--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Unable-to-convert-xpath-value-to-date-tp5773204p5773223.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Unable to convert xpath value to date

From
saritha N
Date:
Thanks a lot,It worked for me.

Thanks & Regards,
Saritha

On Thu, Oct 3, 2013 at 6:40 PM, David Johnston <polobo@yahoo.com> wrote:
saritha N wrote
>  ERROR:  invalid value "{0" for "dd"
>  DETAIL:  Value must be an integer.

The leading "{" in the data is the big give away.  This is how a string
representation of an array looks.  From the documentation:

http://www.postgresql.org/docs/9.3/interactive/functions-xml.html


> The function xpath evaluates the XPath expression xpath (a text value)
> against the XML value xml. It returns an array of XML values corresponding
> to the node set produced by the XPath expression. If the XPath expression
> returns a scalar value rather than a node set, a single-element array is
> returned.

If you want to convert the result of an xpath query you need to operate over
an array.  Usually that means unnesting though there are other
possibilities.   If you know you are only dealing (or care about) the first
element you can simply code it as (xpath(...))[1] - note the surrounding
parentheses - to get that first element.

David J.






--
View this message in context: http://postgresql.1045698.n5.nabble.com/Unable-to-convert-xpath-value-to-date-tp5773204p5773223.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general