Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

I am trying to retrieve the value of Client Element which is a row in array returned as XML SOAP.

I use the following call, but don't know why I dont get any result (and no any error too).

select * from openxml('

<soap-env:envelope xmlns:soap-env="" xmlns:xsi="" xmlns:xsd="" xmlns:wsa="">


  <ns1:getdetailresponse xmlns:ns1="urn:tempuri:Ver1">

   <ns1:result xsi:nil="true"/>

   <mydataset xmlns="urn:tempuri:Ver1">

    <![CDATA[<?xml version="1.0"?>

     <dsclients xmlns:xsi="">









'//*:Body/*:GetDetailResponse/*:MyDataset/*:DsClients/ttClient') with (CusotmerID varchar (100) 'Client');

Can anyone help please?

asked 15 Jul '20, 11:18

Baron's gravatar image

accept rate: 48%

edited 15 Jul '20, 14:01


I'm not sure you can query the contents of a CDATA section via XPath directly - you might first need to extract it as a separate XML document here...

Generally, when parsing "new XML documents" with OpenXML, I have learnt to use the meta properties within the WITH clause to make sure I'm querying the right nodes (and do not stumble over missing namespaces and the like, as I have done all too often)...

(15 Jul '20, 12:03) Volker Barth

do you mean that I should extract the content of CDATA as text and then extract each element separately? i.e. should I need to use OPENXML twice?

The problem is that I can't even extract the content of CDATA!

By replacing the last line in the code above for example with:

'//*:Body/*:GetDetailResponse/*:MyDataset') with (CusotmerID varchar (100) 'text()');

(15 Jul '20, 14:13) Baron
Replies hidden

My main problem is not with CDATA, as I can replace <![CDATA[<?xml version="1.0"?> with '' and ]]> with ''.

I am not sure where exactly to use *: and where not to use in the last line of the code

(15 Jul '20, 15:58) Baron

You can always use '*:' before any node/attribute name to allow any namespace - unless the XML actually does use identical names in different name spaces so you have to distinguish the name spaces.

But note that names are case-sensitive, while your sample does treat them as case-insensitive.

(15 Jul '20, 16:08) Volker Barth

Ok, clear. One more question, what if some element has an embedded attribute in it, like:

<mydataset xmlns="urn:tempuri:Ver1">

Do I need also to add *: too or *: is really only for the namespace preceeding the element name (something like ns1: or ns2: or whatsever).

(16 Jul '20, 04:21) Baron

As stated, I'd really recommend to use the metaproperties to try to list all nodes and attributes with their values - that's the easiest way IMHO to find out how to adapt the desired XPath queries.

That being said, your last question is a general XML question better asked in general XML forums...

(16 Jul '20, 05:21) Volker Barth

I could get the correct value of client after correcting the following points:

  1. replace '<![CDATA[<?xml version="1.0"?>' with ''
  2. replace ']]>' with ''
  3. replace 'xmlns="urn:tempuri:Ver1"' with ''
  4. correcting capital & small letters (case sensitive)

My question is, how can I deactivate the effect of CDATA, so that I get the Data within CDATA as Markup and not as literal text:

In other words, I want to get:


instead of:

 & lt ; client & gt ; 200403 & lt ;/client & gt ;
(21 Jul '20, 06:12) Baron
Replies hidden

Actually I could also solve this problem by:

replace(mytext, '& lt ;' , '<');

but this takes too long time, and I wanted to know whether there is some ready function for this.

(21 Jul '20, 06:18) Baron

See the html_decode() and html_encode() builtin functions, the first should do exactly what you are trying to.

(21 Jul '20, 08:51) Volker Barth

Instead of using replace to "flatten" the CDATA contents, you might take the according node's contents and handle it as a separate XML document (which it is, obviously).

(21 Jul '20, 08:54) Volker Barth
Replies hidden

Thank you very much, yes html_decode() does what I want.

(22 Jul '20, 03:18) Baron

Yes, this is what I want, but I can't bring these together.

Is there any simple example showing how to deal with CDATA?

(22 Jul '20, 03:18) Baron

There are XPath functions to do so according to the internets, but I guess those are not supported by SQL Anywhere's subset of XPath functions.

(24 Jul '20, 10:06) Volker Barth
showing 5 of 13 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 15 Jul '20, 11:18

question was seen: 1,008 times

last updated: 24 Jul '20, 10:06