Search value in XML

General
Highlighted
Teradata Employee

Search value in XML

Hi All,

 

I am struck while searching for text inside a xml document. Please find the details.

i have xml data which looks like '<Company>    <CompanyId>3</CompanyId>    <IsUserPlaying>true</IsUserPlaying>    <IsKickUser>true</IsKickUser>    </Company>'

and from this i want to search or find value 'true'. I used xmlextract function but to no avail. I also used XPathQuery but no success.

XPathquery ----> i tested this by creating a table with varchar(63000) field. but when i used it i get error - data type new value does not matched defined type name

query using XPathQuery - SELECT id,XPathValue(o.new_value,'//Company/IsUserPlaying/*') AS PO_Number
FROM common.test_x_query o 

i also changed the data type of table to varchar(500) so that it gets stored as string but i get same error.

Appriciate help in this regard. Thank you.