OPENXML with Array an Attribute

[Baron]

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="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/08/addressing">

 <soap-env:body>

  <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="http://www.w3.org/2001/XMLSchema-instance">

     <ttclient>

      <client>200403</client>

     </ttclient>

     </dsclients>]]>

   </mydataset>

  </ns1:getdetailresponse>

 </soap-env:body>

</soap-env:envelope>',

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

Can anyone help please?

[VolkerBarth]

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)...

[Baron]

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()');

[Baron]

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

[VolkerBarth]

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.

[Baron]

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).

[VolkerBarth]

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...

[Baron]

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:

<client>200403</client>

instead of:

 & lt ; client & gt ; 200403 & lt ;/client & gt ;

[Baron]

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.

[VolkerBarth]

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

[VolkerBarth]

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).

[Baron]

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

[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?

[VolkerBarth]

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.