You can translate the question and the replies:

strip XML tags from a column

Hello, I am getting the data to a column as <p><span style="color: black; font-family: arial,sans-serif; font-size: 10pt;">1:  Communication with CNF GM Wallice</span></p><p>hdsfgkwlfewfw</p<ul><li>something</li><li>something2<li><p>iahgferbfeogf</p> I need to convert it into text format. I tried to use xpath but I am not getting null. I need to convert it into normal text in the view I am going to create from the base view. Can you please give me a solution for this. Thanks & Regards, Gokul
user
05-11-2019 08:19:55 -0500

3 Answers

Hi, I had the same issue and it seems that it has to do with the structure of the data that is not meeting the XML standard. Based on your example, it is missing a root node to enclose the whole data. Then it is also missing the '**ul**' closing tag, while the '**li**' tags that is closing something2 is '<li>' when it needs to be closed with '</li>'. After resolving all this format issue, i was able to use the XPath function to get the value. You can refer to the [XML Processing Functions](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/appendix/syntax_of_condition_functions/xml_processing_functions#xpath) section of the Virtual DataPort VQL Guide for more information. Hope this helps!
Denodo Team
06-11-2019 03:43:36 -0500
Apologies, I somehow have deleted the closing tags while copying. My XML looks like this, <p><span style="color: black; font-family: arial,sans-serif; font-size: 10pt;">1: Communication with CNF GM Wallice</span></p><p>hdsfgkwlfewfw</p><ul><li>something</li><li>something2</li></ul><p>iahgferbfeogf</p> But yes, as you said, if the whole xml is enclosed within a single tag, we are getting the correct values using Xpath but in the cases like I mentioned above I am getting null value using xpath. Any idea on how to solve that or any workaround.
user
06-11-2019 04:02:09 -0500
Hi, As it doesn't have a single root element then it is not a well-formed (or malformed) XML document. Unfortunately, the XML processing functions will not be able to work against the data if a root element is not found, therefore a null value is returned. An alternative that I can think of would be to cast format the data as string and project the values that you want using substring functions. Otherwise, I would recommend checking if the XML could be properly formatted from the source. Hope this helps!
Denodo Team
19-11-2019 04:23:06 -0500
You must sign in to add an answer. If you do not have an account, you can register here